select query to present values based on month

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)

select query to present values based on month

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-07-22 : 12:42:48

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 12:58:34

[code]-- 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;[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 12:59:55

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 13:02:09

[code]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;[/code]

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

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-07-22 : 13:05:06

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 13:06:24

Check my edited queries.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 02:06:55

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources