date columns based query

  • 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

  • Please read and follow the recommendation given in the first link in my signature.

    It will help us help you.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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

  • Edit: duplicate post.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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