SQL Join Query for Not Matching Amount from two Tables for the same Invoice #

I am trying to write a Join Query to pull the Invoice Amount for the Invoice Number which is not matching in two Tables.

Table 1 : Billing_Line - > Has line by line purchase detail for one Invoice Number. So, We Sum ( Invoice Amount ) for that Invoice Number.

Table 2 : Billing_HDR -> Has one line of record with Totalled Invoice Amount for the Invoice Number.

Both these Tables are feeded from different data source. So we found there is some mismatch in the Invoice Amount in Billing_HDR against the Billing_Line for some Invoice Numbers.

Below is the two set of queries .. Kindly help me to rewrite the logic.

with cte as
(
select 
inv_num,
SUM(line_amt) LINE_FEES
from dbo.BILLING_line
where REQ_ID = 3895
group by INV_NUM
)
select cte.inv_num,vw.inv_num from cte
inner join vw_billinginv vw
on   cte.INV_NUM = vw.inv_num
where cte.LINE_FEES <>  vw.INV_AMT

Open in new window





create view vw_billinginv
as
select		a.INV_NUM, a.INV_AMT 
from		dbo.BILLING_HDR a
where		ERR_CD = '0'
and			REQ_ID = 3895

Open in new window

chokkaStudentAsked:
Who is Participating?
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.

Brian CroweDatabase AdministratorCommented:
SELECT H.inv_num, H.inv_amt, SUM(L.line_amt) AS LINE_FEES
FROM Billing_Hdr AS H
INNER JOIN Billing_Line AS L
      ON H.inv_num = L.inv_num
GROUP BY H.inv_num, H.inv_amt
HAVING H.inv_amt <> SUM(L.line_amt)
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's wrong with your original query?
0
chokkaStudentAuthor Commented:
My apologize for the delay, I will check into the query and respond back
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

From novice to tech pro — start learning today.

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.