October 14, 2022 at 5:52 pm
Hi,
I have a below query. Instead of average I need to calculate median for the below query. Could you please help on this.
;With Resultaverageduration As (
select a,
avg(CASE WHEN IS NULL or = '01/01/1970'
THEN 0 end) As [NS],
avg(CASE WHEN c IS not NULL and c != '01/01/1970'
THEN DateDiff(day,dateadd(d,1,[closing date]),c) else 0 end) As [SA] ,
avg(CASE WHEN [d] IS not NULL
Then DATEDIFF(day, c,d) else 0 end) as [RR],
avg(CASE WHEN [ e] IS not NULL and THEN
DATEDIFF(day,d,e) else 0 end) as [RS],
avg(CASE WHEN [f] IS not NULL and THEN
DATEDIFF(day,d,f) else 0 end) as [Rc]
from
group by a
)
Insert into [Table]
select a,Stage,[NO of Days] from (
SELECT a,
0 AS [NS],
cast(AVG (ISNULL([SA], 0)) as Int) AS [SA],
cast(AVG (ISNULL([RR], 0)) as Int) AS [RR] ,
cast(AVG (ISNULL([RS], 0)) as Int) AS [RS],
cast(AVG (ISNULL([RC], 0)) as Int) AS [RC]
FROM Resultaverageduration
group by a) Results1
unpivot
([No of Days]
for Stage in ([NS],
[SA],
[RR],
[RS],
[RC]))Ap2;
October 14, 2022 at 5:59 pm
Check out Aaron Bertrand's article: What is the fastest way to calculate the median?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy