help needed in calculating totals through SQL

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)

help needed in calculating totals through SQL

Author

Topic

gauravkanand
Starting Member

5 Posts

Posted - 2012-08-08 : 05:54:22

I am new to SQL.. I need some help in writing a query

sample data in table

ID WARM HOT STATUS DATE
1 10 10 UP 10/07/2012
2 5 3 DOWN 12/07/2012
3 2 2 UP 15/07/2012
4 6 1 UP 14/08/2012
5 0 2 DOWN 10/08/2012
6 8 0 DOWN 10/09/2012
7 1 2 UP 10/10/2012
8 2 2 UP 12/10/2012

As my output i need
total no of WARM & HOT numbers in each month with status as UP & DOWN and their net difference too ie. Up is considered as positive (+1) & Down is considered as negative (-1)

JULY AUG SEPT OCT
WARM UP 12 6 0 3
WARM DOWN 5 0 8 0
HOT UP 12 1 0 4
HOT DOWN 3 2 0 0
TOTAL 16 5 -8 7

NET 16 21 13 20

TOTAL IS being caluclated as (WARM UP + HOT UP - WARM DOWN - HOT DOWN) for particular month
and NET is calculated as for JULY (UP - DOWN) gives (24-8) = 16
then we add this to total of AUG (16 + 5 ) = 21 and then the Aug net is added to total of sept to get the net of sept (21 -8) = 13
and similarly for oct (13+7) = 20.

Two seprate queries for calcuating Total and Net are also acceptable.

Thanks! in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-08 : 06:47:25

See technique displayed to you here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=177525

N 56°04'39.26"
E 12°55'05.63"

gauravkanand
Starting Member

5 Posts

Posted - 2012-08-08 : 07:04:35

quote:
Originally posted by SwePeso

See technique displayed to you here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=177525

N 56°04'39.26"
E 12°55'05.63"

Hi Peso,

There is a slight change in both the queries, I am trying to work out this query with the logic you provided earlier. However i am unable to get the right query. it's not working out.. So i need help on this. the last one is working well

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 11:32:08

[code]
declare @test table
(
ID int,
WARM int,
HOT int,
[STATUS] varchar(100),
[DATE] date

)

insert @test
VALUES(1, 10, 10, 'UP', '2012-07-10'),
(2, 5, 3, 'DOWN', '2012-07-12'),
(3, 2, 2, 'UP', '2012-07-15'),
(4, 6, 1, 'UP', '2012-08-14'),
(5, 0, 2, 'DOWN', '2012-08-10'),
(6, 8, 0, 'DOWN', '2012-09-10'),
(7, 1, 2, 'UP', '2012-10-10'),
(8, 2, 2, 'UP', '2012-10-12')

;With Temp_Values
AS
(
select [STATUS],MONTH([DATE]) AS MonthNo,
LEFT(DATENAME(mm,[DATE]),3) AS MonthName,
SUM(Val) AS TotalVal,CONDN
from @test
unpivot (Val FOR Condn IN ([WARM],[HOT]))u
GROUP BY CONDN,[STATUS],MONTH([DATE]),
DATENAME(mm,[DATE])
UNION ALL
SELECT 'TOTAL',MONTH([DATE]) AS MonthNo,
LEFT(DATENAME(mm,[DATE]),3) AS MonthName,
SUM(case when [status]='UP' then Val else -val end) AS TotalVal,
'TOTAL'
from @test
unpivot (Val FOR Condn IN ([WARM],[HOT]))u
GROUP BY MONTH([DATE]),
DATENAME(mm,[DATE])

)

SELECT CONDN,STATUS,
COALESCE(Jul,0) AS Jul,
COALESCE(Aug,0) AS Aug,
COALESCE(Sep,0) AS Sep,
COALESCE(Oct,0) AS Oct
FROM
(
SELECT t.CONDN,
CASE t.CONDN
WHEN 'HOT' THEN 2
WHEN 'WARM' THEN 1
ELSE 3
END AS CondnNo,
t.STATUS,
CASE t.STATUS
WHEN 'DOWN' THEN 2
WHEN 'UP' THEN 1
ELSE 3
END AS StatusNo,
t.MonthName,t.TotalVal
FROM Temp_Values t
UNION ALL
SELECT 'NET',999,'NET',999,MonthName,
SUM(TotalVal)+COALESCE(PrevTotal,0)
FROM (SELECT DISTINCT MonthNo,MonthName,TotalVal,Status,CONDN FROM Temp_Values) t
CROSS APPLY (SELECT SUM(TotalVal) AS PRevTotal
FROM Temp_Values
WHERE MonthNo <t.MonthNo
AND CONDN='TOTAL'
)t1
WHERE CONDN='Total'
GROUP BY Monthname,PrevTotal
)t
PIVOT (SUM(TotalVal) FOR MONTHName IN ([Jul],[Aug],[Sep],[Oct]))p
ORDER BY CondnNo,StatusNo

output
-----------------------------------------------------------------
CONDN STATUS Jul Aug Sep Oct
WARM UP 12 6 0 3
WARM DOWN 5 0 8 0
HOT UP 12 1 0 4
HOT DOWN 3 2 0 0
TOTAL TOTAL 16 5 -8 7
NET NET 16 21 13 20

[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources