SQL, CASE

HI,

I have the following query

INSERT INTO #tbltest ([2008], [2009], [2010], [2011])
Select
            case when a.[2008] > b.[2008] then 'B' else 'b'  end as '2008',
            case when a.[2009] > b.[2009] then 'B' else 'b'  end as '2009',
            case when a.[2010] > b.[2010] then 'B' else 'b'  end as '2010',
            case when a.[2011] > b.[2011] then 'B' else ''b  end as '2011'
      From  #tbl1 a join #tbl2 b on a.UKey = b.UKey

in #tbl1 I have a record for 2008 = 0.80
in #tbl2 I have a record for 2008 = 0.70

I need to do some computation like this the first record (0.80) - second record (0.70) * 100

So when I do select * from #tbltest - the results are displayed like so

b (10)
B (5)

and so forth

please help

Thanks

R8VI
R8VIAsked:
Who is Participating?
 
LIONKINGCommented:
Will something like this work for you?

INSERT INTO #tbltest ([2008], [2009], [2010], [2011])
Select 
            case when a.[2008] > b.[2008] then 'B' else 'b'  end + ' (' + CONVERT(VARCHAR(6),(a.[2008]-b.[2008])*100) + ')' as '2008',
            case when a.[2009] > b.[2009] then 'B' else 'b'  end + ' (' + CONVERT(VARCHAR(6),(a.[2009]-b.[2009])*100) + ')' as '2009',
            case when a.[2010] > b.[2010] then 'B' else 'b'  end + ' (' + CONVERT(VARCHAR(6),(a.[2010]-b.[2010])*100) + ')' as '2010',
            case when a.[2011] > b.[2011] then 'B' else ''b  end + ' (' + CONVERT(VARCHAR(6),(a.[2011]-b.[2011])*100) + ')' as '2011'
      From  #tbl1 a join #tbl2 b on a.UKey = b.UKey

Open in new window

0
 
David KrollCommented:
INSERT INTO #tbltest ([2008], [2009], [2010], [2011])
Select
            case
              when a.[2008] > b.[2008] then coalesce('B(', '') + coalesce(convert(varchar, (a.[2008] -   b.[2008]) * 100), '') + coalesce(')', '')
              else coalesce('b(', '') + coalesce(convert(varchar, (b.[2008] -   a.[2008]) * 100), '') + coalesce(')', '')   end as '2008',
            case
              when a.[2009] > b.[2009] then coalesce('B(', '') + coalesce(convert(varchar, (a.[2009] -   b.[2009) * 100), '') + coalesce(')', '')
              else coalesce('b(', '') + coalesce(convert(varchar, (b.[2009] -   a.[2009]) * 100), '') + coalesce(')', '')   end as '2009',
            case
              when a.[2010] > b.[2010] then coalesce('B(', '') + coalesce(convert(varchar, (a.[2010] -   b.[2010]) * 100), '') + coalesce(')', '')
              else coalesce('b(', '') + coalesce(convert(varchar, (b.[2010] -   a.[2010]) * 100), '') + coalesce(')', '')   end as '2010',
            case
              when a.[2011] > b.[2011] then coalesce('B(', '') + coalesce(convert(varchar, (a.[2011] -   b.[2011]) * 100), '') + coalesce(')', '')
              else coalesce('b(', '') + coalesce(convert(varchar, (b.[2011] -   a.[2011]) * 100), '') + coalesce(')', '')   end as '2011',
      From  #tbl1 a join #tbl2 b on a.UKey = b.UKey
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.