March 30, 2011 at 4:00 pm
Your expected results and ColdCoffee's are very similar.
Your results:
Day 1st shift 2nd Shift 3rd Shift
3/1 3 1 1
3/2 2 1 0
3/3 0 0 0
Cold Coffee's:
DayNumberFirstShiftEventCountMiddleShiftEventCountLateShiftEventCount
Day 1360
Day 2360
Day 3361
Why don't you modify his code to get your results? I would venture to say your method is not standard operating procedure while cold coffee's is.
Do you need help modifying ColdCoffee's code?
March 31, 2011 at 6:35 am
I'm afraid I don't understand ColdCoffee's code. It's a little too advanced for me.
March 31, 2011 at 9:18 am
DECLARE @startDate datetime;
SET @startDate = '2011-03-01 00:00:00.000';
DECLARE @endDate datetime;
SET @endDate = '2011-03-03 00:00:00.000';
SELECT [Day],
SUM(CASE WHEN [Type]='T' AND Shift=1 THEN 1 ELSE 0 END) AS [firstTransport],
SUM(CASE WHEN [Type]='O' AND Shift=1 THEN 1 ELSE 0 END) AS [firstOrderly],
SUM(CASE WHEN [Type]='T' AND Shift=2 THEN 1 ELSE 0 END) AS [secondTransport],
SUM(CASE WHEN [Type]='O' AND Shift=2 THEN 1 ELSE 0 END) AS [secondOrderly],
SUM(CASE WHEN [Type]='T' AND Shift=3 THEN 1 ELSE 0 END) AS [thirdTransport],
SUM(CASE WHEN [Type]='O' AND Shift=3 THEN 1 ELSE 0 END) AS [thirdOrderly]
FROM (
-- Strip off time from timestamp
SELECT DATEADD(day,
-- Adjust timestamp to prev day if before 6:30AM
CASE WHEN CONVERT(char(8),timestampCompleted,108) < '06:30:00' THEN -1
WHEN CONVERT(char(8),timestampCompleted,108) >= '22:30:00' THEN 1
ELSE 0 END,
DATEDIFF(day,0,timestampCompleted)) AS [Day],
-- Set Orderly or Transport type
CASE WHEN [type] IN (1,6) THEN 'O'
ELSE 'T'
END AS [Type],
CASE WHEN CONVERT(char(8),timestampCompleted,108) < '06:30:00' THEN 3
WHEN CONVERT(char(8),timestampCompleted,108) < '14:30:00' THEN 1
WHEN CONVERT(char(8),timestampCompleted,108) < '22:30:00' THEN 2
ELSE 3
END AS [Shift]
FROM [Events]
-- Select data according to rules
WHERE timestampCompleted >= DATEADD(hour,6,DATEADD(minute,30,@startDate))
AND timestampCompleted < DATEADD(hour,6,DATEADD(minute,30,DATEADD(day,1,@endDate)))
AND [type] IN (1,2,3,4,6,7)
AND class = 4
) a
GROUP BY [Day]
ORDER BY [Day]
Far away is close at hand in the images of elsewhere.
Anon.
March 31, 2011 at 9:23 am
I'm getting a syntax error on that one inside the SUM() functions. THEN ELSE END...it says incorrect syntax near the keyword "ELSE"
March 31, 2011 at 9:27 am
pdonley (3/31/2011)
I'm getting a syntax error on that one inside the SUM() functions. THEN ELSE END...it says incorrect syntax near the keyword "ELSE"
Sorry my bad :blush:
Was in a hurry and didn't test the code
I have edited my original post
Far away is close at hand in the images of elsewhere.
Anon.
March 31, 2011 at 9:30 am
I think you might need to change it to this...
SUM(CASE WHEN [Type]='O' AND Shift=1 THEN 1 ELSE 0 END) AS [firstOrderly],
etc.
but I could very easily be wrong
March 31, 2011 at 9:31 am
In a hurry? And you came up with that? I couldn't have done that if I had a month. Thanks!
One question though, where do I tell it what table to pull those fields from? For me its called [Events]
Thanks.
March 31, 2011 at 9:33 am
Changed the code add FROM clause
Far away is close at hand in the images of elsewhere.
Anon.
March 31, 2011 at 9:37 am
Okay, now it works GREAT on my test system...which...uses SQL Server 2008. But it doesn't work on SQL Server 2005, which is my prod environment. (I know I shouldn't have different versions of the database for test versus prod environments...but that's what they gave me.)
It says it doesn't know what the "TIME" type is. Should I just replace it with datetime?
March 31, 2011 at 9:42 am
Try replacing
CAST(timestampCompleted as TIME)
to
CONVERT(char(8),timestampCompleted,108)
Far away is close at hand in the images of elsewhere.
Anon.
March 31, 2011 at 9:44 am
Can I still use the < function with two varchar values then? I'll give it a shot.
March 31, 2011 at 9:49 am
I'm not sure it's working...I'm getting different results with that than I am with my own clunky method...and when I specify a date range of '2011-03-01 00:00:00.000' to '2011-03-03 00:00:00.000', it's giving me everything up to the 5th.
March 31, 2011 at 10:08 am
Sorry error in Day calc
Try the posted code now.
Far away is close at hand in the images of elsewhere.
Anon.
March 31, 2011 at 2:42 pm
Much closer, but now we're starting a day before the start date.
March 31, 2011 at 5:49 pm
OK try replacing WHERE line with
WHERE timestampCompleted >= DATEADD(hour,6,DATEADD(minute,30,@startDate))
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply