Quarter results - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Quarter results

Author

Topic

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-19 : 15:45:28

How do I get the revenue data summed up by quarter for the past 5 years? Can I use Pivot function? Does anyone know how to use it? Please show me an example.

Thanks,
-S

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-19 : 15:48:21

You'll need to show us some sample data and the expected result set as we aren't familiar with your database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-19 : 16:08:23

First, I need the data to be grouped by Country, Building, Transaction type, Revenue Total, Run Date

I have to sum the Revenue total by transaction type and Total quarter.

country Building Tran Type Revenue Run Date
01 C1 1 20000.00 Q1 - 2004
01 C1 4 223000.00 Q1 - 2004
01 C1 7 20000.00 Q1 - 2004
01 C1 8 223000.00 Q1 - 2004

01 C1 1 20000.00 Q2 - 2004
01 C1 4 223000.00 Q2 - 2004
01 C1 7 20000.00 Q2 - 2004

01 D2 2 20000.00 Q2 - 2004
01 D2 3 223000.00 Q2 - 2004
01 D2 4 20000.00 Q2 - 2004

This is the sample result.

Thanks,
-S

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-19 : 16:34:02

I would look into using the partition function for this one.

The code for that has been posted many times on the forum & is searchable

good luck !

r&r

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-20 : 08:59:19

Can anyone please provide the sample code? I think it must be used in dataware house.

Thanks,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 09:03:49

quote:
Originally posted by sqlpal2009

First, I need the data to be grouped by Country, Building, Transaction type, Revenue Total, Run Date

I have to sum the Revenue total by transaction type and Total quarter.

country Building Tran Type Revenue Run Date
01 C1 1 20000.00 Q1 - 2004
01 C1 4 223000.00 Q1 - 2004
01 C1 7 20000.00 Q1 - 2004
01 C1 8 223000.00 Q1 - 2004

01 C1 1 20000.00 Q2 - 2004
01 C1 4 223000.00 Q2 - 2004
01 C1 7 20000.00 Q2 - 2004

01 D2 2 20000.00 Q2 - 2004
01 D2 3 223000.00 Q2 - 2004
01 D2 4 20000.00 Q2 - 2004

This is the sample result.

Thanks,
-S

how are you financial year quarters defined?

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-20 : 09:22:14

First Quarter: Jan - Mar
Second Quarter: Apr - Jun
Third Quarter: Jul - Sept
Fourth Quater: Oct - Dec

And there is a rundate column which I have to use to show the result matrix in quarters for past 5 years.
Thanks,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 09:30:26

[code]
select country ,Building, [Tran Type]
SUM(Revenue) AS Total,
DATEPART(qq,[Run Date]),
YEAR([Run Date])
from table
where [Run Date]>=DATEADD(yy,DATEDIFF(yy,0,GETDATE())-5,0)
AND [Run Date]< DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
GROUP BY country, Building, [Tran Type],
DATEPART(qq,[Run Date]),
YEAR([Run Date])
[/code]

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-20 : 10:53:28

Thanks visakh16. I will try this code in my query and will let you know. I still have to work on other requirements of the query.

Thanks,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:29:22

yup...you're welcome
let me know how you got on

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-21 : 11:25:35

Is it possible to you Pivot function here which would give me the names of the quarters and will get expose to SQL 2005 function.

Thanks,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 11:30:29

you mean you need quarter values in seperate columns?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 11:33:59

then try this


SELECT country ,Building, [Tran Type],Year,[1] AS Q1,[2] AS Q2,[3] AS Q3,[4] AS Q4
FROM
(
select country ,Building, [Tran Type] 
SUM(Revenue) AS Total,
DATEPART(qq,[Run Date]) AS Quarter,
YEAR([Run Date]) AS Year
from table
where [Run Date]>=DATEADD(yy,DATEDIFF(yy,0,GETDATE())-5,0)
AND [Run Date]< DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
GROUP BY country, Building, [Tran Type],
DATEPART(qq,[Run Date]),
YEAR([Run Date])
)r
PIVOT (MAX(Total) FOR Quarter IN ([1],[2],[3],[4]))p

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-21 : 11:44:27

Yes. They wanted to see the results grouped by quaters and another one by each month and in the following matrix.

location Tran type Jan Feb Mar ... year
002 Moving 20000 2000 20000 2005
002 Aux. 20000 2000 20000 2005
003 Packaging 20000 2000 20000 2005

And

location Tran type Q1 Q2 Q3 Q4 year
002 Moving 20000 2000 20000 2005
002 Aux. 20000 2000 20000 2005
003 Packaging 20000 2000 20000 2005

Thanks,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 11:49:57

oh...so its for sql reporting. then its very easy to do this in reporting services. just use below query as source for dataset and inside matrix add quarter as a column group for first matrix and month for second and you will get output in above format


select country ,Building, [Tran Type] 
SUM(Revenue) AS Total,
DATENAME(mm,[Run Date]) as Month,
DATEPART(qq,[Run Date]),
YEAR([Run Date])
from table
where [Run Date]>=DATEADD(yy,DATEDIFF(yy,0,GETDATE())-5,0)
AND [Run Date]< DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
GROUP BY country, Building, [Tran Type],
DATENAME(mm,[Run Date]) as Month,
DATEPART(qq,[Run Date]),
YEAR([Run Date])

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-21 : 13:04:09

No it is not the SQL reporting. They wanted the results in the above matrix in two spreadsheets one by Quaters and another by months.

Thank you visakh16.

-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 13:09:29

for quarters use


SELECT country ,Building, [Tran Type],Year,[1] AS Q1,[2] AS Q2,[3] AS Q3,[4] AS Q4
FROM
(
select country ,Building, [Tran Type] 
SUM(Revenue) AS Total,
DATEPART(qq,[Run Date]) AS Quarter,
YEAR([Run Date]) AS Year
from table
where [Run Date]>=DATEADD(yy,DATEDIFF(yy,0,GETDATE())-5,0)
AND [Run Date]< DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
GROUP BY country, Building, [Tran Type],
DATEPART(qq,[Run Date]),
YEAR([Run Date])
)r
PIVOT (MAX(Total) FOR Quarter IN ([1],[2],[3],[4]))p

for months use


SELECT country ,Building, [Tran Type],Year,[January],
[February],
[March],
[April],
...,
[December]
FROM
(
select country ,Building, [Tran Type] 
SUM(Revenue) AS Total,
DATEPART(mm,[Run Date]) AS Month,
YEAR([Run Date]) AS Year
from table
where [Run Date]>=DATEADD(yy,DATEDIFF(yy,0,GETDATE())-5,0)
AND [Run Date]< DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
GROUP BY country, Building, [Tran Type],
DATEPART(mm,[Run Date]),
YEAR([Run Date])
)r
PIVOT (MAX(Total) FOR Month IN ([January],[February],[March],[April],...,[December]))p

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-22 : 15:37:50

Thank you for the sample code Visakh16.

I get the following error while running my SQL.

Msg 1033, Level 15, State 1, Line 26
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Is it becuase I am using Max function?

-S

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-01-22 : 16:35:38

I resolved it. It was related to the ORDER BY in subquery. I had to put it after PIVOT.

Thanks,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 23:29:07

yup...or put TOP 100 percent inside select query which contain ORDER BY

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources