using case to subtract to condition

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

General SQL Server Forums

New to SQL Server Programming

using case to subtract to condition

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2013-09-30 : 15:33:52

Hi,

I have data for moths which are commulative, for example Feb where month is 02, ecul Jan. + Feb. data. I would like to have the data for only Feb = when month '02' Feb = data for '02' - data for '01'. I would like help to writ these commands: For Jan there is no problem to write the command but for other months I am not sure how to, would you please help me to write the commands for months other than Jan.

,Jan_qty_1 = case
when [stat_month] = '01' then cast(sum(cast([qty_1_mo] as bigint)) as bigint)
else CAST('0' as int)
end

,Feb_qty_1 = case
when [stat_month] = '02' then cast(sum(cast([qty_1_mo] as bigint)) - sum(cast([qty_1_mo] as bigint) when [stat_month] = '01') as bigint)
else CAST('0' as int)
end

,Mar_qty_1 = case
when [stat_month] = '03' then cast(sum(cast([qty_1_mo] as bigint)) - sum(cast([qty_1_mo] as bigint) when [stat_month] = '02') as bigint)
else CAST('0' as int)
end

here is the data:

ust_code district stat_year stat_month qty_1_mo
2410 52 2007 01 12619
2410 52 2007 02 2200
2410 52 2007 02 4438
2410 52 2007 03 0
2410 52 2007 03 14434
2410 52 2007 04 2295
2410 52 2007 04 0
2410 52 2007 05 0
2410 52 2007 05 0
2410 52 2007 06 454

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-30 : 17:35:55

Here is one possible approach. If you are on SQL 2012, it could be simpler:

;WITH cte AS
(
	SELECT *, ROW_NUMBER() OVER
		(PARTITION BY ust_code, district ORDER BY stat_year, stat_month) AS RN
	FROM YourTable
)
SELECT
	a.ust_code,
	a.district,
	a.stat_year,
	a.stat_month,
	SUM(a.qty_1_mo) AS CumulativeQty,
	SUM(a.qty_1_mo) - SUM(COALESCE(b.qty_1_mo,0)) AS MonthlyQty
FROM
	cte a
	LEFT JOIN cte b ON
		a.ust_code = b.ust_code 
		AND a.district = b.district
		AND a.RN = b.RN+1
GROUP BY
	a.ust_code,
	a.district,
	a.stat_year,
	a.stat_month

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-01 : 04:23:01

[code]
sample data

DECLARE @Sample TABLE(ust_code int, district int, stat_year int, stat_month char(2), qty_1_mo int)
INSERT INTO @Sample
SELECT *
FROM (values
(2410, 52, 2007, '01', 12619),
(2410, 52, 2007, '02', 2200),
(2410, 52, 2007, '02', 4438),
(2410, 52, 2007, '03', 0),
(2410, 52, 2007, '03', 14434),
(2410, 52, 2007, '04', 2295),
(2410, 52, 2007, '04', 0),
(2410, 52, 2007, '05', 0),
(2410, 52, 2007, '05', 0),
(2410, 52, 2007, '06', 454)) AS Src (ust_code, district, stat_year, stat_month, qty_1_mo)

query
;WITH CTE AS(
SELECT
ust_code
, district
, stat_year
, stat_month
, qty_1_mo = SUM(qty_1_mo)
FROM @Sample
GROUP BY
ust_code
, district
, stat_year
, stat_month)
SELECT
today.*
, ThisMonth = COALESCE(yesterday.qty_1_mo, 0) - today.qty_1_mo
FROM CTE today
LEFT JOIN CTE yesterday
ON today.ust_code = yesterday.ust_code
AND today.district = yesterday.district
AND today.stat_year = yesterday.stat_year
AND today.stat_month = yesterday.stat_month + 1
[/code]

goligol
Posting Yak Master

128 Posts

Posted - 2013-10-02 : 14:52:01

Thank you so much. It was really helpful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources