Select Data From Previous Month

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

SQL Server 2008 Forums

Transact-SQL (2008)

Select Data From Previous Month

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2012-09-09 : 19:34:30

I'm trying to find the max order quantity for the previous based on the code below. I'm trying to do something at work which is very similar to the way the data is laid out in the adventure works database.

Basically, I would like another column that will show the max order quantity for the previous month. When you run the code below, it is for the months of August-October so the new column would first show the max order quantity for July in the August column and the max order quantity in the september column would be the max order quantity for August etc.. I guess you would have to use cross apply, but i'm not sure if the code is supposed to be written for this type of query too look back at the previous month.

use AdventureWorks
declare @start as datetime
set @start = '08/01/2001'
declare @end as datetime
set @end = '10/30/2001'

; with cte as (

SELECT
c.[Name] category, ps.Name subCat, p.Color, so.OrderQty,

month(so.modifieddate) monthNum
,case when month(so.modifieddate) = 1 then 'January'
when month(so.modifieddate) = 2 then 'February'
when month(so.modifieddate) = 3 then 'March'
when month(so.modifieddate) = 4 then 'April'
when month(so.modifieddate) = 5 then 'May'
when month(so.modifieddate) = 6 then 'June'

when month(so.modifieddate) = 7 then 'July'
when month(so.modifieddate) = 8 then 'August'
when month(so.modifieddate) = 9 then 'September'
when month(so.modifieddate) = 10 then 'October'
when month(so.modifieddate) = 11 then 'November'
when month(so.modifieddate) = 12 then 'December'

end Month

FROM [AdventureWorks].[Production].[ProductCategory] c
join [AdventureWorks].[Production].[ProductSubcategory] ps
on c.ProductCategoryID = ps.ProductCategoryID
join production.product p on
ps.ProductSubcategoryID = p.ProductSubcategoryID
join Sales.SalesOrderDetail so
on so.ProductID = p.ProductID
where p.Color is not null
and so.ModifiedDate between @start and @end

)

select cte.*,dd.OrderQty Max_OrderQ_ByMonth

--Cross Apply to find the Max Order Quantity by Month
from cte cross apply (select top 1 cte1.OrderQty from cte as cte1 where cte.Month = cte1.month order by cte1.OrderQty desc) as dd

--Need a new column that will find the Max Order Quantity for the previous month. Next to August, it should show the Max Order Quantity for July
order by cte.monthNum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 22:44:40

do you've a calendar table in your db?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

joe8079
Posting Yak Master

127 Posts

Posted - 2012-09-09 : 22:51:19

sorry, no calendar table.

joe8079
Posting Yak Master

127 Posts

Posted - 2012-09-09 : 23:09:53

is there any way to do this without the calendar table? It seems extremely complicated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 23:16:25

quote:
Originally posted by joe8079

is there any way to do this without the calendar table? It seems extremely complicated.

unless you've calendar table how would you get previous month date for each date?

you could create a function like below and use it if you want though!

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

joe8079
Posting Yak Master

127 Posts

Posted - 2012-09-10 : 18:11:36

Thanks for the link Visakh. I just added the function, but i'm still confused as to how it would work with my original issue.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 20:03:44

use the function to generate a calendar. then you can use the calendar to get previous year and get values corresponding to that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

joe8079
Posting Yak Master

127 Posts

Posted - 2012-09-10 : 20:13:34

cool thanks, I'll give it a shot. I've never really used a table value function like this before in SQL so this is kind of new. I guess I would use cross apply with the function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 20:22:01

yep...you can

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources