January 10, 2012 at 2:00 pm
the following query
select member_number, date, time_in, time_out from member_time where time_out <='9:30 am' gives the following data
member_numberdatetime_intime_out
25501/9/20127:55:31 AM1:08:15 PM
25631/9/20127:55:59 AM4:30:00 PM
7551/9/20127:56:31 AM8:00:00 PM
22291/9/20128:00:00 AM9:00:00 AM
12651/9/20128:26:25 AM12:06:15 PM
24791/9/20128:33:10 AM12:02:37 PM
9711/9/20128:34:48 AM11:31:39 AM
21791/9/20128:35:18 AM10:22:09 AM
as you can see many time_out records are >9:30 AM. what should the query be to capture only those who have clocked out before 9:30
January 10, 2012 at 2:32 pm
the following query
select member_number, date, time_in, time_out from member_time where time_out <='9:30 am' gives the following data
member_number date time_in time_out
2550 1/9/2012 7:55:31 AM 1:08:15 PM
2563 1/9/2012 7:55:59 AM 4:30:00 PM
755 1/9/2012 7:56:31 AM 8:00:00 PM
2229 1/9/2012 8:00:00 AM 9:00:00 AM
1265 1/9/2012 8:26:25 AM 12:06:15 PM
2479 1/9/2012 8:33:10 AM 12:02:37 PM
971 1/9/2012 8:34:48 AM 11:31:39 AM
2179 1/9/2012 8:35:18 AM 10:22:09 AM
as you can see many time_out records are >9:30 AM. what should the query be to capture only those who have clocked out before 9:30
What data type is time_out? Not tried this, but try using :
select member_number, date, time_in, time_out
from member_time
where time_out <='9:30:00 AM'
--or just try where time_out <= '9:30'
Let me know the results..
Stephen
January 10, 2012 at 2:40 pm
bill.akin (1/10/2012)
the following queryselect member_number, date, time_in, time_out from member_time where time_out <='9:30 am' gives the following data
member_numberdatetime_intime_out
25501/9/20127:55:31 AM1:08:15 PM
25631/9/20127:55:59 AM4:30:00 PM
7551/9/20127:56:31 AM8:00:00 PM
22291/9/20128:00:00 AM9:00:00 AM
12651/9/20128:26:25 AM12:06:15 PM
24791/9/20128:33:10 AM12:02:37 PM
9711/9/20128:34:48 AM11:31:39 AM
21791/9/20128:35:18 AM10:22:09 AM
as you can see many time_out records are >9:30 AM. what should the query be to capture only those who have clocked out before 9:30
I would be willing to bet that your datatypes are varchar? You should use date and time datatypes where appropriate. String sorting does not do what you might think. It sorts by the ascii values. This means that 12:31:00 PM will sort before 3:00:00 AM.
Personally I would use datetime as time_in and time_out and get rid of the date field. That way you can handle shifts crossing dates. (i.e. They clock in at 11pm on Wednesday and clock out on Thursday at 7am). The single date field can't handle that at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2012 at 2:59 pm
the datatype is datetime in the sql table. Thank you for your responses.
January 10, 2012 at 3:02 pm
Can you post ddl (create table scripts), sample data (insert statements) and desired output? It is pretty tough to figure out what your data and queries look like.
Take a look at the first link in my signature if you need some help on how to post that information.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 10, 2012 at 3:09 pm
USE [BtcSystem]
GO
/****** Object: Table [dbo].[member_time] Script Date: 01/10/2012 16:11:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[member_time](
[id] [int] IDENTITY(161657,1) NOT NULL,
[member_number] [int] NULL,
[date] [datetime] NULL,
[time_in] [datetime] NULL,
[time_out] [datetime] NULL,
[nvarchar](255) NULL,
[fsa] [smallint] NULL,
[gpr] [smallint] NULL,
[goal_id] [int] NULL,
[task_id] [int] NULL,
[service] [nvarchar](10) NULL,
[locnbr] [smallint] NULL,
[pr_staff] [int] NULL,
[moved_to_los] [bit] NULL,
[TypeOfSocial] [nvarchar](50) NULL,
CONSTRAINT [PK_member_time] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[member_time] ADD CONSTRAINT [DF_member_time_moved_to_los] DEFAULT ((0)) FOR [moved_to_los]
GO
2401022292008-01-03 00:00:00.0001899-12-30 08:00:00.0001899-12-30 12:15:00.000NULL211472PRAG1107TrueNULL
240143662008-01-03 00:00:00.0001899-12-30 08:00:00.0001899-12-30 08:45:00.000NULL3320112PRAG139TrueNULL
2401721742008-01-03 00:00:00.0001899-12-30 08:15:00.0001899-12-30 13:30:00.000NULL331472PRAG1107TrueNULL
2401817402008-01-03 00:00:00.0001899-12-30 08:30:00.0001899-12-30 12:30:00.000NULL331472PRAG139TrueNULL
240196312008-01-03 00:00:00.0001899-12-30 08:30:00.0001899-12-30 12:30:00.000NULL221472PRAG139TrueNULL
2402021962008-01-03 00:00:00.0001899-12-30 08:30:00.000NULLNULL0000NULL00FalseNULL
2402321532008-01-03 00:00:00.0001899-12-30 08:45:00.0001899-12-30 12:45:00.000NULL331580PRAG1107TrueNULL
2402422112008-01-03 00:00:00.0001899-12-30 08:45:00.0001899-12-30 14:45:00.000NULL331580PRAG1107TrueNULL
240255592008-01-03 00:00:00.0001899-12-30 08:45:00.0001899-12-30 12:00:00.000NULL211472PRAG1107TrueNULL
240266342008-01-03 00:00:00.0001899-12-30 08:45:00.0001899-12-30 09:30:00.000NULL331472PRAG1107TrueNULL
2402717682008-01-03 00:00:00.0001899-12-30 09:00:00.0001899-12-30 10:45:00.000NULL331472PRAG139TrueNULL
2402921182008-01-03 00:00:00.0001899-12-30 09:30:00.0001899-12-30 12:15:00.000NULL331472PRAG139TrueNULL
2403220512008-01-03 00:00:00.0001899-12-30 09:30:00.0001899-12-30 14:30:00.000NULL221472PRAG1107TrueNULL
2403322242008-01-03 00:00:00.0001899-12-30 09:30:00.0001899-12-30 14:00:00.000NULL321472PRAG172TrueNULL
240343532008-01-03 00:00:00.0001899-12-30 09:45:00.0001899-12-30 13:15:00.000NULL2220112PRAG139TrueNULL
2403521282008-01-03 00:00:00.0001899-12-30 09:45:00.0001899-12-30 14:45:00.000NULL331472PRAG139TrueNULL
240369012008-01-03 00:00:00.0001899-12-30 09:45:00.0001899-12-30 14:30:00.000NULL221472PRAG1107TrueNULL
240379042008-01-03 00:00:00.0001899-12-30 09:30:00.0001899-12-30 14:00:00.000NULL211472PRAG1107TrueNULL
2403921242008-01-03 00:00:00.0001899-12-30 09:45:00.0001899-12-30 10:30:00.000NULL221472PRAG1107TrueNULL
240422692008-01-03 00:00:00.0001899-12-30 09:45:00.0001899-12-30 12:45:00.000NULL331472PRAG139TrueNULL
242835852008-01-03 00:00:00.0001899-12-30 10:00:00.0001899-12-30 11:00:00.000NULL321472PRAG172TrueNULL
January 11, 2012 at 1:31 am
The problem is your DATETIME columns contain a date part as well as a time. When you write "time_out <= '9:30 am'" (with no date specified), SQL Server uses the default date which is '1900-01-01'. Taking the implied date into account, it is easy to see why records from '1899-12-30' (+ any time compoent) are less than '1900-01-01 09:30:00'. Example:
DECLARE @Example TABLE
(
time_out datetime NULL
)
INSERT @Example
(time_out)
VALUES
('1899-12-30 1:08:15 PM'),
('1899-12-30 4:30:00 PM'),
('1899-12-30 8:00:00 PM'),
('1899-12-30 9:00:00 AM'),
('1899-12-30 12:06:15 PM'),
('1899-12-30 12:02:37 PM'),
('1899-12-30 11:31:39 AM'),
('1899-12-30 10:22:09 AM');
SELECT
e.time_out
FROM @Example AS e
WHERE
e.time_out <= '9:30 am'
One way to fix this is to use the TIME data type instead of DATETIME:
DECLARE @Example TABLE
(
time_out time(0) NULL
)
INSERT @Example
(time_out)
VALUES
(CONVERT(time(0), '13:08:15', 108)),
(CONVERT(time(0), '16:30:00', 108)),
(CONVERT(time(0), '20:00:00', 108)),
(CONVERT(time(0), '09:00:00', 108)),
(CONVERT(time(0), '12:06:15', 108)),
(CONVERT(time(0), '12:02:37', 108)),
(CONVERT(time(0), '11:31:39', 108)),
(CONVERT(time(0), '10:22:09', 108));
SELECT
e.time_out
FROM @Example AS e
WHERE
e.time_out <= CONVERT(time(0), '09:30:00', 108);
Another advantage here is that TIME only requires 3-5 bytes (depending on fractional seconds precision specified, so 3 bytes in the example above). DATETIME needs 8 bytes.
Another alternative would be to add a CHECK constraint to the 'time' columns using DATETIME to constrain the actual values to be >= '1900-01-01' and < '1900-01-02'. This is more workaround than solution - the TIME solution is much more satisfactory.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2012 at 8:47 am
Thank you. This seems to be the trick. Now to get the query to give info that the manager wants.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply