Comparing Sums and Grouping - Need Advice

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 2005 Forums

Transact-SQL (2005)

Comparing Sums and Grouping - Need Advice

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2010-04-10 : 12:03:46

I've tried a couple of things on this, and hit dead ends, so I would be very grateful if someone could help me out. I have a table named tblTEST that contains the following data representation. What I want to do is change the status on a row to "1" if the total DEBITs and CREDITs do not equal for a combination of CUST/INVOICE. Furthermore, there must be both a CUST and INVOICE to even make the evaluation and possibly change the status to "1". If either or both of the CUST and INVOICE is blank or NULL, then the STATUS is left at "0". Notice the OWNER "JOHN". I only want to do this for rows with an owner of JOHN. If you imagine the same rows in the table with an owner of "BOB", then they would not be evaluated.

Note that I'm using an * to represent a blank (or null) because, even though I'm using a Courier New font, the columns don't line up with spaces in them.

CUST|INVOICE|DEBIT|CREDIT|STATUS|OWNER
--------------------------------------
****|*******|00044|000000|-----0|JOHN
AAAA|1111111|00000|000100|-----0|JOHN
AAAA|1111111|00050|000000|-----0|JOHN
AAAA|1111111|00075|000000|-----0|JOHN
AAAA|1111111|00000|000025|-----0|JOHN
AAAA|2222222|00030|000000|-----0|JOHN
BBBB|4444444|00200|000000|-----0|JOHN
BBBB|4444444|00000|000200|-----0|JOHN
BBBB|4444444|00000|000015|-----0|JOHN
FFFF|*******|00055|000000|-----0|JOHN
****|7777777|*****|000098|-----0|JOHN

===============================

After the SQL script is run, the following should be the result of the STATUS column:

CUST|INVOICE|DEBIT|CREDIT|STATUS|OWNER
--------------------------------------
****|*******|00044|000000|-----0|JOHN
AAAA|1111111|00000|000100|-----0|JOHN
AAAA|1111111|00050|000000|-----0|JOHN
AAAA|1111111|00075|000000|-----0|JOHN
AAAA|1111111|00000|000025|-----0|JOHN
AAAA|2222222|00030|000000|-----1|JOHN
BBBB|4444444|00200|000000|-----1|JOHN
BBBB|4444444|00000|000200|-----1|JOHN
BBBB|4444444|00000|000015|-----1|JOHN
FFFF|*******|00055|000000|-----0|JOHN
****|7777777|*****|000098|-----0|JOHN

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-10 : 14:12:06

This should give you what you want:


UPDATE tblTEST
SET STATUS = CASE WHEN TOTAL != 0 THEN 1 ELSE 0 END
FROM tblTEST t
INNER JOIN (
		SELECT INVOICE, SUM(CREDIT - DEBIT) AS TOTAL
		FROM tblTEST
		WHERE OWNER = 'JOHN'
		GROUP BY INVOICE ) u
	ON t.INVOICE = u.INVOICE
WHERE CUST IS NOT NULL

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

BobRoberts
Posting Yak Master

109 Posts

Posted - 2010-04-11 : 02:58:19

Thank you so much - that solution works great - but I have a problem in translating IS NOT NULL to "is not blank/blanks). That's right - if I put a NULL into the CUST column, it knows not to submit it to the comparison - but if I put just a simple one or two or many blanks (hitting the spacebar) into the CUST column, it acts as if there is something still in there - and the comparison fails. I guess what I'm asking is, how do you compare for blanks in SQL? I've gone on Google and tried to find this out - but no luck - can't find a thing. If you've got a good link - please post it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-11 : 03:08:33

[code]
UPDATE t
SET t.Status = 1
FROM
(SELECT Status,SUM(CREDIT - DEBIT) OVER (PARTITION BY CUST,INVOICE) AS Total
FROM YourTable
WHERE Owner = 'JOHN'
AND CUST > ''
AND INVOICE > ''
)t
WHERE Total<>0
[/code]

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

BobRoberts
Posting Yak Master

109 Posts

Posted - 2010-04-11 : 14:59:46

Yes, that works really well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 06:15:57

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources