i want output like this - 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 2008 Forums

Transact-SQL (2008)

i want output like this

Author

Topic

praveen050
Starting Member

9 Posts

Posted - 2012-05-28 : 06:11:48

this is my sample table
YEAR MONTH_NUMBER AC_USD
2007 12 2,223,757,572.3
2008 1 337,168,751.28
2008 2 320,847,865.22
2008 3 313,340,930.72
2008 4 317,816,048.3
2008 5 312,048,722.24
2008 6 322,824,314.9

Write a sql to get the data in the following form
YEAR MONTH_NUMBER CY Revenue PY Revenue Variance
2007 12 2,223,757,572.3 0 2,223,757,572.3
2008 1 337,168,751.28 0 337,168,751.3
2008 2 320,847,865.22 0 320,847,865.2
2008 3 313,340,930.72 0 313,340,930.7
2008 4 317,816,048.3 0 317,816,048.3
2008 5 312,048,722.24 0 312,048,722.2
2008 6 322,824,314.9 0 322,824,314.9
2008 7 326,693,981.879999 0 326,693,981.9
2008 8 301,424,277.62 0 301,424,277.6
2008 9 334,869,665.94 0 334,869,665.9
2008 10 329,890,993.64 0 329,890,993.6
2008 11 277,395,124.58 0 277,395,124.6
2008 12 322,481,081.72 2,223,757,572.3 -1,901,276,490.6
2009 1 350,519,005.02 337,168,751.28 13,350,253.7
2009 2 329,270,395.94 320,847,865.22 8,422,530.7
2009 3 341,696,990.3 313,340,930.72 28,356,059.6
2009 4 327,751,567.96 317,816,048.3 9,935,519.7
2009 5 304,955,010.54 312,048,722.24 -7,093,711.7
2009 6 340,120,290.3 322,824,314.9 17,295,975.4
2009 7 336,207,844.620001 ########## 9,513,862.7
2009 8 317,748,208.980001 301,424,277.62 16,323,931.4
2009 9 335,805,144.039999 334,869,665.94 935,478.1
2009 10 374,949,692.840001 329,890,993.64 45,058,699.2
2009 11 350,417,627.3 277,395,124.58 73,022,502.7
2009 12 386,443,067.06 322,481,081.72 63,961,985.3
Write SQL to get the data in following form(yearly and Quarterly Total)
Period Revenue
2007 2,223,757,572.3
2007-Q4 2,223,757,572.3
2008 3,816,801,758.04
2008-Q1 971,357,547.22
2008-Q2 952,689,085.44
2008-Q3 962,987,925.439999
2008-Q4 929,767,199.94

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 13:33:45

[code]
SELECT Year,Quarter,SUM(AC_USD) AS Total
FROM
(
SELECT Year,
CASE WHEN MONTH_NUMBER BETWEEN 1 AND 3 THEN 'Q1'
WHEN MONTH_NUMBER BETWEEN 4 AND 6 THEN 'Q2'
WHEN MONTH_NUMBER BETWEEN 6 AND 9 THEN 'Q3'
WHEN MONTH_NUMBER BETWEEN 10 AND 12 THEN 'Q4'
END AS Quarter,
AC_USD
FROM table
)t
GROUP BY Year,Quarter
WITH ROLLUP
[/code]

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

gayathris
Starting Member

11 Posts

Posted - 2012-06-04 : 06:44:14

Hello Visakh

Could you please tell me the sql query for the following case. I have a polygon which has id,name,latitude and longitude.
I used the query: select ASTEXT(polygon) AS polygon from table_name; This gives me all details but are congested. How can I seperate them?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 10:05:44

please dont hijack threads. Post your question as a new thread to improve visibility and increase chances of getting quick and accurate solution.

you need to also explain what you mean by conjested and tell us how want output to come instead

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

praveen050
Starting Member

9 Posts

Posted - 2012-06-05 : 04:58:11

but i want current year revenue and previous year revenue and difference
between them

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 10:27:55

[code]
SELECT Year,Quarter,SUM(AC_USD) AS Total
INTO #temp
FROM
(
SELECT Year,
CASE WHEN MONTH_NUMBER BETWEEN 1 AND 3 THEN 'Q1'
WHEN MONTH_NUMBER BETWEEN 4 AND 6 THEN 'Q2'
WHEN MONTH_NUMBER BETWEEN 6 AND 9 THEN 'Q3'
WHEN MONTH_NUMBER BETWEEN 10 AND 12 THEN 'Q4'
END AS Quarter,
AC_USD
FROM table
)t
GROUP BY Year,Quarter
WITH ROLLUP

SELECT t1.*,t2.Total AS PYTotal,
t1.Total-t2.Total AS Variance
FROM #Temp t1
CROSS APPLY (SELECT Total
FROM #temp
WHERE [Year]=t1.[Year]-1
AND [Quarter] = t1.[QUarter]
)t2
DROP TABLE #Temp
[/code]

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

praveen050
Starting Member

9 Posts

Posted - 2012-06-06 : 02:36:02

it should show for 2007 pyrev is 0,
thanks for the query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-07 : 22:57:17

[code]
SELECT Year,Quarter,SUM(AC_USD) AS Total
INTO #temp
FROM
(
SELECT Year,
CASE WHEN MONTH_NUMBER BETWEEN 1 AND 3 THEN 'Q1'
WHEN MONTH_NUMBER BETWEEN 4 AND 6 THEN 'Q2'
WHEN MONTH_NUMBER BETWEEN 6 AND 9 THEN 'Q3'
WHEN MONTH_NUMBER BETWEEN 10 AND 12 THEN 'Q4'
END AS Quarter,
AC_USD
FROM table
)t
GROUP BY Year,Quarter
WITH ROLLUP

SELECT t1.*,COALESCE(t2.Total,0) AS PYTotal,
t1.Total-COALESCE(t2.Total,0) AS Variance
FROM #Temp t1
OUTER APPLY (SELECT Total
FROM #temp
WHERE [Year]=t1.[Year]-1
AND [Quarter] = t1.[QUarter]
)t2
DROP TABLE #Temp
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources