October 17, 2011 at 8:55 am
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
October 17, 2011 at 9:31 am
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.
October 17, 2011 at 9:36 am
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
October 17, 2011 at 10:00 am
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.
October 17, 2011 at 10:21 am
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.
October 17, 2011 at 10:26 am
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
October 17, 2011 at 10:37 am
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.
October 17, 2011 at 11:54 am
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.
October 17, 2011 at 11:58 am
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.
October 17, 2011 at 12:20 pm
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
October 17, 2011 at 12:46 pm
Expected results based on the sample data?
October 17, 2011 at 12:58 pm
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
October 17, 2011 at 1:05 pm
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!)
October 17, 2011 at 1:08 pm
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
October 17, 2011 at 1:10 pm
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