October 4, 2016 at 5:17 am
This is what I came up with.
;with Data
as
(
selectAccountNumber,
DischargeDateTime,
RegistrationDateTime,
datediff(minute, RegistrationDateTime, DischargeDateTime) as TAT
from #Test
),
TAT
as
(
SELECTWeekEnding,
TAT,
row_number() over(partition by WeekEnding order by TAT) as RowNum,
count(*) over(partition by WeekEnding) as Cnt
FROM Data t
CROSS APPLY (
SELECT 4 AS Friday /*0=Mon,...,6=Sun*/
) AS which_day_of_week
CROSS APPLY (
SELECT CAST(DATEADD(DAY, -DATEDIFF(DAY, Friday, t.RegistrationDateTime) % 7 + 7, t.RegistrationDateTime) AS date) AS WeekEnding
) AS assign_alias_names
GROUP BY WeekEnding, TAT
)
selectWeekEnding,
avg(TAT) AS MedianLOS
from TAT
where RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)
group by WeekEnding
October 4, 2016 at 5:29 am
I don't get how the cross apply is working. My limited experience is using it like a join. I'm using 2008 Express but, I suppose I could download 12.
October 4, 2016 at 5:29 am
Yes, that's broadly what I had in mind. The reason I thought it more complicated is that my definition of median includes taking the average of the two middle terms when you have an even number of terms (instead of taking the larger as you do). To do that, you need to use windowing functions if you have 2012 or later, or a self-join if you don't.
John
Edit - I've just looked at your query again and noticed you're using exactly the same definition of median as mine. The only difference is you found a much more elegant way to calculate it than I did!
October 4, 2016 at 5:37 am
NineIron (10/4/2016)
I don't get how the cross apply is working. My limited experience is using it like a join. I'm using 2008 Express but, I suppose I could download 12.
The first CROSS APPLY is just including the extra value 4 in each row. 4 represents the fourth day after 01/01/1900, which just happened to be a Friday. The second CROSS APPLY takes the value of RegistrationDate for each row and uses it to calculate the WeekEnding by going back to day 4 (05/01/1900) and taking the remainder when the number of days in between is divided by 7. Hope that makes sense! CROSS APPLY is quite difficult to get used to (at least it was to me), so you might want to look up some pieces on it online and work through the examples.
John
October 4, 2016 at 5:40 am
Thanx. That helps.
October 4, 2016 at 10:20 am
Sorry, just now have time to follow up on this.
CROSS APPLY (CA) really is a type of join, I'm just co-opting it here to assign names to values. You can use CROSS JOIN instead for this case if you prefer, but CROSS APPLY is much more flexible, and valuable to know, so you might as well get used to using it.
As to the code itself:
First CA:
The base date in SQL, date 0, is 1900-01-01. That just happens to be a Monday. The first Friday after that date would then be 1900-01-05, or date 4. This CA assigns a name to the value 4, so it's not quite such a mystery or "magic number".
Note, btw, that the date is always 4, no matter what the value of DATEFIRST is. Finally, any known Friday would work, but it's very common in SQL to use date 0, so using the first Friday after that date makes sense.
Second CA:
Calcs how many days past Friday the date you're using is, then subtracts that number of days from the date. Voila, the date is forced back to the immediately previous Friday (if the date itself is a Friday, the date does not change). Since in this case you want the upcoming Friday, 7 days are added to move forward one week (to the next Friday).
This technique has many applications and, once you get used to it, it's actually quite logical. And it certainly beats looping or reading thru many table rows.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 5, 2016 at 4:42 am
Thank you very much.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply