June 12, 2012 at 1:14 pm
Hello,
I need to change a calculation of a metric I have to do a three month average in a stored procedure. However, the other metrics in this SP are monthly so my DECLARE/SET looks like this:
DECLARE @beg_dt DATETIME
SET @beg_dt = '2012-05-01'
DECLARE @end_dt DATETIME
SET @end_dt = '2012-05-31'
Here's my current calucation:
ISNULL(CAST (SUM(PRW.top2num)
/ NULLIF(CAST (SUM(PRW.top2den) AS DECIMAL(9,
4)), 0) AS DECIMAL(9,
4)), 0) AS Top2
I am very much a beginner to SQL. Can this somehow easily be changed to an average? Also, in my where statement can I change this:
WHERE sd.acty_dt >= @beg_dt
AND sd.acty_dt <= @end_dt
to somehow pull in the three months?
Thank you in advance!
June 12, 2012 at 1:48 pm
While the where clause can easily be adapted to include three months the real question is in the group by statement. Also do you want an average over through months or do you want a monthly average of the last three month totals?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 12, 2012 at 1:59 pm
vicki.k.noble (6/12/2012)
Hello,I need to change a calculation of a metric I have to do a three month average in a stored procedure. However, the other metrics in this SP are monthly so my DECLARE/SET looks like this:
DECLARE @beg_dt DATETIME
SET @beg_dt = '2012-05-01'
DECLARE @end_dt DATETIME
SET @end_dt = '2012-05-31'
Here's my current calucation:
ISNULL(CAST (SUM(PRW.top2num)
/ NULLIF(CAST (SUM(PRW.top2den) AS DECIMAL(9,
4)), 0) AS DECIMAL(9,
4)), 0) AS Top2
I am very much a beginner to SQL. Can this somehow easily be changed to an average? Also, in my where statement can I change this:
WHERE sd.acty_dt >= @beg_dt
AND sd.acty_dt <= @end_dt
to somehow pull in the three months?
Thank you in advance!
Using the following:
declare @CurrentMonth date = getdate(); -- 2012-06-12
Your WHERE clause would look like this:
WHERE
sd.acty_dt >= dateadd(mm, datediff(mm, 0, @CurrentMonth) - 3, 0) and -- 2012-03-01
sd.acty_dt < dateadd(mm, datediff(mm, 0, @CurrentMonth), 0); -- 2012-06-01
June 12, 2012 at 2:01 pm
Hi Dan,
The average would be of the last three month totals. For example, in July, I would need the average of May, June and July.
June 12, 2012 at 2:04 pm
vicki.k.noble (6/12/2012)
Hi Dan,The average would be of the last three month totals. For example, in July, I would need the average of May, June and July.
Based on the above, the following:
Using the following:
declare @CurrentMonth date = getdate(); -- 2012-06-12
Your WHERE clause would look like this:
WHERE
sd.acty_dt >= dateadd(mm, datediff(mm, 0, @CurrentMonth) - 2, 0) and -- 2012-04-01
sd.acty_dt < dateadd(mm, datediff(mm, 0, @CurrentMonth) + 1, 0); -- 2012-07-01
June 12, 2012 at 2:36 pm
Given that you said you were new take the following example
create table #Example_data (Tran_dt datetime,Sales_amount decimal(6,2))
insert into #Example_data
select '03/20/12',100.00
union
select '03/21/12',200.00
union
select '03/22/12',200.00
union
select '04/11/12',300.00
union
select '04/2/12',250.00
union
select '04/3/12',100.00
union
select '04/20/12',400.00
union
select '05/12/12',200.00
union
select '05/13/12',600.00
union
select '05/15/12',100.00
union
select '05/16/12',150.00
union
select '06/1/12',200.00
union
select '06/4/12',350.00
select
datepart(mm,Tran_dt) as 'Month',sum(Sales_amount) as 'Sales_amount'
from #Example_data
group by datepart(mm,Tran_dt)
select avg(t.Sales_amount)
from (select
datepart(mm,Tran_dt) as 'Month',sum(Sales_amount) as 'Sales_amount'
from #Example_data
group by datepart(mm,Tran_dt))t
drop table #Example_data
The first Query will Sum the sales amounts by month This will get you part of the way there but the second query will take the results of the first and average those. If you apply this type of scenerio along with the suggested Where statement changes you should get where you need. feel free to use the example to play around and see how the different parts would function before jumping into you SP.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply