quote:

---

Originally posted by sherrireid

I have a query where I need to gather some invoice/payment data at the summary level and then put that together with the detail info regarding the vendors.

I create 3 temp tables for Invoices, Payments and Voids and then I join these temp tables to the main query where I gather vendor name, address, tax id, etc.

The problem I am having is that the user only wants one line of data for each vendor.

When there is a void for the vendor,it creates a 2nd line even though I am grouping by the vendor code.

To get the total payment amount, in the main query I am adding together the void payment amount and the payment amount. I don't understand how this could create 2 lines of data.

Does anyone have any ideas?

Here is my code:
[CODE]
--- Create Invoice totals

create table #InvInfo (
VendCode char(60),
INVCount int,
INVDollar money,
INVDisc money)


insert into #InvInfo
(VendCode, INVCount, INVDollar, INVDisc)


select
IVIF.VEND_CODE,
COUNT(IVIF.TB_STATUS),
SUM(IVIF.INV_AMT),
SUM(IVIF.DISC_AMT)

from IVIF

where IVIF.INV_STATUS = 4 and
IVIF.FLX_UPDATE_DATE >= '07/01/2013' and
IVIF.FLX_UPDATE_DATE <= '07/10/2013'

group by IVIF.VEND_CODE

order by IVIF.VEND_CODE

--- Create Payment totals
create table #PymntInfo (
VendCode Char(60),
PymntCount int,
PymntAmt money)

insert into #PymntInfo
(VendCode, PymntCount, PymntAmt)

select
CATR.CP_CODE,
COUNT(CATR.TB_STATUS),
SUM(CATR.BANK_DOC_AMT)

from CATR
where
CATR.DOC_DATE >= '07/01/2013' and
CATR.DOC_DATE <= '07/10/2013' and
CATR.DOC_STATUS in (1,3,4) and
CATR.CASH_TRAN_TYPE IN (1,3)

group by CATR.CP_CODE,CATR.DOC_STATUS

order by CATR.CP_CODE


-- Create Void Payments

create table #PymntVoid (
VendCode Char(60),
PymntAmt money)

insert into #PymntVoid
(VendCode, PymntAmt)

SELECT
CATR.CP_CODE,
(SUM(CATR.SRC_DOC_AMT) * -1) AS PYMNT_AMT


FROM [H-HEFMDB].HEFM.dbo.CATR CATR

WHERE
CATR.REVERSAL_DATE >= '07/01/2013' and
CATR.REVERSAL_DATE <= '07/10/2013' and
CATR.BANK_ACCT_CODE ='10_APCHK' and
CATR.DOC_STATUS in (1,3,4) and
CATR.CASH_TRAN_TYPE IN (1,3)

group by

CATR.CP_CODE

order by CATR.CP_CODE


--- MAIN QUERY

select DISTINCT -- this might help
CATR.CP_CODE AS 'VENDOR NO',
RemitVendor.MISC_FED_TAX,
CASE
when VENP.PRINT_ALIAS = 1 then VENP.VEND_ALIAS
when VENP.PRINT_ALIAS = 0 then VENP.ALT_VEND_NAME
END VENDOR_NAME,
Addr.ADDR1 AS 'STREET-1',
Addr.ADDR2 AS 'STREET-2',
Addr.ADDR3 AS 'STREET-3',
Addr.CITY AS 'CITY',
Addr.STATE AS 'STATE',
Addr.POST_CODE AS 'ZIP',
substring(Addr.CNTRY_CD,7,3) AS 'COUNTRY',
RemitVendor.FED_TAX_NO AS 'TIN',
#InvInfo.INVCount AS 'INVOICE COUNT',
#InvInfo.INVDollar AS 'INVOICE AMT',
#InvInfo.INVDisc AS 'DISCOUNT',
#PymntInfo.PymntCount AS 'PaymentCount',
COALESCE(#PymntInfo.PymntAmt, 0) + COALESCE(#PymntVoid.PymntAmt, 0) AS 'PaymentAmt', -- else you will get NULL if either of these values are NULL
VendB.TERM_RL_CODE AS 'Pay Term',
CASE WHEN VENP.ACH_VEND_SW = 1 then 'ACH'
else 'CHECK'
end PAYMENT_Type,
Addr.PHONE


from [H-HEFMDB].HEFM.dbo.CATR CATR

join [H-HEFMDB].HEFM.dbo.VENP VENP
on CATR.CP_CODE = VENP.VEND_CODE and
CATR.CP_LOC_CODE = VENP.VEND_PAY_CODE and
CATR.FLX_PRS_OWNER = VENP.FLX_PRS_OWNER

JOIN VEND
on VEND.VEND_CODE = VENP.VEND_CODE and
VEND.VEND_CODE = CATR.CP_LOC_CODE

JOIN CATD
on (CATR.FLX_PRS_OWNER = CATD.FLX_PRS_OWNER AND
CATR.CP_CODE = CATD.CP_CODE AND
CATR.CP_LOC_CODE = CATD.CP_LOC_CODE AND
CATR.CASH_TRAN_ID = CATD.CASH_TRAN_ID AND
CATR.ORIG_BATCH_ID = CATD.ORIG_BATCH_ID )

join [H-HEFMDB].HEFM.dbo.VENB VendB
on VendB.VEND_BUY_CODE = VENP.VEND_PAY_CODE

join [H-HEMMDB].HEMM.dbo.VEND_REMIT RemitVendor
on RemitVendor.VEND_NO = VEND.VEND_CODE

join [H-HEMMDB].HEMM.dbo.ADDR Addr
on Addr.ADDR_ID = RemitVendor.ADDR_ID

join #InvInfo
on #InvInfo.VendCode = CATR.CP_CODE

join #PymntInfo
on #PymntInfo.VendCode = CATR.CP_CODE

left outer join #PymntVoid -- This left join might be adding additional rows
on #PymntVoid.VendCode = CATR.CP_CODE

group by

CATR.CP_CODE,
VENP.ALT_VEND_NAME,
RemitVendor.MISC_FED_TAX,
Addr.ADDR1 ,
Addr.ADDR2 ,
Addr.ADDR3 ,
Addr.CITY ,
Addr.STATE ,
Addr.POST_CODE ,
Addr.CNTRY_CD ,
RemitVendor.FED_TAX_NO ,
VendB.TERM_RL_CODE,
VENP.ACH_VEND_SW,
VENP.VEND_ALIAS,
Addr.PHONE ,
VEND.VEND_NAME,
VENP.PRINT_ALIAS,
#InvInfo.INVCount,
#InvInfo.INVDollar,
#InvInfo.INVDisc,
#InvInfo.VendCode,
#PymntInfo.PymntCount,
#PymntInfo.PymntAmt,
#PymntInfo.VendCode,
#PymntVoid.PymntAmt


drop table #InvInfo
drop table #PymntInfo
drop table #PymntVoid

[/CODE]
Thanks
Sherri Reid

SLReid
Forum Newbie
Renton, WA USA

---