Function Sum and negative values

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)

Function Sum and negative values

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-05-08 : 09:46:44

Hi,
I wish to do an addition of the column Qty group by Office and Hour
When one of the two row per hour has a negative value in the Qty column, then I wish my sum to give -1 as a result no matter what the other Qty is.

Before:
ID|Office|Hour |Qty|Direction|
1 |ABC |08:00|14 |2 |
2 |ABC |08:00|12 |1 |
3 |ABC |09:00|10 |2 |
4 |ABC |09:00|-1 |1 |

After:
Office|Hour |Qty|
ABC |08:00|26 |
ABC |09:00|-1 |

Thanks for any help!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-08 : 11:26:50

There might be some better ways, but here is one way:

DECLARE @Foo TABLE (ID INT, Office VARCHAR(10), [Hour] TIME, Qty INT, Direction INT)
INSERT @Foo
VALUES
(1, 'ABC', '08:00', 14, 2), 
(2, 'ABC', '08:00', 12, 1), 
(3, 'ABC', '09:00', 10, 2), 
(4, 'ABC', '09:00', -1, 1) 

SELECT 
	A.Office, 
	A.[Hour],
	SUM(CASE WHEN B.[Hour] IS NOT NULL AND A.Qty > 0 THEN 0 ELSE A.Qty END) AS Qty
FROM	
	@Foo AS A
LEFT OUTER JOIN
	(
		-- Get Office-hour combos with negative Qty's
		SELECT Office, [Hour]
		FROM @Foo
		WHERE Qty < 0
		GROUP BY Office, [Hour]
	) AS B
	ON A.Office = B.Office
	AND A.[Hour] = B.[Hour]
GROUP BY
	A.Office,
	A.[Hour]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 11:27:58

[code]
SELECT Office,Hour,
CASE WHEN SUM(CASE WHEN Qty < 0 THEN 1 ELSE 0 END) > 0 THEN -1 ELSE SUM(Qty) END AS Qty
FROM table
GROUP BY Office,Hour
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2012-05-08 : 11:37:01

Firts of all ,
Thanks to Lamprey and visakh16.

I used the one proposed by visakh16 because it is a bit shorter.
But both work perfectly!

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 12:24:24

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources