Calculation for median and high low.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculation for median and high low.

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-03-03 : 16:43:49

Hi All,

I have the following query:

select Type, Bill_Desc, product,
sum(Last_Week) as Last_Week,
sum(Two_Weeks_Ago)as Two_Week_Ago,
sum(Three_Week_Ago)as Three_Week_Ago,
sum(Four_Week_Ago) as Four_Week_Ago,
sum(Five_Week_Ago) as Five_Week_Ago,
sum(YTD) as YTD,
sum(Month3Median) as [3MonthMedian],
sum(Month6median) as [6MonthMedian],
sum(Month6High) as [6MonthHigh],
sum(Month6Low) as [6MonthLow],
sum(Active_Orders)as [Active_Orders],
sum(Cancel_Orders) as [Cancel_Orders_LastWeek]

FROM

( SELECT Type, Bill_Desc, product,
isnull(count(*),0) as Last_week,
0 as Two_weeks_ago,
0 as Three_Week_Ago,
0 as Four_Week_Ago,
0 as Five_Week_Ago,
0 as YTD,
0 as Month3Median,
0 as Month6median,
0 as Month6High,
0 as Month6Low,
0 as Active_Orders,
0 as Cancel_Orders
FROM datamart..trantype with (nolock)
WHERE order_Received_date >= @Start_Recd_Date and order_Received_date < @End_Recd_Date
GROUP BY Type, Bill_Desc, product
UNION

<query for the rest of the part>

The above query pulls the counts for the last five weeks. I need to get the 3monthmedian,
6monthmedian,6monthhigh and 6monthlow. I am not sure how I go about it.

Thanks,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 08:29:53

are you using sql 2005?