SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 using case to subtract to condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

goligol
Posting Yak Master

128 Posts

Posted - 09/30/2013 :  15:33:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 09/30/2013 :  17:35:55  Show Profile  Reply with Quote
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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
947 Posts

Posted - 10/01/2013 :  04:23:01  Show Profile  Reply with Quote

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
Go to Top of Page

goligol
Posting Yak Master

128 Posts

Posted - 10/02/2013 :  14:52:01  Show Profile  Reply with Quote
Thank you so much. It was really helpful.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000