| Author |
 Topic  |
|
|
praveen050
Starting Member
9 Posts |
 Posted - 05/28/2012 : 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
India
47023 Posts |
Posted - 05/28/2012 : 13:33:45
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
gayathris
Starting Member
11 Posts |
Posted - 06/04/2012 : 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
India
47023 Posts |
Posted - 06/04/2012 : 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 - 06/05/2012 : 04:58:11
|
but i want current year revenue and previous year revenue and difference
between them |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/05/2012 : 10:27:55
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
praveen050
Starting Member
9 Posts |
Posted - 06/06/2012 : 02:36:02
|
it should show for 2007 pyrev is 0,
thanks for the query |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/07/2012 : 22:57:17
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
i want output like this
