CODEWITHSUNDEEP
sqlunioncrystal-reports-xi
Charvonne
Charvonne

Reputation: 1

Not able to get this union to work - Incorrect syntax near the keyword 'select' or 'as'

I am modifying an Accounts Receivable Aging report. I have four tables

@archg(fgc, pvd, [date], archg) 
@arpay(fgc, pvd, [date], arpay)
@chg(fgc, pvd, [date], chg)
@pay(fgc, pvd, [date], pay)

What I need is to have combine the (archg and chg) to equal one total and (arpay and pay) to equal one total. I am not sure if I am going about this the right way. Any help would be greatly appreciated.

select distinct
vp.person_name as pvd,
lfg.FinancialGroup_Code as fgc,
select (sum(isnull(chg,0)) 
    from @chg
    where  [date]<= 30
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 0-30
UNION
    select (sum(isnull(archg,0)) 
    from @archg
    where  [date]<= 30
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 0-30;

select (sum(isnull(chg,0)) 
    from @chg  
    where [date] > 30
    and [date]<= 60
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 31-60
UNION
Select (sum(isnull(archg,0)) 
    from @archg  
    where [date] > 30 and [date]<= 60
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 31-60;

Select (sum(isnull(chg,0)) 
    from @chg  
    where [date] > 61 and [date]<= 90
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 61-90
UNION
Select (sum(isnull(archg,0)) 
    from @archg  
    where [date] > 61 and [date]<= 90
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 61-90;

Select (sum(isnull(chg,0)) 
    from @chg  
    where [date] > 90 and [date]<= 120
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 91-120
UNION
Select (sum(isnull(archg,0)) 
    from @archg  
    where [date] > 90 and [date]<= 120
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 91-120;

Select (sum(isnull(chg,0)) 
    from @chg  
    where [date] > 120 and [date]<= 150
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 121-150
UNION
Select (sum(isnull(archg,0)) 
    from @archg  
    where [date] > 120 and [date]<= 150
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 121-150;

Select (sum(isnull(chg,0)) 
    from @chg  
    where [date] > 150 and [date]<= 180
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 151-180
UNION
Select (sum(isnull(archg,0)) 
    from @archg  
    where [date] > 150 and [date]<= 180
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 151-180;
Select (sum(isnull(chg,0)) 
    from @chg  
    where [date] > 180 and [date]<= 365
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 181-365
UNION
Select (sum(isnull(archg,0)) 
    from @archg  
    where [date] > 180 and [date]<= 365
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 181-365;
Select (sum(isnull(chg,0)) 
    from @chg  
    where [date] > 365 and [date]<= 730
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 366-730 
UNION
Select (sum(isnull(archg,0)) 
    from @archg  
    where [date] > 365and [date]<= 730
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 366-730;
Select (sum(isnull(archg,0)) 
    from @archg  
    where [date] > 730
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 731+
UNION
Select (sum(isnull(archg,0)) 
    from @archg  
    where [date] > 730
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges 731+;
--  Select (sum(isnull(chg,0)) from @chg  
--      where fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Charges Total

Select (sum(isnull(pay,0)) from @pay
    where  [date]<= 30
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 0-30
UNION
    Select (sum(isnull(arpay,0)) from @arpay
    where  [date]<= 30
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 0-30;

Select (sum(isnull(pay,0)) from @pay  
    where [date] > 30
    and datediff(day, date, @EndDate)<= 60
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 31-60
UNION
Select (sum(isnull(arpay,0)) from @arpay  
    where [date] > 30 and [date]<= 60
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 31-60;
Select (sum(isnull(pay,0)) from @pay  
    where [date] > 61 and [date]<= 90
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 61-90 
UNION
Select (sum(isnull(arpay,0)) from @arpay  
    where [date] > 61 and [date]<= 90
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 61-90;
Select (sum(isnull(pay,0)) from @pay  
    where [date] > 90 and [date]<= 120
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 91-120 
UNION
Select (sum(isnull(arpay,0)) from @arpay  
    where [date] > 90 and [date]<= 120
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 91-120;
Select (sum(isnull(pay,0)) from @pay  
    where [date] > 120 and [date]<= 150
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 121-150 
UNION
Select (sum(isnull(arpay,0)) from @arpay  
    where [date] > 120 and [date]<= 150
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 121-150;
Select (sum(isnull(pay,0)) from @pay  
    where [date] > 150 and [date]<= 180
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 151-180
UNION
Select (sum(isnull(arpay,0)) from @arpay  
    where [date] > 150 and [date]<= 180
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 151-180;
Select (sum(isnull(pay,0)) from @pay  
    where [date] > 180 and [date]<= 365
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 181-365 
UNION
Select (sum(isnull(arpay,0)) from @arpay  
    where [date] > 180 and [date]<= 365
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 181-365;
Select (sum(isnull(pay,0)) from @pay  
    where [date] > 365 and [date]<= 730
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 366-730 
UNION
Select (sum(isnull(arpay,0)) from @arpay  
    where [date] > 365and [date]<= 730
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 366-730;
Select (sum(isnull(arpay,0)) from @arpay  
    where [date] > 730
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 731+
UNION
Select (sum(isnull(arpay,0)) from @arpay  
    where [date] > 730
    and fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as pmt 731+;
--  Select (sum(isnull(pay,0)) from @pay
--      where fgc=lfg.FinancialGroup_Code and pvd=vp.person_name) as Pmt Total
into #AR
from
@chg, @pay, @archg, @arpay

order by
vp.person_name

--select * from @chg
--select * from @pay
--select * from #AR

select 
pvd as provider,
fgc,
isnull(isnull([Charges 0-30],0)-isnull([Pmt 0-30],0),0) as '0-30',
isnull(isnull([Charges 31-60],0)-isnull([Pmt 31-60],0),0) as '31-60',
isnull(isnull([Charges 61-90],0)-isnull([Pmt 61-90],0),0) as '61-90',
isnull(isnull([Charges 91-120],0)-isnull([Pmt 91-120],0),0) as '91-120',
isnull(isnull([Charges 121-150],0)-isnull([Pmt 121-150],0),0) as '121-150',
isnull(isnull([Charges 151-180],0)-isnull([Pmt 151-180],0),0) as '151-180',
isnull(isnull([Charges 181-365],0)-isnull([Pmt 181-365],0),0) as '181-365',
isnull(isnull([Charges 366-730],0)-isnull([Pmt 366-730],0),0) as '366-730',
isnull(isnull([Charges 731+],0)-isnull([Pmt 731+],0),0) as '731+'
--isnull(isnull([Charges Total],0)-isnull([Pmt Total],0),0) as 'Total'
--sum(isnull(isnull([Charges 0-30],0)-isnull([Pmt 0-30],0),0)+
--isnull(isnull([Charges 31-60],0)-isnull([Pmt 31-60],0),0)+
--isnull(isnull([Charges 61-90],0)-isnull([Pmt 61-90],0),0)+
--isnull(isnull([Charges 91-120],0)-isnull([Pmt 91-120],0),0)+
--isnull(isnull([Charges 121-150],0)-isnull([Pmt 121-150],0),0)+
--isnull(isnull([Charges 151-180],0)-isnull([Pmt 151-180],0),0)+
--isnull(isnull([Charges 181-365],0)-isnull([Pmt 181-365],0),0)+
--isnull(isnull([Charges 366-730],0)-isnull([Pmt 366-730],0),0)+
--isnull(isnull([Charges 731+],0)-isnull([Pmt 731+],0),0)) as 'Total'
from #AR
--group by pvd,fgc,
--[Charges 0-30],[Charges 31-60],[Charges 61-90],[Charges 91-120],[Charges 121-150],[Charges     --151-180],
--[Charges 181-365],[Charges 366-730],[Charges 731+],[Pmt 0-30],[Pmt 31-60],[Pmt 61-90],[Pmt     91-120],
--[Pmt 121-150],[Pmt 151-180],[Pmt 181-365],[Pmt 366-730],[Pmt 731+]
--order by 1,2

DROP TABLE #AR

Upvotes: 0

Views: 319

Answers (1)

jeroenh
jeroenh

Reputation: 26782

What you have looks very wrong to me, or at least WAY too complex. You should start over: decompose the problem and go step by step. I won't completely solve the problem, but this should get you started.

First, you want to combine 2 tables. OK, then start by combining them! E.g. for pay:

@pay(fgc, pvd, [date], pay) 
@arpay(fgc, pvd, [date], arpay) 

SELECT fgc, pvd, [date], pay from @pay
UNION 
SELECT fgc, pvd, [date], arpay as pay from @arpay

Similar for the other two tables.

Run the query and look at the results. You seem to want to group by certain date ranges. OK, so maybe we can add a property to group by:

SELECT fgc, pvd, [date], pay, 

CASE WHEN [date]<= 30 THEN '0-30'
     WHEN [date]> 30 AND [date] <= 60 THEN '30-60'
-- ETC, you get the idea
     ELSE '>730'
END AS daterange

from (
    SELECT fgc, pvd, [date], pay from @pay
    UNION 
    SELECT fgc, pvd, [date], arpay as pay from @arpay
) allpays

Run the query, look at the results. Small warning: it may be pretty slow as a full table scan will be required. But I'm mainly trying to illustrate a way of thinking here, you can optimize later.

OK, now you have something you can group by (fgc, pvd):

 SELECT fgc, pvd, sum(pay) FROM (
 -- insert the above subquery here
 ) querytogroup
 group by fgc, pvd

This can be joined with your entity tables (lfg, vp). And so on...

Upvotes: 2

Related Questions