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
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
create view vw_billinginv
select a.INV_NUM, a.INV_AMT
from dbo.BILLING_HDR a
where ERR_CD = '0'
and REQ_ID = 3895