Sort Data by Shift

  • I have a request to sort a table by shift. Basically, I have data that has a datetime field in it and I would like to group the data into 3 different temporary tables sorted by the specific work shift, obviously grouped into 3 shifts at a given time. Say 1st shift work from 7:00AM to 4:00PM, the Second shift works from 4:00PM to 1:00AM, and the 3rd shift works from 1:00AM to 7:00AM. Sounds simple enough, but for the life of me all I can do is an aggregate. This is not what I want, I want to actually sort the entire dataset by shift, place them in three different tables accordingly, and then be able to perform other sorts on those temp tables.

    Select COUNT(SerialNumber),

    CASE WHEN DATEPART(hour,(DateTested)) between 7 AND 16 THEN '1st SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 16 AND 1 THEN '2nd SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 1 AND 7 THEN '3rd SHIFT'

    END

    FROM TestHeader WHERE StatusID = '1'

    GROUP BY CASE WHEN DATEPART(hour,(DateTested)) between 7 AND 16 THEN '1st SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 16 AND 1 THEN '2nd SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 1 AND 7 THEN '3rd SHIFT'

    END

    Thanks

    Chris

  • Not sure why you're doing an aggregate or a group by or one query...if you want the data separated into 3 temp tables, then copy them into 3 temp tables. Unless I'm not getting the requirements. 😎

    select * into #First_Shift from TestHeader where DATEPART(hour,(DateTested)) between 7 AND 16;

    select * into #Second_Shift from TestHeader where DATEPART(hour,(DateTested)) between 16 AND 1;

    select * into #Third_Shift from TestHeader where DATEPART(hour,(DateTested)) between 1 AND 7;

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • Personally, I would add a column to the original table denoting the shift. Make it an INT NOT NULL. Maybe even make it a calculated column based on your scenarios. Then go from there.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • I didn't think about it at first, but calculating the time like "where DATEPART(hour,(DateTested)) between 7 AND 16;" won't work. Use something like "where CAST(DateTested as Time) between '07:00' AND '16:00';"

    But I also rather jared's idea of using a calculated column.

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • Select COUNT(SerialNumber),

    CASE WHEN DATEPART(hour,(DateTested)) between 7 AND 16 THEN '1st SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 16 AND 1 THEN '2nd SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 1 AND 7 THEN '3rd SHIFT'

    END

    FROM TestHeader WHERE StatusID = '1'

    GROUP BY CASE WHEN DATEPART(hour,(DateTested)) between 7 AND 16 THEN '1st SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 16 AND 1 THEN '2nd SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 1 AND 7 THEN '3rd SHIFT'

    END

    Does anyone else see a problem with this code? If not, take a close look at the BETWEENs, there are going to be overlaps where the hour is 7, 16, and 1. Also, there will be nothing selected for the second BETWEEN clause as it is doing BETWEEN 16 and 1.

  • Lynn Pettis (10/17/2011)


    Select COUNT(SerialNumber),

    CASE WHEN DATEPART(hour,(DateTested)) between 7 AND 16 THEN '1st SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 16 AND 1 THEN '2nd SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 1 AND 7 THEN '3rd SHIFT'

    END

    FROM TestHeader WHERE StatusID = '1'

    GROUP BY CASE WHEN DATEPART(hour,(DateTested)) between 7 AND 16 THEN '1st SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 16 AND 1 THEN '2nd SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 1 AND 7 THEN '3rd SHIFT'

    END

    Does anyone else see a problem with this code? If not, take a close look at the BETWEENs, there are going to be overlaps where the hour is 7, 16, and 1. Also, there will be nothing selected for the second BETWEEN clause as it is doing BETWEEN 16 and 1.

    Yes, but my feeling was that it has to be rewritten anyway. Get rid of the case statements, use a "proper" methodology to get the shift numbers, and store that in a column.

    Jared

    Jared
    CE - Microsoft

  • jared-709193 (10/17/2011)


    Lynn Pettis (10/17/2011)


    Select COUNT(SerialNumber),

    CASE WHEN DATEPART(hour,(DateTested)) between 7 AND 16 THEN '1st SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 16 AND 1 THEN '2nd SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 1 AND 7 THEN '3rd SHIFT'

    END

    FROM TestHeader WHERE StatusID = '1'

    GROUP BY CASE WHEN DATEPART(hour,(DateTested)) between 7 AND 16 THEN '1st SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 16 AND 1 THEN '2nd SHIFT'

    WHEN DATEPART(hour,(DateTested)) between 1 AND 7 THEN '3rd SHIFT'

    END

    Does anyone else see a problem with this code? If not, take a close look at the BETWEENs, there are going to be overlaps where the hour is 7, 16, and 1. Also, there will be nothing selected for the second BETWEEN clause as it is doing BETWEEN 16 and 1.

    Yes, but my feeling was that it has to be rewritten anyway. Get rid of the case statements, use a "proper" methodology to get the shift numbers, and store that in a column.

    Jared

    I'll agree that it should be rewritten, but I also think we need to ppoint out the error in the basic logic.

    cdelgross, if you would like better answers to solving this problem, please post the DDL for the table(s) involved, sample data for the table(s), and expected results based on the sample data.

    Also, please be sure to read the first article I reference below in my signature block regarding asking for help. It will show you how to post the information I have requested.

  • I would love to do a CAST(DateTested as Time) but that data type (Time) is not supported in SQL 2005 (unfortunately this is not something I can control, legacy data and all). I cannot alter the schema of the table either.

    What I can do is add views, sotred procedures, and user functions to accomplish what I need to do, which is sort the data by shift. The shifts are as stated

    7:01AM to 4:30PM -1st shift

    4:31PM to 1:10AM - 2nd Shift

    1:11AM to 7:00AM - 3rd Shift

    Sorry I cannot post any of the data either due to NDA.

  • cdelgross (10/17/2011)


    I would love to do a CAST(DateTested as Time) but that data type (Time) is not supported in SQL 2005 (unfortunately this is not something I can control, legacy data and all). I cannot alter the schema of the table either.

    What I can do is add views, sotred procedures, and user functions to accomplish what I need to do, which is sort the data by shift. The shifts are as stated

    7:01AM to 4:30PM -1st shift

    4:31PM to 1:10AM - 2nd Shift

    1:11AM to 7:00AM - 3rd Shift

    Sorry I cannot post any of the data either due to NDA.

    Not asking for actual data, asking for sample data. You should be able to put together something to help us help you. Based on your description, it is difficult to determine what you are trying to accomplish and what the expected results of any query we develop should return.

  • Here you go:

    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

    DROP TABLE #mytable

    CREATE TABLE #mytable

    (HeaderID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    SerialNumber varchar(15),

    DateTested DATETIME,

    OperatorID INT,

    StationID INT )

    SET IDENTITY_INSERT #mytable ON

    INSERT INTO #mytable

    (HeaderID, SerialNumber, DateTested, OperatorID, StationID)

    SELECT '1','B101121588','3/15/2010 5:56:07 PM','8','1' UNION ALL

    SELECT '2','B094009083','3/15/2010 6:46:14 PM','17','1' UNION ALL

    SELECT '3','B101121612','3/16/2010 7:27:18 AM','21','2' UNION ALL

    SELECT '4','B101121590','3/16/2010 7:29:30 AM','9','2' UNION ALL

    SELECT '5','B101120343','3/16/2010 7:31:11 AM','7','1' UNION ALL

    SELECT '6','B101121612','3/16/2010 2:37:55 PM','18','3' UNION ALL

    SELECT '7','B101121618','3/16/2010 8:46:03 PM','16','3' UNION ALL

    SELECT '8','B101121611','3/17/2010 7:31:11 AM','16','2' UNION ALL

    SELECT '9','B101121605','3/17/2010 11:10:01 PM','16','1' UNION ALL

    SELECT '10','B101121603','3/18/2010 12:06:31 AM','16','2' UNION ALL

    SELECT '11','B101121627','3/18/2010 2:06:31 AM','16','2' UNION ALL

    SELECT '12','B101120327','3/18/2010 7:31:11 AM','16','1' UNION ALL

    SELECT '13','B101120328','3/18/2010 9:02:13 AM','16','1' UNION ALL

    SELECT '14','B101120281','3/18/2010 4:55:08 PM','16','1' UNION ALL

    SELECT '15','B101120328','3/23/2010 5:43:55 PM','16','3' UNION ALL

    SELECT '16','B101121618','3/23/2010 9:11:26 PM','16','3' UNION ALL

    SELECT '17','B101121603','3/27/2010 8:47:26 AM','16','3' UNION ALL

    SELECT '18','B101121605','3/20/2010 7:31:11 AM','16','3' UNION ALL

    SELECT '19','B101121619','3/27/2010 12:02:41 PM','16','1' UNION ALL

    SELECT '20','B101120308','3/27/2010 1:51:42 AM','16','2'

    SET IDENTITY_INSERT #mytable OFF

  • Expected results based on the sample data?

  • Data sorted by shift...

    The shifts are as stated :

    7:01AM to 4:30PM -1st shift

    4:31PM to 1:10AM - 2nd Shift

    1:11AM to 7:00AM - 3rd Shift

  • Sorry, should be more explicit:

    Based on a similar table, adding a column to the schema for shift number ('1' being 1st shift,'2' being 2nd shift ,'3' being 3rd shift).

    CREATE TABLE #sortedtable

    (HeaderID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    SerialNumber varchar(15),

    DateTested DATETIME,

    OperatorID INT,

    StationID INT ,

    ShiftNumber INT)

    '1','B101121588','3/15/2010 5:56:07 PM','8','1','2'

    '2','B094009083','3/15/2010 6:46:14 PM','17','1','2'

    '3','B101121612','3/16/2010 7:27:18 AM','21','2' '1'

    '4','B101121590','3/16/2010 7:29:30 AM','9','2' '1'

    '5','B101120343','3/16/2010 7:31:11 AM','7','1' '1'

    '6','B101121612','3/16/2010 2:37:55 PM','18','3' '1'

    '7','B101121618','3/16/2010 8:46:03 PM','16','3' '2'

    '8','B101121611','3/17/2010 7:31:11 AM','16','2' '1'

    '9','B101121605','3/17/2010 11:10:01 PM','16','1' '2'

    '10','B101121603','3/18/2010 12:06:31 AM','16','2' '2'

    '11','B101121627','3/18/2010 2:06:31 AM','16','2' '3'

    '12','B101120327','3/18/2010 7:31:11 AM','16','1' '1'

    '13','B101120328','3/18/2010 9:02:13 AM','16','1' '1'

    '14','B101120281','3/18/2010 4:55:08 PM','16','1' '2'

    '15','B101120328','3/23/2010 5:43:55 PM','16','3' '2'

    '16','B101121618','3/23/2010 9:11:26 PM','16','3' '2'

    '17','B101121603','3/27/2010 8:47:26 AM','16','3' '1'

    '18','B101121605','3/20/2010 7:31:11 AM','16','3' '1'

    '19','B101121619','3/27/2010 12:02:41 PM','16','1' '2'

    '20','B101120308','3/27/2010 1:51:42 AM','16','2','3'

    (hopefully I did not make any errors in shifts!)

  • SELECT HeaderID, SerialNumber, DateTested, OperatorID, StationID,

    CASE

    WHEN CONVERT(VARCHAR(8),DateTested,108) >= '07:01:00' AND CONVERT(VARCHAR(8),DateTested,108) < '16:31:00' THEN '1st Shift'

    WHEN CONVERT(VARCHAR(8),DateTested,108) >= '01:11:00' AND CONVERT(VARCHAR(8),DateTested,108) < '07:01:00' THEN '3rd Shift'

    ELSE '2nd Shift'

    END AS Shift

    FROM #myTable

    ORDER BY

    CASE

    WHEN CONVERT(VARCHAR(8),DateTested,108) >= '07:01:00' AND CONVERT(VARCHAR(8),DateTested,108) < '16:31:00' THEN '1st Shift'

    WHEN CONVERT(VARCHAR(8),DateTested,108) >= '01:11:00' AND CONVERT(VARCHAR(8),DateTested,108) < '07:01:00' THEN '3rd Shift'

    ELSE '2nd Shift'

    END

    EDIT for 2008:SELECT HeaderID, SerialNumber, DateTested, OperatorID, StationID,

    CASE

    WHEN CONVERT(TIME,DateTested,108) >= '07:01:00' AND CONVERT(TIME,DateTested,108) < '16:31:00' THEN '1st Shift'

    WHEN CONVERT(TIME,DateTested,108) >= '01:11:00' AND CONVERT(TIME,DateTested,108) < '07:01:00' THEN '3rd Shift'

    ELSE '2nd Shift'

    END AS Shift

    FROM #myTable

    ORDER BY

    CASE

    WHEN CONVERT(TIME,DateTested,108) >= '07:01:00' AND CONVERT(TIME,DateTested,108) < '16:31:00' THEN '1st Shift'

    WHEN CONVERT(TIME,DateTested,108) >= '01:11:00' AND CONVERT(TIME,DateTested,108) < '07:01:00' THEN '3rd Shift'

    ELSE '2nd Shift'

    END

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • Okay, I'm sure we could come up with seberal versions of the data sorted by shift. It eould help if you showed us what you rxpect based on the sample data you have provided.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply