Need Help in SQL Query - SQL Server Forums

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)

Need Help in SQL Query

Author

Topic

gauravkanand
Starting Member

5 Posts

Posted - 2012-08-06 : 07:35:46

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

sample data in table

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

As my output i need
total no of id's in each month with status as UP & DOWN and their net difference too ie. Up is +1 & Down is -1

JULY AUGUST SEPTEMBER OCT
UP 2 1 0 2
DOWN 1 1 1 0

TOTAL 1 0 -1 2

NET 1 1 0 2

TOTAL IS being caluclated as (UP - DOWN) for particular month
and NET is calculated as for JULY (UP - DOWN) gives (2-1) = 1
then we add this to total of AUG (1 + 0 ) = 1 and then the Aug net is added to total of sept to get the net of sept (1 -1) = 0
and similarly for oct (0+2) = 2.

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-06 : 08:39:58

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
ID INT NOT NULL,
[STATUS] VARCHAR(20) NOT NULL,
[DATE] DATE NOT NULL
);

-- Populate sample data
INSERT @Sample
(
ID,
[STATUS],
[DATE]
)
VALUES (1, 'UP', '20120710'),
(2, 'DOWN', '20120712'),
(3, 'UP', '20120715'),
(4, 'UP', '20120814'),
(5, 'DOWN', '20120810'),
(6, 'DOWN', '20120910'),
(7, 'UP', '20121010'),
(8, 'UP', '20121012');

-- Solution by SwePeso
CREATE TABLE #Stage
(
[Status] VARCHAR(20) NOT NULL,
Value INT NOT NULL,
[Month] TINYINT NOT NULL
);

INSERT #Stage
(
[Status],
Value,
[Month]
)
SELECT [STATUS],
SUM(CASE [STATUS]
WHEN 'UP' THEN 1
ELSE -1
END) AS Value,
DATEPART(MONTH, [DATE]) AS [Month]
FROM @Sample
WHERE [STATUS] IN ('UP', 'DOWN')
GROUP BY [STATUS],
DATEPART(MONTH, [DATE])

CREATE NONCLUSTERED INDEX IX_Month ON #Stage ([Month])

;WITH cteSource(theOrder, [Status], January, February, March, April, May, June, July, August, September, October, November, December)
AS (
SELECT CASE [Status]
WHEN 'UP' THEN 1
ELSE 2
END AS theOrder,
[Status],
SUM(CASE WHEN [Month] = 1 THEN ABS(Value) ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 THEN ABS(Value) ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 THEN ABS(Value) ELSE 0 END) AS March,
SUM(CASE WHEN [Month] = 4 THEN ABS(Value) ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 THEN ABS(Value) ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 THEN ABS(Value) ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 THEN ABS(Value) ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 THEN ABS(Value) ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 THEN ABS(Value) ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 THEN ABS(Value) ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 THEN ABS(Value) ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 THEN ABS(Value) ELSE 0 END) AS December
FROM #Stage
GROUP BY [Status]

UNION ALL

SELECT 3 AS theOrder,
'Total' AS [Status],
SUM(CASE WHEN [Month] = 1 THEN Value ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 THEN Value ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 THEN Value ELSE 0 END) AS March,
SUM(CASE WHEN [Month] = 4 THEN Value ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 THEN Value ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 THEN Value ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 THEN Value ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 THEN Value ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 THEN Value ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 THEN Value ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 THEN Value ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 THEN Value ELSE 0 END) AS December
FROM #Stage

UNION ALL

SELECT 4 AS theOrder,
'Net' AS [Status],
SUM(CASE WHEN [Month] = 1 THEN Value ELSE 0 END) AS January,
SUM(CASE WHEN [Month] = 2 THEN Value ELSE 0 END) AS February,
SUM(CASE WHEN [Month] = 3 THEN Value ELSE 0 END) AS March,
SUM(CASE WHEN [Month] = 4 THEN Value ELSE 0 END) AS April,
SUM(CASE WHEN [Month] = 5 THEN Value ELSE 0 END) AS May,
SUM(CASE WHEN [Month] = 6 THEN Value ELSE 0 END) AS June,
SUM(CASE WHEN [Month] = 7 THEN Value ELSE 0 END) AS July,
SUM(CASE WHEN [Month] = 8 THEN Value ELSE 0 END) AS August,
SUM(CASE WHEN [Month] = 9 THEN Value ELSE 0 END) AS September,
SUM(CASE WHEN [Month] = 10 THEN Value ELSE 0 END) AS October,
SUM(CASE WHEN [Month] = 11 THEN Value ELSE 0 END) AS November,
SUM(CASE WHEN [Month] = 12 THEN Value ELSE 0 END) AS December
FROM (
SELECT s.[Month],
f.Value
FROM (
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
) AS s([Month])
CROSS APPLY (
SELECT SUM(x.Value)
FROM #Stage AS x
WHERE x.[Month] <= s.[Month]
) AS f(Value)
) AS d
)
SELECT [Status],
January,
February,
March,
April,
May,
June,
July,
August,
September,
October,
November,
December
FROM cteSource
ORDER BY theOrder

DROP TABLE #Stage;[/code]

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

gauravkanand
Starting Member

5 Posts

Posted - 2012-08-06 : 09:12:26

Thanks! for the quick reply..
though it will take some time for me to understand and implement this.
I hope this will work.. I case of any issues i'll surely bug you

gauravkanand
Starting Member

5 Posts

Posted - 2012-08-08 : 05:59:03

quote:

Originally posted by SwePeso

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		ID INT NOT NULL,
		[STATUS] VARCHAR(20) NOT NULL,
		[DATE] DATE NOT NULL
	);

-- Populate sample data
INSERT	@Sample
	(
		ID,
		[STATUS],
		[DATE]
	)
VALUES	(1, 'UP', '20120710'),
	(2, 'DOWN', '20120712'),
	(3, 'UP', '20120715'),
	(4, 'UP', '20120814'),
	(5, 'DOWN', '20120810'),
	(6, 'DOWN', '20120910'),
	(7, 'UP', '20121010'),
	(8, 'UP', '20121012');

-- Solution by SwePeso
CREATE TABLE	#Stage
		(
			[Status] VARCHAR(20) NOT NULL,
			Value INT NOT NULL,
			[Month] TINYINT NOT NULL
		);

INSERT		#Stage
		(
			[Status],
			Value,
			[Month]
		)
SELECT		[STATUS],
		SUM(CASE [STATUS]
			WHEN 'UP' THEN 1
			ELSE -1
		END) AS Value,
		DATEPART(MONTH, [DATE]) AS [Month]
FROM		@Sample
WHERE		[STATUS] IN ('UP', 'DOWN')
GROUP BY	[STATUS],
		DATEPART(MONTH, [DATE])

CREATE NONCLUSTERED INDEX IX_Month ON #Stage ([Month])

;WITH cteSource(theOrder, [Status], January, February, March, April, May, June, July, August, September, October, November, December)
AS (
	SELECT		CASE [Status]
				WHEN 'UP' THEN 1
				ELSE 2
			END AS theOrder,
			[Status],
			SUM(CASE WHEN [Month] = 1 THEN ABS(Value) ELSE 0 END) AS January,
			SUM(CASE WHEN [Month] = 2 THEN ABS(Value) ELSE 0 END) AS February,
			SUM(CASE WHEN [Month] = 3 THEN ABS(Value) ELSE 0 END) AS March,
			SUM(CASE WHEN [Month] = 4 THEN ABS(Value) ELSE 0 END) AS April,
			SUM(CASE WHEN [Month] = 5 THEN ABS(Value) ELSE 0 END) AS May,
			SUM(CASE WHEN [Month] = 6 THEN ABS(Value) ELSE 0 END) AS June,
			SUM(CASE WHEN [Month] = 7 THEN ABS(Value) ELSE 0 END) AS July,
			SUM(CASE WHEN [Month] = 8 THEN ABS(Value) ELSE 0 END) AS August,
			SUM(CASE WHEN [Month] = 9 THEN ABS(Value) ELSE 0 END) AS September,
			SUM(CASE WHEN [Month] = 10 THEN ABS(Value) ELSE 0 END) AS October,
			SUM(CASE WHEN [Month] = 11 THEN ABS(Value) ELSE 0 END) AS November,
			SUM(CASE WHEN [Month] = 12 THEN ABS(Value) ELSE 0 END) AS December
	FROM		#Stage
	GROUP BY	[Status]

	UNION ALL

	SELECT	3 AS theOrder,
		'Total' AS [Status],
		SUM(CASE WHEN [Month] = 1 THEN Value ELSE 0 END) AS January,
		SUM(CASE WHEN [Month] = 2 THEN Value ELSE 0 END) AS February,
		SUM(CASE WHEN [Month] = 3 THEN Value ELSE 0 END) AS March,
		SUM(CASE WHEN [Month] = 4 THEN Value ELSE 0 END) AS April,
		SUM(CASE WHEN [Month] = 5 THEN Value ELSE 0 END) AS May,
		SUM(CASE WHEN [Month] = 6 THEN Value ELSE 0 END) AS June,
		SUM(CASE WHEN [Month] = 7 THEN Value ELSE 0 END) AS July,
		SUM(CASE WHEN [Month] = 8 THEN Value ELSE 0 END) AS August,
		SUM(CASE WHEN [Month] = 9 THEN Value ELSE 0 END) AS September,
		SUM(CASE WHEN [Month] = 10 THEN Value ELSE 0 END) AS October,
		SUM(CASE WHEN [Month] = 11 THEN Value ELSE 0 END) AS November,
		SUM(CASE WHEN [Month] = 12 THEN Value ELSE 0 END) AS December
	FROM	#Stage

	UNION ALL

	SELECT	4 AS theOrder,
		'Net' AS [Status],
		SUM(CASE WHEN [Month] = 1 THEN Value ELSE 0 END) AS January,
		SUM(CASE WHEN [Month] = 2 THEN Value ELSE 0 END) AS February,
		SUM(CASE WHEN [Month] = 3 THEN Value ELSE 0 END) AS March,
		SUM(CASE WHEN [Month] = 4 THEN Value ELSE 0 END) AS April,
		SUM(CASE WHEN [Month] = 5 THEN Value ELSE 0 END) AS May,
		SUM(CASE WHEN [Month] = 6 THEN Value ELSE 0 END) AS June,
		SUM(CASE WHEN [Month] = 7 THEN Value ELSE 0 END) AS July,
		SUM(CASE WHEN [Month] = 8 THEN Value ELSE 0 END) AS August,
		SUM(CASE WHEN [Month] = 9 THEN Value ELSE 0 END) AS September,
		SUM(CASE WHEN [Month] = 10 THEN Value ELSE 0 END) AS October,
		SUM(CASE WHEN [Month] = 11 THEN Value ELSE 0 END) AS November,
		SUM(CASE WHEN [Month] = 12 THEN Value ELSE 0 END) AS December
	FROM	(
			SELECT		s.[Month],
					f.Value
			FROM		(
						VALUES	(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
					) AS s([Month])
			CROSS APPLY	(
						SELECT	SUM(x.Value)
						FROM	#Stage AS x
						WHERE	x.[Month] <= s.[Month]
					) AS f(Value)
		) AS d
)
SELECT		[Status],
		January,
		February,
		March,
		April,
		May,
		June,
		July, 
		August,
		September,
		October,
		November,
		December
FROM		cteSource
ORDER BY	theOrder

DROP TABLE	#Stage;

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

HI swepeso
Thanks! for the response, this worked well
however i have slightly different requirement this time
can you please check and help me on
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=177606
Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources