Link to home
Start Free TrialLog in
Avatar of fwstealer
fwstealerFlag for United States of America

asked on

union columns

i have four columns and need them to be combined as two.

i.FeeName as ShortFeeName,
i.Description as FeeName,  
i.Amount,
CA_FeeName = case a.stateid when 17 then 'California Property Tax' end,
CA_ArrivalTax = case a.stateid when 17 then convert(money,(118.16)) else 0 end,


-- need CA_FeeName included in i.Description as FeeName
-- need CA_ArrivalTax included in i.Amount

I'm not sure to how to do this as I haven't before do i need to use a union in the sense that i do a query without the CA stuff then union that query to a new query to include the CA stuff?
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fwstealer

ASKER

okay but the results are different as current amounts are over written with 118.16 when i need to keep them.

my sql to get results:
i.FeeName as ShortFeeName,
i.Description as FeeName,  
i.Amount,
CA_FeeName = case a.stateid when 17 then 'California Property Tax' end,
CA_ArrivalTax = case a.stateid when 17 then convert(money,(118.16)) else 0 end,


suggested sql to get results:
i.FeeName as ShortFeeName,
CASE WHEN a.stateid <> 17 THEN i.Description
    ELSE 'California Property Tax' END as ShortFeeName,
CASE WHEN a.stateid <> 17 THEN i.Amount
    ELSE convert(money,(118.16)) END AS Amount,

see the attached for the data results
Book1.xls
OK, so what is the result that you *want*?

:)