October 16, 2009 at 11:48 am
I have a datecolumn in this format 2009-10-15 16:57:00 in a table.
I need to write a query which takes this date column values and classifies rows based on the times.
Criteria is . I have 8 hr shifts for each day . 6am - 2pm...morning 2pm - 10pm...evening
10pm - 6am .... night shift.
My table has 100's of values from every shift and it gets updated every 5minutes . But now if I am between 6am and 2pm ..my query should retrieve only rows between todays that shift i.e only for todays date .. and similarly for evening and night.
So if it is 7am on 16th oct.My query should retrieve todays values from morning shift of oct16th only.Similary for any days evening and night shifts also.
My results will be in this format:
dateshift
2009-10-15 16:57:00 evening
....
.....
Thanks
October 16, 2009 at 12:50 pm
Please read and follow the recommendation given in the first link in my signature.
It will help us help you.
October 16, 2009 at 12:54 pm
You didn't provide any test data, here's some courtesy of Jeff:
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Jeff Moden
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
INTO dbo.ShiftTest
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2
--===== Add the Primary Key
ALTER TABLE dbo.ShiftTest
ADD PRIMARY KEY CLUSTERED (RowNum)
Here is a partially tested solution (to be honest I got bored halfway through, you'd get answers from more industrious individuals if you posted table definitions, sample data readily consumed and non-ambiguous requirements!), however, if I had to do this properly I'd probably go for a Calender solution. But this worked pretty good on the million rows.
DECLARE @DateTimeNow DATETIME
SELECT @DateTimeNow = '2009-10-16 13:00'
SELECT * FROM ShiftTest
WHERE
SomeDate >=
CASE
WHEN --Morning shift
@DateTimeNow >= DATEADD(HH, 6, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
AND @DateTimeNow < DATEADD(HH, 14, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
THEN DATEADD(HH, 6, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
WHEN --afternoon shift
@DateTimeNow >= DATEADD(HH, 14, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
AND @DateTimeNow < DATEADD(HH, 22, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
THEN DATEADD(HH, 14, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
WHEN --night shift 1
@DateTimeNow < DATEADD(HH, 6, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
THEN DATEADD(HH, -2, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
WHEN --night shift 2
@DateTimeNow >= DATEADD(HH, 22, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
THEN DATEADD(HH, 22, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
END
AND SomeDate <
CASE
WHEN --Morning shift
@DateTimeNow >= DATEADD(HH, 6, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
AND @DateTimeNow < DATEADD(HH, 14, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
THEN DATEADD(HH, 14, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
WHEN --afternoon shift
@DateTimeNow >= DATEADD(HH, 14, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
AND @DateTimeNow < DATEADD(HH, 22, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
THEN DATEADD(HH, 22, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
WHEN --night shift 1
@DateTimeNow < DATEADD(HH, 6, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
THEN DATEADD(HH, 6, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
WHEN --night shift 2
@DateTimeNow >= DATEADD(HH, 22, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
THEN DATEADD(HH, 30, DATEADD(DD,0, DATEDIFF(DD, 0, @DateTimeNow)))
END
October 16, 2009 at 12:57 pm
October 16, 2009 at 1:46 pm
sql2000-915039 (10/16/2009)
I have a datecolumn in this format 2009-10-15 16:57:00 in a table.I need to write a query which takes this date column values and classifies rows based on the times.
Criteria is . I have 8 hr shifts for each day . 6am - 2pm...morning 2pm - 10pm...evening
10pm - 6am .... night shift.
My table has 100's of values from every shift and it gets updated every 5minutes . But now if I am between 6am and 2pm ..my query should retrieve only rows between todays that shift i.e only for todays date .. and similarly for evening and night.
So if it is 7am on 16th oct.My query should retrieve todays values from morning shift of oct16th only.Similary for any days evening and night shifts also.
My results will be in this format:
dateshift
2009-10-15 16:57:00 evening
....
.....
Thanks
Do you have only one date per row or do you have a StartDateTime and EndDateTime? The suggestion of you posting some sample data using the techniques found in the first link in my signature below would go a long way in helping us to help you.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2009 at 6:33 pm
Exactly 1 column(datecolumn).
The output I need from a t-sql query is in this format .
Datecolumn Shift
2009-10-15 16:31:00 evening
2009-10-15 16:07:00 evening
2009-10-07 08:58:00 day
Thanks
October 17, 2009 at 7:52 am
Hi,
had a closer look at this, basically I see two main solutions, compute the shift name with a case statement, which is fine when the shift patterns never change, or a calendar table based solution.
I implemented the two solutions, given the shift patterns specified above, and compared execution times:
Records Calendar Computed
T (ms) T (ms)
10 86 2
100 337.33 3
1,000 201 5
10,000 263 27.33
100,000 441 191.67
1000,000 1050 1751
So the calendar solution is a lot poorer for smaller data sets, but there is a tipping point, around 220,000 on my system, when the calendar solution comes into its own.
Please note that an index is required on the oject table (ShiftTest in my code) to get this performance, there are also a couple of other indexes on calendar table which help out too.
I have a question: the ShiftCalendar table, is there any way to specify a constraint on shifts NOT overlapping?
Here is the code:
--For benchmarking
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--My test database
USE Test
--test data courtest of Jeff
IF OBJECT_ID('dbo.ShiftTest') IS NOT NULL
DROP TABLE ShiftTest
SELECT TOP 220000
RowNum = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
INTO dbo.ShiftTest
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2
--===== Add the Primary Key
ALTER TABLE dbo.ShiftTest
ADD PRIMARY KEY CLUSTERED (RowNum)
--This turned Cluster index scan on ShiftTest to an Index seek, gives major performance boost
CREATE NONCLUSTERED INDEX IX_ShiftTest_SomeDate ON ShiftTest (SomeDate)
--Create ShiftType table, purpose is key value pair, and also to help populate ShiftCalender
IF OBJECT_ID('dbo.ShiftType') IS NOT NULL
DROP TABLE ShiftType
CREATE TABLE ShiftType
(
ShiftTypeValue INT NOT NULL,
ShiftTypeName VARCHAR(32) NOT NULL,
ShiftTypeStart INT NOT NULL, --purely for populating ShiftCalendar
ShiftTypeEnd INT NOT NULL --purely for populating ShiftCalendar
)
INSERT INTO ShiftType
SELECT 1, 'day', 6, 14 UNION
SELECT 2, 'evening', 14, 22 UNION
SELECT 3, 'night',22, 30
ALTER TABLE ShiftType ADD PRIMARY KEY CLUSTERED (ShiftTypeValue)
--Start date for beginning of ShiftCalendar
DECLARE @StartDate DATETIME
SELECT @StartDate = '1979-12-31 00:00'
--Create ShiftCalendar
IF OBJECT_ID('dbo.ShiftCalendar') IS NOT NULL
DROP TABLE ShiftCalendar
CREATE TABLE ShiftCalendar
(
ShiftStart DATETIME NOT NULL,
ShiftEnd DATETIME NOT NULL,
ShiftTypeValue INT NOT NULL
)
INSERT INTO ShiftCalendar
SELECT
DATEADD(HH, ShiftTypeStart, DATEADD(DD, N, @StartDate)),
DATEADD(HH, ShiftTypeEnd, DATEADD(DD, N, @StartDate)),
ShiftTypeValue
FROM Master.dbo.Tally
CROSS JOIN ShiftType
--This isn't really a decent primary key, but prevents a table scan on ShiftCalendar, v. little effect on performance
--What I really want here is some kind of constraint which prevent shifts overlapping — and suggestions?
ALTER TABLE ShiftCalendar ADD PRIMARY KEY CLUSTERED (ShiftStart, ShiftEnd)
--Following indeces give moderate performance boost
CREATE NONCLUSTERED INDEX [IX_ShiftCalendar_ShiftStart] ON [dbo].[ShiftCalendar]
(
[ShiftStart] ASC
) INCLUDE ( [ShiftTypeValue])
CREATE NONCLUSTERED INDEX [IX_ShiftCalendar_ShiftEnd] ON [dbo].[ShiftCalendar]
(
[ShiftEnd] ASC
) INCLUDE ( [ShiftTypeValue])
IF OBJECT_ID('tempdb.dbo.#Test1') IS NOT NULL
DROP TABLE #Test1
DECLARE @QueryStart DATETIME
SELECT @QueryStart = GETDATE()
SELECT SomeDate, ShiftTypeName
INTO #Test1
FROM ShiftTest
JOIN ShiftCalendar ON SomeDate >= ShiftStart AND SomeDate < ShiftEnd
JOIN ShiftType ON ShiftCalendar.ShiftTypeValue = ShiftType.ShiftTypeValue
PRINT DATEDIFF(MS, @QueryStart, GETDATE())
IF OBJECT_ID('tempdb.dbo.#Test2') IS NOT NULL
DROP TABLE #Test2
SELECT @QueryStart = GETDATE()
--For bench mark - compute scalar
SELECT SomeDate,
CASE
WHEN --Morning shift
SomeDate >= DATEADD(HH, 6, DATEADD(DD,0, DATEDIFF(DD, 0, SomeDate)))
AND SomeDate < DATEADD(HH, 14, DATEADD(DD,0, DATEDIFF(DD, 0, SomeDate)))
THEN 'day'
WHEN --afternoon shift
SomeDate >= DATEADD(HH, 14, DATEADD(DD,0, DATEDIFF(DD, 0, SomeDate)))
AND SomeDate < DATEADD(HH, 22, DATEADD(DD,0, DATEDIFF(DD, 0, SomeDate)))
THEN 'evening'
WHEN --night shift 2
SomeDate >= DATEADD(HH, 22, DATEADD(DD,0, DATEDIFF(DD, 0, SomeDate)))
OR SomeDate < DATEADD(HH, 6, DATEADD(DD,0, DATEDIFF(DD, 0, SomeDate)))
THEN 'night'
END Shift
INTO #Test2
FROM ShiftTest
PRINT DATEDIFF(MS, @QueryStart, GETDATE())
///Edit formatting issue, any ideas on how to post HTML here?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply