Missing Records with Sum - 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 2000 Forums

Transact-SQL (2000)

Missing Records with Sum

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-10-17 : 09:49:17

I have a similar problem to one that I had recently where I need to display records where totals do not match from one table to another. One table holds the TaxTotal amount and one table holds all the Tax Detail records that should equal that TaxTotal. (Data samples below). I need to be able to display records that are either 1)missing the Tax Detail records completely or 2) the tax detail records do not sum to the TaxTotal record. I found some samples that used views to get the two sums and then one SQL statement to compare all the data, but I really need it to happen all in one statement if possible without any seperate views, etc.

CREATE TABLE [dbo].[TransDetail] (
[ID] [int] NOT NULL ,
[ItemID] [int] NOT NULL ,
[TaxTotal] [money] NOT NULL
) ON [PRIMARY]
GO

INSERT TransDetail Values (1, 111, 3)
INSERT TransDetail Values (2, 112, 5)

CREATE TABLE [dbo].[TransDetailTax] (
[ID] [int] NOT NULL ,
[TransDetailID] [int] NOT NULL ,
[TaxID] [int] NOT NULL ,
[TaxAmount] [money] NOT NULL
) ON [PRIMARY]
GO

INSERT TransDetailTax Values (1, 1, 4, 1.50)
INSERT TransDetailTax Values (1, 1, 5, 1.45)

This will create two records in TransDetail.

Sample 1: TransDetail ID 1 has a total of 3 for tax. But the corresponding TransDetailTax records only adds up to 1.95. I need to return the TransDetailID at least. I may need to return the amount that the two are off, but for right now, I'm happy if I could return the TransDetailID.

Sample 2: TransDetail ID 2 has a total tax of 5 for tax, but is completely missing the TransDetailTax records. I need to return the TransDetailID like above to show that it is not balanced. If I can also show that is it off by 5, all the better, but again, I'll be happy with what I can get.

Thank you for taking a look. I have several tables that I have to do this type of comparison too, so I am desperately hoping that if I can get help figuring this one out, then I can use the same logic for the other ones. Thanks so much,
JAdauto

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-17 : 09:56:28

You wont be able to this just by writting one statement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-17 : 09:58:22

[code]SELECT d.*,
off = d.TaxTotal - coalesce(t.TaxTotal, 0)
FROM [TransDetail] d
left JOIN
(
SELECT TransDetailID, TaxTotal = SUM(TaxAmount)
FROM [TransDetailTax]
GROUP BY TransDetailID
) t
ON d.ID = t.TransDetailID
WHERE d.TaxTotal <> t.TaxTotal
OR t.TaxTotal IS NULL[/code]
EDIT : in red

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 10:04:04

[code]-- Prepare sample data
DECLARE @TransDetail TABLE (ID INT, ItemID INT, TaxTotal MONEY)

INSERT @TransDetail
SELECT 1, 111, 3 UNION ALL
SELECT 2, 112, 5

DECLARE @TransDetailTax TABLE (ID INT, TransDetailID INT, TaxID INT, TaxAmount MONEY)

INSERT @TransDetailTax
SELECT 1, 1, 4, 1.50 UNION ALL
SELECT 1, 1, 5, 1.45

-- Is this the expected output?
SELECT td.Id,
td.ItemID,
td.TaxTotal,
COALESCE(tdt.TaxAmount, 0) AS TaxAmount,
COALESCE(tdt.TaxAmount, 0) - td.TaxTotal AS TaxDifference
FROM @TransDetail AS td
LEFT JOIN (
SELECT TransDetailID,
SUM(TaxAmount) AS TaxAmount
FROM @TransDetailTax
GROUP BY TransDetailID
) AS tdt ON tdt.TransDetailID = td.ID[/code]

E 12°55'05.25"
N 56°04'39.16"

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-10-17 : 12:59:32

quote:

Originally posted by khtan

SELECT	d.*,
	off = d.TaxTotal - coalesce(t.TaxTotal, 0)
FROM	[TransDetail] d
	left JOIN 
	(
		SELECT	TransDetailID, TaxTotal = SUM(TaxAmount)
		FROM	[TransDetailTax] 
		GROUP BY TransDetailID
	) t
	ON	d.ID	= t.TransDetailID
WHERE	d.TaxTotal	<> t.TaxTotal
OR	t.TaxTotal	IS NULL

EDIT : in red

KH
[spoiler]Time is always against us[/spoiler]

BEAUTIFUL!! I am going to do some tests on actual data from clients to see if it works as beautiful as it did for my sample data. I am also going to see if I can modify it to use for the other tables that I have to do similar comparison. I APPRECIATE the help!!
Thanks,
JAdauto

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-17 : 14:10:35

This may be helpful to you:

http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server

Read that and part II, it breaks down why you need the derived table in case it isn't entirely clear.

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-17 : 17:21:48

To build on Peso's sample data if you have a case where TansDetailTax might have a record but nothing in the TransDetail, you may want to use a FULL OUTER JOIN or a UNION. Since Jeff is fond of union's

here is a sample:

DECLARE	@TransDetail TABLE (ID INT, ItemID INT, TaxTotal MONEY)

INSERT	@TransDetail
SELECT	1, 111, 3 UNION ALL
SELECT	2, 112, 5

DECLARE	@TransDetailTax TABLE (ID INT, TransDetailID INT, TaxID INT, TaxAmount MONEY)

INSERT	@TransDetailTax
SELECT	1, 1, 4, 1.50 UNION ALL
SELECT	1, 1, 5, 1.45 UNION ALL
SELECT  2, 3, 8, 2.30


SELECT
	ID,
	MAX(ItemID),
	SUM(TaxTotal),
	SUM(TaxAmount),
	SUM(TaxTotal) - SUM(TaxAmount) AS TaxDiff
FROM
	(
		SELECT ID, ItemID, TaxTotal, 0 AS TaxAmount
		FROM @TransDetail
		
		UNION ALL

		SELECT TransDetailID AS ID, NULL, 0, SUM(TaxAmount)
		FROM @TransDetailTax
		GROUP BY TransDetailID
	) AS Temp
GROUP BY ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources