SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Problem with grouping (I Think!)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sherrireid
Yak Posting Veteran

USA
58 Posts

Posted - 07/24/2013 :  18:05:39  Show Profile  Reply with Quote
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 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
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',
#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.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
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


Thanks
Sherri Reid

SLReid
Forum Newbie
Renton, WA USA

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/24/2013 :  20:49:43  Show Profile  Reply with Quote
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:

--- 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


Thanks
Sherri Reid

SLReid
Forum Newbie
Renton, WA USA

Go to Top of Page

sherrireid
Yak Posting Veteran

USA
58 Posts

Posted - 07/25/2013 :  13:43:46  Show Profile  Reply with Quote
THank you very much!! I'll give these things a try today!

SLReid
Forum Newbie
Renton, WA USA
Go to Top of Page

sherrireid
Yak Posting Veteran

USA
58 Posts

Posted - 07/25/2013 :  15:20:12  Show Profile  Reply with Quote
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.

SLReid
Forum Newbie
Renton, WA USA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000