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.
| Author |
Topic |
|
jaimealvarez
Starting Member
31 Posts |
 Posted - 2012-12-14 : 15:23:48
|
| Hi,I have the code below , CASE WHEN matter.mrtcode = 10 then (select timerate.tkrt10 FROM son_db.dbo.timerate timerate INNER JOIN ( Select tkinit, MAX(tkeffdate) AS tkeffdate FROM son_db.dbo.timerate GROUP BY tkinit) timerate2 ON timerate.tkinit = timerate2.tkinit and timerate.tkeffdate = timerate2.tkeffdate WHERE (timerate.tkinit=timecard.ttk) AND (timerate.tkrtcur='USD')AND (timerate.tkeffdate>={ts '2012-01-01 00:00:00'})) WHEN matter.mrtcode = 11 then (select timerate.tkrt11 FROM son_db.dbo.timerate timerate INNER JOIN ( Select tkinit, MAX(tkeffdate) AS tkeffdate FROM son_db.dbo.timerate GROUP BY tkinit) timerate2 ON timerate.tkinit = timerate2.tkinit and timerate.tkeffdate = timerate2.tkeffdate WHERE (timerate.tkinit=timecard.ttk) AND (timerate.tkrtcur='USD')AND (timerate.tkeffdate>={ts '2012-01-01 00:00:00'})) end AS 'BaseRate'This works fine but now I have to many lines, it goes from 10 to 50... so it is too long.All lines are the same excpet for the numbers (matter.mrtcode = 10 and timerate.tkrt10) ... so what I have to do is in the case where the code is equal to 10 it has to look for the field that ends in 10, this is the same all the way down to 50!Is there a way to make this a one-liner?Any help would be appreciated. Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-15 : 13:03:13
|
| sounds like unpivot scenario for me!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
Need to streamline "CASE" code from multiline to 1