Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sherrireid
Yak Posting Veteran
58 Posts |
Posted - 2013-07-24 : 18:05:39
|
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:--- Create Invoice totalscreate 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 IVIFwhere IVIF.INV_STATUS = 4 andIVIF.FLX_UPDATE_DATE >= '07/01/2013' andIVIF.FLX_UPDATE_DATE <= '07/10/2013'group by IVIF.VEND_CODEorder by IVIF.VEND_CODE--- Create Payment totalscreate 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 CATRwhere CATR.DOC_DATE >= '07/01/2013' andCATR.DOC_DATE <= '07/10/2013' andCATR.DOC_STATUS in (1,3,4) andCATR.CASH_TRAN_TYPE IN (1,3)group by CATR.CP_CODE,CATR.DOC_STATUSorder by CATR.CP_CODE-- Create Void Paymentscreate table #PymntVoid (VendCode Char(60),PymntAmt money)insert into #PymntVoid(VendCode, PymntAmt)SELECTCATR.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 byCATR.CP_CODEorder by CATR.CP_CODE--- MAIN QUERYselect CATR.CP_CODE AS 'VENDOR NO',RemitVendor.MISC_FED_TAX,CASE when VENP.PRINT_ALIAS = 1 then VENP.VEND_ALIASwhen VENP.PRINT_ALIAS = 0 then VENP.ALT_VEND_NAMEEND 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',#PymntInfo.PymntAmt + #PymntVoid.PymntAmt AS 'PaymentAmt',VendB.TERM_RL_CODE AS 'Pay Term',CASE WHEN VENP.ACH_VEND_SW = 1 then 'ACH'else 'CHECK'end PAYMENT_Type,Addr.PHONEfrom [H-HEFMDB].HEFM.dbo.CATR CATRjoin [H-HEFMDB].HEFM.dbo.VENP VENPon CATR.CP_CODE = VENP.VEND_CODE andCATR.CP_LOC_CODE = VENP.VEND_PAY_CODE andCATR.FLX_PRS_OWNER = VENP.FLX_PRS_OWNERJOIN VEND on VEND.VEND_CODE = VENP.VEND_CODE andVEND.VEND_CODE = CATR.CP_LOC_CODEJOIN CATD on (CATR.FLX_PRS_OWNER = CATD.FLX_PRS_OWNER ANDCATR.CP_CODE = CATD.CP_CODE ANDCATR.CP_LOC_CODE = CATD.CP_LOC_CODE ANDCATR.CASH_TRAN_ID = CATD.CASH_TRAN_ID ANDCATR.ORIG_BATCH_ID = CATD.ORIG_BATCH_ID )join [H-HEFMDB].HEFM.dbo.VENB VendBon VendB.VEND_BUY_CODE = VENP.VEND_PAY_CODEjoin [H-HEMMDB].HEMM.dbo.VEND_REMIT RemitVendoron RemitVendor.VEND_NO = VEND.VEND_CODEjoin [H-HEMMDB].HEMM.dbo.ADDR Addr on Addr.ADDR_ID = RemitVendor.ADDR_IDjoin #InvInfoon #InvInfo.VendCode = CATR.CP_CODEjoin #PymntInfo on #PymntInfo.VendCode = CATR.CP_CODEleft outer join #PymntVoidon #PymntVoid.VendCode = CATR.CP_CODEgroup byCATR.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.PymntAmtdrop table #InvInfodrop table #PymntInfodrop table #PymntVoidThanksSherri ReidSLReidForum NewbieRenton, WA USA |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-24 : 20:49:43
|
Couple of comments in red below: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 totalscreate 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 IVIFwhere IVIF.INV_STATUS = 4 andIVIF.FLX_UPDATE_DATE >= '07/01/2013' andIVIF.FLX_UPDATE_DATE <= '07/10/2013'group by IVIF.VEND_CODEorder by IVIF.VEND_CODE--- Create Payment totalscreate 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 CATRwhere CATR.DOC_DATE >= '07/01/2013' andCATR.DOC_DATE <= '07/10/2013' andCATR.DOC_STATUS in (1,3,4) andCATR.CASH_TRAN_TYPE IN (1,3)group by CATR.CP_CODE,CATR.DOC_STATUSorder by CATR.CP_CODE-- Create Void Paymentscreate table #PymntVoid (VendCode Char(60),PymntAmt money)insert into #PymntVoid(VendCode, PymntAmt)SELECTCATR.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 byCATR.CP_CODEorder by CATR.CP_CODE--- MAIN QUERYselect DISTINCT -- this might help CATR.CP_CODE AS 'VENDOR NO',RemitVendor.MISC_FED_TAX,CASE when VENP.PRINT_ALIAS = 1 then VENP.VEND_ALIASwhen VENP.PRINT_ALIAS = 0 then VENP.ALT_VEND_NAMEEND 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.PHONEfrom [H-HEFMDB].HEFM.dbo.CATR CATRjoin [H-HEFMDB].HEFM.dbo.VENP VENPon CATR.CP_CODE = VENP.VEND_CODE andCATR.CP_LOC_CODE = VENP.VEND_PAY_CODE andCATR.FLX_PRS_OWNER = VENP.FLX_PRS_OWNERJOIN VEND on VEND.VEND_CODE = VENP.VEND_CODE andVEND.VEND_CODE = CATR.CP_LOC_CODEJOIN CATD on (CATR.FLX_PRS_OWNER = CATD.FLX_PRS_OWNER ANDCATR.CP_CODE = CATD.CP_CODE ANDCATR.CP_LOC_CODE = CATD.CP_LOC_CODE ANDCATR.CASH_TRAN_ID = CATD.CASH_TRAN_ID ANDCATR.ORIG_BATCH_ID = CATD.ORIG_BATCH_ID )join [H-HEFMDB].HEFM.dbo.VENB VendBon VendB.VEND_BUY_CODE = VENP.VEND_PAY_CODEjoin [H-HEMMDB].HEMM.dbo.VEND_REMIT RemitVendoron RemitVendor.VEND_NO = VEND.VEND_CODEjoin [H-HEMMDB].HEMM.dbo.ADDR Addr on Addr.ADDR_ID = RemitVendor.ADDR_IDjoin #InvInfoon #InvInfo.VendCode = CATR.CP_CODEjoin #PymntInfo on #PymntInfo.VendCode = CATR.CP_CODEleft outer join #PymntVoid -- This left join might be adding additional rows on #PymntVoid.VendCode = CATR.CP_CODEgroup byCATR.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.PymntAmtdrop table #InvInfodrop table #PymntInfodrop table #PymntVoid[/CODE]ThanksSherri ReidSLReidForum NewbieRenton, WA USA
|
|
|
sherrireid
Yak Posting Veteran
58 Posts |
Posted - 2013-07-25 : 13:43:46
|
THank you very much!! I'll give these things a try today!SLReidForum NewbieRenton, WA USA |
|
|
sherrireid
Yak Posting Veteran
58 Posts |
Posted - 2013-07-25 : 15:20:12
|
Well, unfortunately I still ended up with 2 rows of data with your suggestion. However, while I was working on that I thought of a different way to attack the problem - and that worked!! So yay! Thank you very much for trying.SLReidForum NewbieRenton, WA USA |
|
|
|
|
|
|
|