• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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