SQL Question, comparing the total in 1 table to total in another table in WHERE clause

These tables are joined by receive_id

I Only want to include receive_id's where the total 'receive_amt' in a_receive_detail is GREATER then the total amt(amt1 + amt2 + amt3) for the SAME receive_id in a_receive_invoice.
/* CREATE 3 TABLES */
CREATE TABLE [dbo].[a_receive_header](
	[receive_id] [int] NOT NULL,
	[date] [datetime] NULL,
 CONSTRAINT [PK_a_receiving] PRIMARY KEY CLUSTERED 
(
	[receive_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
 
CREATE TABLE [dbo].[a_receive_detail](
	[receive_detail_id] [int] NOT NULL,
	[receive_id] [nchar](10) NULL,
	[receive_amt] [money] NULL,
 CONSTRAINT [PK_a_receive_dtl] PRIMARY KEY CLUSTERED 
(
	[receive_detail_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
 
CREATE TABLE [dbo].[a_receive_invoice](
	[invoice_id] [int] NOT NULL,
	[receive_id] [int] NULL,
	[amt1] [money] NULL,
	[amt2] [money] NULL,
	[amt3] [money] NULL,
 CONSTRAINT [PK_a_receive_invoice] PRIMARY KEY CLUSTERED 
(
	[invoice_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
 
/* POPULATE THE 3 TABLES */
INSERT INTO a_receive_header
SELECT 50, '2005/10/10' UNION ALL 
SELECT 51, '2006/10/10' UNION ALL 
SELECT 52, '2007/10/10' 
 
 
INSERT INTO a_receive_detail
SELECT 1, 50,	50.00	UNION ALL 
SELECT 2, 50,	10	UNION ALL 
SELECT 3, 51,	100.00	UNION ALL 
SELECT 4, 51,	100.00	UNION ALL 
SELECT 5, 52,	11.00
 
 
INSERT INTO a_receive_invoice
SELECT 1, 50,	1,1,1		UNION ALL 
SELECT 2, 50,	1,1,1		UNION ALL 
SELECT 3, 50,	1,1,1		UNION ALL 
SELECT 4, 50,	1,1,1		UNION ALL 
SELECT 5, 51,	500,null,null	UNION ALL 
SELECT 6, 52,	5,null,null
 
/* DESIRED RESULT */
'date'	    'detail amt'  'invoice amt'
---------------------------------------
2005-10-10  60.00	    12.00
2007-10-10  11.00	    5.0
 
-Receive #51 is excluded because the receiving_detail total ($200.00) is NOT GREATER then the invoice total($500.00) from the same receive in a_receive_invoice.

Open in new window

byteboy11Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

i2mentalCommented:
SELECT  date, DetailAmt, InvoiceAmt
FROM
(SELECT arh.[receive_id],date,SUM(receive_amt) AS DetailAmt
FROM [a_receive_header] AS arh
JOIN [a_receive_detail] AS ard ON arh.[receive_id] = ard.[receive_id]
GROUP BY arh.[receive_id], date) AS a
JOIN
(SELECT receive_id, SUM(ISNULL(amt1,0)+ISNULL(amt2,0)+ISNULL(amt3,0)) AS InvoiceAmt
FROM [a_receive_invoice] AS ari
GROUP BY [receive_id]) AS b
ON a.[receive_id] = b.[receive_id]
WHERE [DetailAmt] > [InvoiceAmt]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
check this:
SELECT A.date,B.DetailAmt,C.InvoiceAmt
  FROM a_receive_header A
  JOIN (SELECT receive_id,SUM(receive_amt) AS DetailAmt
          FROM a_receive_detail
         GROUP BY receive_id) B
    ON A.receive_id = B.receive_id
  JOIN (SELECT receive_id,SUM(ISNULL(amt1,0)+ISNULL(amt2,0)+ISNULL(amt3,0)) AS InvoiceAmt
          FROM a_receive_invoice
         GROUP BY receive_id) C
    ON A.receive_id = C.receive_id
 WHERE B.DetailAmt > C.InvoiceAmt
0
byteboy11Author Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.