SQL Union help - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Union help

Author

Topic

mj.richardson
Starting Member

2 Posts

Posted - 2011-04-21 : 09:29:25

Good Day all,

This is my very 1st post and I am new to the SQL community. Please be gentle with me.

I have a question please and need help solving this issue.

Here is the select statement with unions to tie them together. The end result I am looking for is to have this information show on one result line instead of it showing on two different lines.

select
--ch.assign,pinum,sinum,tinum,
inscode,i.name
,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email
,sum(chgamount-payins1-payins2-payguar-adjust-writeoff)as '0-30'
,0 as '31-60', 0 as '61-90',0 as '91-120', 0 as '120+ '
from clcharge ch
join clclaim cl
on ch.company=cl.company and ch.claimid=cl.claimid and ch.account=cl.account
and ch.incidentno=cl.incidentno
join clname n
on cl.company=n.company and cl.account=n.account and pinum=inum
join clins i
on i.code = n.inscode
where inscode = '0003009'
--ch.account = '416799'
and
( ch.assign='Y' and billdate is not null and billdate2 is null
and (billdate between (convert(datetime,convert(char,GetDate(),101)) - 30 )
and convert(datetime,convert(char,GetDate(),101)))) or inscode = '0003009' --or ch.account = '416799'
and
(ch.assign='Y' and billdate2 is not null and (billdate2 between (convert(datetime,convert(char,GetDate(),101))
- 30 )and convert(datetime,convert(char,GetDate(),101))))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='N' and patdate is not null and (patdate between (convert(datetime,convert(char,GetDate(),101))
- 30 )and convert(datetime,convert(char,GetDate(),101))))
and (chgamount-payins1-payins2-payguar-adjust-writeoff)<>0
and ch.assign <> 'n'
group by
--ch.assign,pinum,sinum,tinum,
inscode,i.name,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email
union ALL
select
--ch.assign,pinum,sinum,tinum,
inscode,i.name
,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email
,0
,sum(chgamount-payins1-payins2-payguar-adjust-writeoff),0,0,0
from clcharge ch
join clclaim cl
on ch.company=cl.company and ch.claimid=cl.claimid and ch.account=cl.account
and ch.incidentno=cl.incidentno
join clname n
on cl.company=n.company and cl.account=n.account and pinum=inum
join clins i
on i.code = n.inscode
where inscode = '0003009'
--ch.account = '416799'
and
(ch.assign='Y' and billdate is not null and billdate2 is null
and (billdate between (convert(datetime,convert(char,GetDate(),101)) - 60 ) and
(convert(datetime,convert(char,GetDate(),101))-31)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='Y' and billdate2 is not null and (billdate2 between
(convert(datetime,convert(char,GetDate(),101)) - 60 ) and (convert(datetime,convert(char,GetDate(),101))
-31)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='N' and patdate is not null and (patdate between (convert(datetime,convert(char,GetDate(),101))
- 60 ) and (convert(datetime,convert(char,GetDate(),101))-31)))
and (chgamount-payins1-payins2-payguar-adjust-writeoff)<>0
and ch.assign <> 'n'
group by
--ch.assign,pinum,sinum,tinum,
inscode,i.name,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email

Results (note! shown with information that is causing me difficulties)
inscode 0-30 31-60 61-90 91-120 120+
0003009 0.00 0.00 0.00 400.00 0.00
0003009 0.00 0.00 0.00 0.00 2875.28

What I am looking to have return is the following

inscode 0-30 31-60 61-90 91-120 120+
0003009 0.00 0.00 0.00 400.00 2875.28

Please any help would be great.. if there is anything I have left out let me know again I am a newbie

Mario J Richardson

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-21 : 09:35:28

SELECT inscode < and all the other columns that aren't giving you a problem>
, sum[0-30] as [0-30], etc
FROM
(the query you already wrote) as t
GROUP BY <the stuff in the select list you aren't summing>

Jim

Everyday I learn something that somebody else already knew

mj.richardson
Starting Member

2 Posts

Posted - 2011-04-21 : 10:03:32

Thanks Jim for your quick response. This is what I tried
select
--ch.assign,pinum,sinum,tinum,
inscode,i.name
,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email
,0,0,0
,sum(chgamount-payins1-payins2-payguar-adjust-writeoff),0
from clcharge ch
join clclaim cl
on ch.company=cl.company and ch.claimid=cl.claimid and ch.account=cl.account
and ch.incidentno=cl.incidentno
join clname n
on cl.company=n.company and cl.account=n.account and pinum=inum
join clins i
on i.code = n.inscode
where inscode = '0003009'
--ch.account = '416799'
and
(ch.assign='Y' and billdate is not null and billdate2 is null
and (billdate between (convert(datetime,convert(char,GetDate(),101)) - 120 ) and
(convert(datetime,convert(char,GetDate(),101))-91)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='Y' and billdate2 is not null and (billdate2 between
(convert(datetime,convert(char,GetDate(),101)) - 120 ) and (convert(datetime,convert(char,GetDate(),101))
-91)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='N' and patdate is not null and (patdate between (convert(datetime,convert(char,GetDate(),101))
- 120 ) and (convert(datetime,convert(char,GetDate(),101))-91)))
and (chgamount-payins1-payins2-payguar-adjust-writeoff)<>0
and ch.assign <> 'n'
From
(
select
--ch.assign,pinum,sinum,tinum,
inscode,i.name
,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email
,0,0,0
,sum(chgamount-payins1-payins2-payguar-adjust-writeoff),0
from clcharge ch
join clclaim cl
on ch.company=cl.company and ch.claimid=cl.claimid and ch.account=cl.account
and ch.incidentno=cl.incidentno
join clname n
on cl.company=n.company and cl.account=n.account and pinum=inum
join clins i
on i.code = n.inscode
where inscode = '0003009'
--ch.account = '416799'
and
(ch.assign='Y' and billdate is not null and billdate2 is null
and (billdate between (convert(datetime,convert(char,GetDate(),101)) - 120 ) and
(convert(datetime,convert(char,GetDate(),101))-91)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='Y' and billdate2 is not null and (billdate2 between
(convert(datetime,convert(char,GetDate(),101)) - 120 ) and (convert(datetime,convert(char,GetDate(),101))
-91)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='N' and patdate is not null and (patdate between (convert(datetime,convert(char,GetDate(),101))
- 120 ) and (convert(datetime,convert(char,GetDate(),101))-91)))
and (chgamount-payins1-payins2-payguar-adjust-writeoff)<>0
and ch.assign <> 'n'
group by
--ch.assign,pinum,sinum,tinum,
inscode,i.name,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email
union ALL
select
--ch.assign,pinum,sinum,tinum,
inscode,i.name
,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email
,0,0,0,0
, sum(chgamount-payins1-payins2-payguar-adjust-writeoff)
from clcharge ch
join clclaim cl
on ch.company=cl.company and ch.claimid=cl.claimid and ch.account=cl.account and ch.incidentno=cl.incidentno
join clname n
on cl.company=n.company and cl.account=n.account and pinum=inum
join clins i
on i.code = n.inscode
where inscode = '0003009'
--ch.account = '416799'
and
(ch.assign='Y' and billdate is not null and billdate2 is null
and (billdate < (convert(datetime,convert(char,GetDate(),101))-120)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='Y' and billdate2 is not null and (billdate2 < (convert(datetime,convert(char,GetDate(),101))
-120)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='N' and patdate is not null and (patdate < (convert(datetime,convert(char,GetDate(),101))
-120)))
and (chgamount-payins1-payins2-payguar-adjust-writeoff)<>0
and ch.assign <> 'n'
group by
--ch.assign,pinum,sinum,tinum,
inscode,i.name,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email
)
as t
group by
--ch.assign,pinum,sinum,tinum,
inscode,i.name,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email

results are
Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'From'.
Msg 156, Level 15, State 1, Line 150
Incorrect syntax near the keyword 'as'.

If I just run as follows:

select
--ch.assign,pinum,sinum,tinum,
inscode,i.name
,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email
,0,0,0
,sum(chgamount-payins1-payins2-payguar-adjust-writeoff),0
from clcharge ch
join clclaim cl
on ch.company=cl.company and ch.claimid=cl.claimid and ch.account=cl.account
and ch.incidentno=cl.incidentno
join clname n
on cl.company=n.company and cl.account=n.account and pinum=inum
join clins i
on i.code = n.inscode
where inscode = '0003009'
--ch.account = '416799'
and
(ch.assign='Y' and billdate is not null and billdate2 is null
and (billdate between (convert(datetime,convert(char,GetDate(),101)) - 120 ) and
(convert(datetime,convert(char,GetDate(),101))-91)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='Y' and billdate2 is not null and (billdate2 between
(convert(datetime,convert(char,GetDate(),101)) - 120 ) and (convert(datetime,convert(char,GetDate(),101))
-91)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='N' and patdate is not null and (patdate between (convert(datetime,convert(char,GetDate(),101))
- 120 ) and (convert(datetime,convert(char,GetDate(),101))-91)))
and (chgamount-payins1-payins2-payguar-adjust-writeoff)<>0
and ch.assign <> 'n'
group by
--ch.assign,pinum,sinum,tinum,
inscode,i.name,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email
union ALL
select
--ch.assign,pinum,sinum,tinum,
inscode,i.name
,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email
,0,0,0,0
, sum(chgamount-payins1-payins2-payguar-adjust-writeoff)
from clcharge ch
join clclaim cl
on ch.company=cl.company and ch.claimid=cl.claimid and ch.account=cl.account and ch.incidentno=cl.incidentno
join clname n
on cl.company=n.company and cl.account=n.account and pinum=inum
join clins i
on i.code = n.inscode
where inscode = '0003009'
--ch.account = '416799'
and
(ch.assign='Y' and billdate is not null and billdate2 is null
and (billdate < (convert(datetime,convert(char,GetDate(),101))-120)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='Y' and billdate2 is not null and (billdate2 < (convert(datetime,convert(char,GetDate(),101))
-120)))or inscode = '0003009'--or ch.account = '416799'
and
(ch.assign='N' and patdate is not null and (patdate < (convert(datetime,convert(char,GetDate(),101))
-120)))
and (chgamount-payins1-payins2-payguar-adjust-writeoff)<>0
and ch.assign <> 'n'
group by
--ch.assign,pinum,sinum,tinum,
inscode,i.name,i.address1
,i.address2
,i.city
,i.state
,i.zip
,i.phone
,i.attn
,i.form
,i.emcid
,i.ptype
,i.feetype
,i.email

results are

inscode name address1 address2 city state zip phone attn form emcid ptype feetype email (No column name) (No column name) (No column name) (No column name) (No column name)
0003009 GEICO INSURANCE-PIP PO BOX 9505 FREDERICKSBURG VA 22414 800/841-3000 HW 99999 AUTO STANDARD 0 0 0 400.00 0.00
0003009 GEICO INSURANCE-PIP PO BOX 9505 FREDERICKSBURG VA 22414 800/841-3000 HW 99999 AUTO STANDARD 0 0 0 0.00 2875.28

What did I do wrong

Mario J Richardson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources