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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select query to present values based on month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

536 Posts

Posted - 07/22/2013 :  12:42:48  Show Profile  Reply with Quote
Is it possible, i want to present result in 3 columns only.
by Dept, Period, total
period will show the month number based on value available in which period and the third column Total is the sum(per x) based on dept and period.

when you run below query should present feb value as 3500 for dept 310. since per2 has two rows 2100 and 1400.

Declare @Sample table (Dept int, per1 int, per2 int, per3 int,per4 int,per5 int,per6 int,per7 int,
per8 int, per9 int, per10 int,per11 int,per12 int)
insert @Sample
select 310, 0,2100,0,0,0,0,0,0,0,0,0,0 union all -- feb month period value available
select 310, 234,0,0,0,0,0,0,0,0,0,0,0 union all --jan month value available
select 310, 0,0,765,0,0,0,0,0,0,0,0,0 union all
select 310, 0,0,0,5678,0,0,0,0,0,0,0,0 union all
select 310, 0,0,0,0,875,0,0,0,0,0,0,0 union all
select 310, 0,0,0,0,0,9800,0,0,0,0,0,0 union all
select 310, 0,0,0,0,0,0,9000,0,0,0,0,0 union all
select 310, 0,0,0,0,0,0,0,5600,0,0,0,0 union all
select 310, 0,0,0,0,0,0,0,0,770,0,0,0 union all
select 310, 0,0,0,0,0,0,0,0,0,8900,0,0 union all
select 310, 0,0,0,0,0,0,0,0,0,0,1400,0 union all
select 310, 0,1400,0,0,0,0,0,0,0,0,0,0 union all
select 310, 0,0,0,0,0,0,0,0,770,0,0,1950 

select * from @Sample



Thank you very much for the helpful info.

Edited by - cplusplus on 07/22/2013 12:44:06

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 07/22/2013 :  12:58:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- SwePeso
WITH cteSource(Dept, theMonth, theValue)
AS (
	SELECT	u.Dept,
		--DATEADD(MONTH, CAST(SUBSTRING(u.theCol, 4, 2) AS TINYINT), '18991201') AS theMonth,
		CAST(SUBSTRING(u.theCol, 4, 2) AS TINYINT) AS theMonth,
		u.theValue
	FROM	@Sample AS s
	UNPIVOT	(
			theValue
			FOR theCol IN (s.per1, s.per2, s.per3, s.per4, s.per5, s.per6, s.per7, s.per8, s.per9, s.per10, s.per11, s.per12)
		) AS u
)
SELECT		Dept,
		--CONVERT(CHAR(3), theMonth, 0) AS Period,
		theMonth AS Period,
		SUM(theValue) AS Total
FROM		cteSource
GROUP BY	Dept,
		theMonth
ORDER BY	Dept,
		theMonth;


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Edited by - SwePeso on 07/22/2013 13:06:12
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/22/2013 :  12:59:55  Show Profile  Reply with Quote
do mean this?


Declare @Sample table (Dept int, per1 int, per2 int, per3 int,per4 int,per5 int,per6 int,per7 int,
per8 int, per9 int, per10 int,per11 int,per12 int)
insert @Sample
select 310, 0,2100,0,0,0,0,0,0,0,0,0,0 union all -- feb month period value available
select 310, 234,0,0,0,0,0,0,0,0,0,0,0 union all --jan month value available
select 310, 0,0,765,0,0,0,0,0,0,0,0,0 union all
select 310, 0,0,0,5678,0,0,0,0,0,0,0,0 union all
select 310, 0,0,0,0,875,0,0,0,0,0,0,0 union all
select 310, 0,0,0,0,0,9800,0,0,0,0,0,0 union all
select 310, 0,0,0,0,0,0,9000,0,0,0,0,0 union all
select 310, 0,0,0,0,0,0,0,5600,0,0,0,0 union all
select 310, 0,0,0,0,0,0,0,0,770,0,0,0 union all
select 310, 0,0,0,0,0,0,0,0,0,8900,0,0 union all
select 310, 0,0,0,0,0,0,0,0,0,0,1400,0 union all
select 310, 0,1400,0,0,0,0,0,0,0,0,0,0 union all
select 310, 0,0,0,0,0,0,0,0,770,0,0,1950 

select Dept,MONTH(DATEADD(mm,REPLACE(Period,'per','')-1,0)) AS [Month],SUM(Val) AS Total
from @Sample
unpivot (val for period in (per1,per2,per3,per4,per5,per6,per7,per8,per9,per10,per11,per12))u
WHERE val >0
GROUP BY Dept,MONTH(DATEADD(mm,REPLACE(Period,'per','')-1,0))
ORDER BY Dept,[Month]


output
--------------------------------------------
Dept	Month	Total
310	1	234
310	2	3500
310	3	765
310	4	5678
310	5	875
310	6	9800
310	7	9000
310	8	5600
310	9	1540
310	10	8900
310	11	1400
310	12	1950



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 07/22/2013 :  13:02:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT		s.Dept,
		f.Period,
		SUM(f.theValue) AS Total
FROM		@Sample AS s
CROSS APPLY	(
			VALUES	(1, 'Jan', s.per1),
				(2, 'Feb', s.per2),
				(3, 'Mar', s.per3),
				(4, 'Apr', s.per4),
				(5, 'May', s.per5),
				(6, 'Jun', s.per6),
				(7, 'Jul', s.per7),
				(8, 'Aug', s.per8),
				(9, 'Sep', s.per9),
				(10, 'Oct', s.per10),
				(11, 'Nov', s.per11),
				(12, 'Dec', s.per12)
		) AS f(theSort, Period, theValue)
GROUP BY	s.Dept,
		f.theSort,
		f.Period
ORDER BY	s.Dept,
		f.theSort;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Edited by - SwePeso on 07/22/2013 13:05:35
Go to Top of Page

cplusplus
Aged Yak Warrior

536 Posts

Posted - 07/22/2013 :  13:05:06  Show Profile  Reply with Quote
Peso,
Thanks a lot for the query, it is showing result wrong. Instead of feb it is showing 3500 for march.

Declare @Sample table (Dept int, per1 int, per2 int, per3 int,per4 int,per5 int,per6 int,per7 int,
per8 int, per9 int, per10 int,per11 int,per12 int)
insert @Sample
select 310, 0,2100,0,0,0,0,0,0,0,0,0,0 union all -- feb month period value available
select 310, 234,0,0,0,0,0,0,0,0,0,0,0 union all --jan month value available
select 310, 0,0,765,0,0,0,0,0,0,0,0,0 union all
select 310, 0,0,0,5678,0,0,0,0,0,0,0,0 union all
select 310, 0,0,0,0,875,0,0,0,0,0,0,0 union all
select 310, 0,0,0,0,0,9800,0,0,0,0,0,0 union all
select 310, 0,0,0,0,0,0,9000,0,0,0,0,0 union all
select 310, 0,0,0,0,0,0,0,5600,0,0,0,0 union all
select 310, 0,0,0,0,0,0,0,0,770,0,0,0 union all
select 310, 0,0,0,0,0,0,0,0,0,8900,0,0 union all
select 310, 0,0,0,0,0,0,0,0,0,0,1400,0 union all
select 310, 0,1400,0,0,0,0,0,0,0,0,0,0 union all
select 310, 0,0,0,0,0,0,0,0,770,0,0,1950;


WITH cteSource(Dept, theMonth, theValue)
AS (
	SELECT	u.Dept,
		DATEADD(MONTH, CAST(SUBSTRING(u.theCol, 4, 2) AS TINYINT), '19000101') AS theMonth,
		u.theValue
	FROM	@Sample AS s
	UNPIVOT	(
			theValue
			FOR theCol IN (s.per1, s.per2, s.per3, s.per4, s.per5, s.per6, s.per7, s.per8, s.per9, s.per10, s.per11, s.per12)
		) AS u
)
SELECT		Dept,
		CONVERT(CHAR(3), theMonth, 0) AS Period,
		SUM(theValue) AS Total
FROM		cteSource
GROUP BY	Dept,
		theMonth
ORDER BY	Dept,
		theMonth;



THANK YOU VERY MUCH.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 07/22/2013 :  13:06:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Check my edited queries.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/23/2013 :  02:06:55  Show Profile  Reply with Quote
Also see what all you can do with VLUES table constructor in SQL 2008

http://visakhm.blogspot.com/2012/05/multifacet-values-clause.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.06 seconds. Powered By: Snitz Forums 2000