• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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

0
byteboy11
Asked:
byteboy11
1 Solution
 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now