Rolling Previous Month Query - SQL Server Forums

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

Rolling Previous Month Query

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2011-09-19 : 20:25:17

Hi,

I have a query which provides me metrics for Battery Rate Previous Month and Warranty Battery Rate Previous Month. I need to also have Battery Rate for previous 3 months and Warranty Battery Rate previous 3 months as a separate column alongside battery rate previous month calculations. Please advice is there a way to achieve this as a separate column alongside using a sub query. Please let me know.

The columns that I have currently are

Completion Date

Battery Rate Previous Month

Warranty Battery Rate Previous Month

The columns that I need are

Completion Date

Battery Rate Previous Month

Warranty Battery Rate Previous Month

Battery Rate Previous 3 Month

Warranty Battery Rate Previous 3 Month


select   
       convert (varchar(10),[Completion_Date],101) as [Completion_Date],
       case when sum(Battery_Calls)!=0 then  convert(varchar(20),(sum(Battery_Sales)*100/sum(Battery_Calls)))+'%' else '0%' end as Battery_Rate_Previous_Month,
       case when  sum(Battery_Sales)!=0 then  convert(varchar(20),(sum(Warranty_Battery_Sales)*100/sum(Battery_Sales)))+'%' else '0%' end as Battery_warranty_Rate_Previous_Month

from            Fact_Student_Driver_Course_Details c
     inner join Dim_Student_Driver af on af.Student_Driver_Key=c.Student_Driver_Key
     inner join Dim_Student d on d.Fleet_Driver_Code= af.Student_Driver_Code
     inner join Fact_Student_Services_Metrics m on m.Student_Driver_Key=d.Student_Driver_Key
     inner join dim_date date on date.Date_Key = c.date_key

where           

               (m.date_key between convert(varchar(8),dateadd(mm,-1,[Completion_Date]),112)and convert (varchar(8),[Completion_Date],112)) 
group by 
convert (varchar(10),[Completion_Date],101)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 21:22:34

Hello Dp,

If you could provide some sample data and an expected output, I am sure someone could help you develop a solution here.

TIA.

dim
Yak Posting Veteran

57 Posts

Posted - 2011-09-19 : 21:38:37

Hi Ehorn,

Thank you for the prompt response. Currently I have a working query which outputs the following columns:
1. Completion Date
2. Battery Rate Previous Month
3. Warranty Battery Rate Previous Month
The query is as below :
select
convert (varchar(10),[Completion_Date],101) as [Completion_Date],
case when sum(Battery_Calls)!=0 then convert(varchar(20),(sum(Battery_Sales)*100/sum(Battery_Calls)))+'%' else '0%'
end as Battery_Rate_Previous_Month,
case when sum(Battery_Sales)!=0 then convert(varchar(20),(sum(Warranty_Battery_Sales)*100/sum(Battery_Sales)))+'%' else
'0%' end as Battery_warranty_Rate_Previous_Month

from Fact_Student_Driver_Course_Details c
inner join Dim_Student_Driver af on af.Student_Driver_Key=c.Student_Driver_Key
inner join Dim_Student d on d.Fleet_Driver_Code= af.Student_Driver_Code
inner join Fact_Student_Services_Metrics m on m.Student_Driver_Key=d.Student_Driver_Key
inner join dim_date date on date.Date_Key = c.date_key

where
(m.date_key between convert(varchar(8),dateadd(mm,-1,[Completion_Date]),112)and convert (varchar(8),[Completion_Date],112))
group by
convert (varchar(10),[Completion_Date],101)

I need to add additional logic to also include data columns for the previous 3 months.
The output columns then would be
1. Completion Date
2. Battery Rate Previous Month
3. Warranty Battery Rate Previous Month
4. Battery Rate Previous 3 Months
5. Warranty Battery Rate Previous 3 Months.

I guess we need to include a subquery to have this additional columns included ....Please advice how should we tweak the logic above...

Thank you,

Dp

dim
Yak Posting Veteran

57 Posts

Posted - 2011-09-19 : 21:42:02

The output would look like :

Completion Date Battery Rate Previous Month Battery Rate Previous 3 Month Warranty Battery Rate Previous Month
09/01/2011 20% 30% 10%

Dp

dim
Yak Posting Veteran

57 Posts

Posted - 2011-09-19 : 23:28:37

Here is the simplified version of the question :

The query below calculates the Battery Sales for Previous Month ( Aug 1st to Aug 31st). But along with the Previous Month Battery Sales I also need to show Battery Sales for Previous 3 months.

select
sum(Battery_Sales) as Battery_Sales_Previous_Month
from
Fact_Automotive_Services_Metrics fact
inner join dim_date date on date.Date_Key = fact.date_key
where
date.date between dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0)) and dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))

The current output:

Battery_Sales_Previous_Month

268

Desired output :

Battery_Sales_Previous_Month 268
Battery_Sales_Previous_3_Months 215

Please advice ...how can this be achieved ...subquery??

Thanks,

Dp

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 01:33:02

Your example is a bit unclear, but you may want to do something along these lines. I also thinned out your dateadd a bit. I believe it still achieves your date ranges:

[CODE]
select
sum(Battery_Sales) as Battery_Sales_Previous_Month,
sum(case when date.date>=DATEADD(mm, DATEDIFF(mm, 0, getdate()) -3 ,0) and date.date< DATEADD(mm, DATEDIFF(mm, 0, getdate()) -2 ,0) then Battery_Sales else 0 end) as '3MonthsAgo',
sum(case when date.date>=DATEADD(mm, DATEDIFF(mm, 0, getdate()) -2 ,0) and date.date< DATEADD(mm, DATEDIFF(mm, 0, getdate()) -1 ,0) then Battery_Sales else 0 end) as '2MonthsAgo',
sum(case when date.date>=DATEADD(mm, DATEDIFF(mm, 0, getdate()) -1 ,0) and date.date< DATEADD(mm, DATEDIFF(mm, 0, getdate()) -0 ,0) then Battery_Sales else 0 end) as 'LastMonth'

from
Fact_Automotive_Services_Metrics fact
inner join dim_date date on date.Date_Key = fact.date_key
where
date.date>=DATEADD(mm, DATEDIFF(mm, 0, getdate()) -3 ,0) and
date.date<DATEADD(mm, DATEDIFF(mm, 0, getdate()) -0 ,0)
[/CODE]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-20 : 01:50:01

flamblasters solution will give you what you need for a fixed point in time (getdate()) but it will not be "rolling". A rolling previous month query will i.e. give you a value for each month and then the value of the previous month for each month.

If this is what you want I think you will have to join the fact table again with the previous months data. Sort of like this (you will have to add more join criteria than in my example):


...
from Fact_Student_Driver_Course_Details c
  left outer join Fact_Student_Driver_Course_Details d
    on c.[Completion_Date] = dateadd(month, -1, d.[Completion_Date])

...and then use d.[Completion_Date] in the previous month columns like flamblaster showed you.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources