Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 482

# 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

Thanks

R8VI
0
R8VI
1 Solution

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

Commented:
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
``````
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.