April 1, 2011 at 5:53 am
Okay, now I'm getting a table back, but the data isn't correct.
From running my clunky procedure for, let's say the first three days of March, I get this:
(Columns are Date, 1st shift transport, 1st shift orderly, 2nd shift transport, 2nd shift orderly, 3rd shift transport, 3rd shift orderly, in that order).
3/1/2011 128 14 75 21 20 11
3/2/2011 141 21 57 19 13 10
3/3/2011 112 16 67 19 14 15
From running your more streamlined query, I get this: (same order of columns)
2011-03-01 00:00:00.0001110141192159
2011-03-02 00:00:00.000106211321942
2011-03-03 00:00:00.000159161071953
This is running on the same database.
April 1, 2011 at 6:22 am
Can you post the data you used?
Far away is close at hand in the images of elsewhere.
Anon.
April 1, 2011 at 6:25 am
There's going to be an awful lot of it...you sure you want all of it?
There's 700 rows in that time range with the appropriate [type] field that we're looking at in the query.
April 1, 2011 at 6:29 am
Hey, I just noticed something. Check out the last field in each of your rows compared to the first field in each of my rows. They match.
April 1, 2011 at 6:31 am
Wait, I think I'm on to something. A little more messing around here oughta fix it.
April 1, 2011 at 6:44 am
pdonley (4/1/2011)
Wait, I think I'm on to something. A little more messing around here oughta fix it.
If you still having probs then by all means attach the 700 rows as a file
p.s. attachments can be done using the Edit Attachments button in the Post Options box underneath the post.
Far away is close at hand in the images of elsewhere.
Anon.
April 1, 2011 at 7:07 am
The data is closer now, but still not quite the same.
Here's how I modified the original code:
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 ELSE 0 END,
DATEDIFF(day,0,timestampCompleted)) AS [Day],
-- Set Orderly or Transport type
CASE WHEN [type] = 1 THEN 'O'
WHEN [type] IN (2,3,4,6) THEN 'T'
END AS [Type],
CASE WHEN CONVERT(char(8),timestampCompleted,108) < '14:30:00' THEN 1
WHEN CONVERT(char(8),timestampCompleted,108) < '22:30:00' THEN 2
WHEN CONVERT(char(8),timestampCompleted,108) < '06:30:00' THEN 3
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)
AND class = 4
) a
GROUP BY [Day]
ORDER BY [Day]
I'm attaching the relevant data as a .txt file.
The data didn't export with the column names, so they are id, class (always 4), type (1 is orderly, everything else is transport in this particular subset of data), timestampCompleted
April 1, 2011 at 7:48 am
First, you changed the CASE statement for Shift Why? This would be why the results do not match.
Second I may have missed a bit of logic in one of your previous posts ie
timestampCompleted != timestampOpen
is this important?
Do the results you posted for comparison use the same 700 rows you posted?
Far away is close at hand in the images of elsewhere.
Anon.
April 1, 2011 at 7:58 am
I changed the CASE statement for shift because it was reporting times prior to 06:30 as 1st shift - that's actually third shift (first doesn't start until 6:30). It also reported times prior to 14:30 as 2nd shift, when that would actually be first shift (6:30A - 2:30P) and it reported times prior to 22:30 as 3rd shift, when that's actually 2nd shift (2:30P-10:30P), and then times after that were listed as first shift, when they'd actually be third shift. So it just moved some columns around.
Did I change it incorrectly?
The timestampCompleted != timestampOpen - I forgot about that. If those two values match each other, then that means that particular event was cancelled, and we don't want to include cancelled events in this count.
The results I posted for comparison were on that data yes, but prior to some changes I made. Here is a more recent comparison, based on that exact date. Sorry, should have included this before.
Yours:
2011-03-01 00:00:00.00012720592125
2011-03-02 00:00:00.00013830421901
2011-03-03 00:00:00.00011225531946
Mine:
2011-03-01 00:00:00.0001281475212011
2011-03-02 00:00:00.0001412157191310
2011-03-03 00:00:00.0001121667191415
They're really close - I wonder if that cancelled event bit of logic will finish it out?
By the way, I REALLY appreciate all your help. I'm learning a lot of ideas from you through this that I'm going to be able to use in other places...such as...putting a function call in as the second argument of a DATEADD function? TOTALLY never thought of that before...ever. I can think of some uses for that. Maybe I can clean up some of my other functions around here.
Thanks.
April 1, 2011 at 8:19 am
Ah OK now I see, my bad, got the Shift numbers wrong.
Your change was close but CASE statements check in the order the WHEN is specified, so the test for < '06:30:00' must be before < '14:30:00' otherwise < '14:30:00' would trap the < '06:30:00' data by mistake.
I updated the sql my original post to avoid confusion over several attempts to also correct another error.
The file you supplied only contains 534 rows and I cannot get the same results you posted.
If I remove the [type] IN (1,2,3,4,6) test I get closer. Are you restricting Type or processing all Types?
Far away is close at hand in the images of elsewhere.
Anon.
April 4, 2011 at 8:28 am
Was out of the office over the weekend. I will try your fix. The types just determine whether the event was an orderly or transport event. The only type that I am completely ignoring is 5. (5 is a bed request, and is for a different part of the system.) So, types 1 and 6 are orderly events (I see I missed the 6 initially. I'll fix that), and types 2, 3, 4, and 7 are transports.
April 4, 2011 at 8:31 am
No, the data still doesn't match up, and I'm getting an extra day out of it.
Yours:
2011-03-01 00:00:00.00011914592186
2011-03-02 00:00:00.000132214219814
2011-03-03 00:00:00.000107165319510
2011-03-04 00:00:00.000000046
Mine:
2011-03-01 00:00:00.0001281475212011
2011-03-02 00:00:00.0001412157191310
2011-03-03 00:00:00.0001121667191415
April 4, 2011 at 10:30 am
I think it is the data you posted.
The file has 534 rows whereas the total of your counts comes to 773.
Far away is close at hand in the images of elsewhere.
Anon.
April 4, 2011 at 10:32 am
I just ran your script and mine on the exact same set of data? I'm confused.
April 4, 2011 at 11:05 am
I corrected my posted code for type 6.
You have a problem with your query, your end times are not accurate enough ie
you should be using 14:29:59.997 not 14:29:00.000
it would be better to test for < '06:30:00' ie
WHERE ([type] = 1 OR [type] = 6)
AND class = 4 AND timestampCompleted >= (CONVERT(varchar(8), @currentDate, 112) + ' 06:30:00.000')
AND timestampCompleted < (CONVERT(varchar(8), @currentDate, 112) + ' 14:30:00.000')
AND timestampCompleted != timestampOpen
For the data.txt (534 rows) you posted your corrected code (for above) and my corrected code produce the same results. Remember your code includes timestampCompleted != timestampOpen mine does not.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply