SQL 2005 Quartetly sum query (diff Fiscal year is)

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 2005 Forums

Transact-SQL (2005)

SQL 2005 Quartetly sum query (diff Fiscal year is)

Author

Topic

maksuda
Starting Member

15 Posts

Posted - 2008-10-23 : 13:49:27

Hi:
I'm new in SQL Server(SQL 2005). I need to calculate sum of quarterly sales quantity for each category of Item(more than one item).
My company’s Fiscal year is Nov to Oct, so I need to calculate quarterly sum as per fiscal year(like Nov2007,Dec2007 and Jan2008 are in Q1-2008; Feb2008,Mar2008 and Apr2008 are in Q2-2008; May2008,Jun2008 and Jul2008 are in Q3-2008; Aug2008,Sep2008 and Oct2008 are in Q4-2008)
and my result should be something like:

Item Q1-2008 Q2-2008 Q3-2008 Q8-2008
A 5000 7000 3000 4000
B 6000 4000 5000 6500
C 4500 3000 6000 3800
D 7800 9400 3800 4500

I can calculate default quarterly total I mean for Jan-Dec Q1, Q2, Q3 and Q4, but this is tricky ......
I'm stack .Please help.........

Thanks in advance

Mak...

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 13:58:13

use PIVOT by your Quarters... show us your table and what you got so far so we can help u.

maksuda
Starting Member

15 Posts

Posted - 2008-10-23 : 14:35:13

quote:
Originally posted by hanbingl

use PIVOT by your Quarters... show us your table and what you got so far so we can help u.

Thanks for your reply.

My Table structure is simple:

SalesDate Item SalesQty

12/2/2007 A 20
12/2/2007 B 50
12/2/2007 C 100
12/3/2007 A 120
12/3/2007 C 100
12/4/2007 A 500
12/4/2007 B 250

How could I create pivot table?
please need help.........

tahnks...

Mak

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 16:07:08

[code]
select * from (
select
case when month(salesdate) in (11,12,1) then 'Q1' else
case when month(salesdate) in (2,3,4) then 'Q2' else
case when month(salesdate) in (5,6,7) then 'Q3' else
case when month(salesdate) in (8,9,10) then 'Q4' end end end end +'-'+
cast(
case when month(salesdate) in (11,12) then year(salesdate)+1 else year(salesdate) end as varchar(4)) as fiscyr, item, sum(salesqty) as ttl
from TAB1
group by
case when month(salesdate) in (11,12,1) then 'Q1' else
case when month(salesdate) in (2,3,4) then 'Q2' else
case when month(salesdate) in (5,6,7) then 'Q3' else
case when month(salesdate) in (8,9,10) then 'Q4' end end end end +'-'+
cast(
case when month(salesdate) in (11,12) then year(salesdate)+1 else year(salesdate) end as varchar(4)),
item)t
pivot
(
sum(ttl)
for fiscyr in ([Q1-2008],[Q2-2008],[Q3-2008],[Q4-2008])
)as p[/code]

maksuda
Starting Member

15 Posts

Posted - 2008-10-23 : 16:57:12

Thank you very much hanbingl.........
thanks hundrad thousand...........
it's so complicated query............almost impossible for me......
thanks a looooooooooot..........
you did a great help for me.............and save my project.......

Mak...

maksuda
Starting Member

15 Posts

Posted - 2008-10-24 : 09:37:56

hanbingl, I have a question about the pivot tables query.
It's working fine and giving me the correct data, but is it possible to get the all existing years' quarterly data?
I mean I have also 2007's data. So how could I make the pivot table more dynamic.......get all the existing year's quarterly data?

thanks ......

Mak...

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 10:53:27

There is a way, but you'll have to build up the SQL in a string then EXECUTE.

select 
case when month(salesdate) in (11,12,1) then 'Q1' else
case when month(salesdate) in (2,3,4) then 'Q2' else
case when month(salesdate) in (5,6,7) then 'Q3' else
case when month(salesdate) in (8,9,10) then 'Q4' end end end end +'-'+
cast(
case when month(salesdate) in (11,12) then year(salesdate)+1 else year(salesdate) end as varchar(4)) as fiscyr, item, sum(salesqty) as ttl
INTO #TEMP
from TAB1
group by 
case when month(salesdate) in (11,12,1) then 'Q1' else
case when month(salesdate) in (2,3,4) then 'Q2' else
case when month(salesdate) in (5,6,7) then 'Q3' else
case when month(salesdate) in (8,9,10) then 'Q4' end end end end +'-'+
cast(
 case when month(salesdate) in (11,12) then year(salesdate)+1 else year(salesdate) end as varchar(4)),
item

declare @pivot_col varchar(max)
set @pivot_col = ''

SELECT  @pivot_col = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t2.fiscyr
                        FROM    #TEMP AS t2
                        ORDER BY '],[' + t2.fiscyr
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'


declare @SQLSTR nvarchar(max)

set @SQLSTR = N'select * from #TEMP
pivot 
(
sum(ttl)
for fiscyr in ('+@pivot_col+')
)as p'

execute(@sqlstr)

maksuda
Starting Member

15 Posts

Posted - 2008-10-24 : 15:19:50

thanks a lot...........
it's working........

really complex query !!!!

ms
Starting Member

11 Posts

Posted - 2008-10-31 : 11:38:33

The above query helpmed me quite a bit as well.But our financial year is from april to mar. So the q4 will be jan,feb and mar of next year. So when I ran the above query with pivots [q1-2007]...[q4-2007] it does not add the sum the records for q4. It works when i change it to [q1-2007]..[q4-2008]..how do i get around it.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 11:46:19

then subtract 3 months from date and then group on it. so that jan.feb,mar will fall into q4 of previous fy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources