another gem

richard101 2014-06-11 08:13:08

I would like to simplify these 3x Select statements into 1 (but not by using sub-selects). The 2nd and 3rd ones seem to be just shaping the data from #1 in a long-winded manor.
richard101 2014-06-11 09:55:57
done! (although I couldn't quite do it in one select) using a crazy case within the row_number function …

select GlobalPlanObjectID, MissingGlobalObjectLifeTimeID, MissingLifeTimeStatus, GlobalObjectStatusID from
    (SELECT gpo.GlobalPlanObjectID GlobalPlanObjectID, gol.GlobalObjectLifeTimeID MissingGlobalObjectLifeTimeID, sta.[Status] MissingLifeTimeStatus, GlobalObjectStatusID,
       ROW_NUMBER() OVER (PARTITION  BY GlobalPlanObjectID order by (CASE gol.GlobalObjectStatusID WHEN 20 THEN 1 WHEN 25 THEN 2 WHEN 24 THEN 3 ELSE 4 END)) AS RowNumber
    FROM dbo.tblGlobalPlanObject gpo
    JOIN dbo.tblGlobalObjectLifeTime gol ON gpo.GlobalPlanObjectID = gol.GlobalObjectID  
    JOIN dbo.tblStatus sta ON gol.GlobalObjectStatusID = sta.StatusID) MissingLifeTimes
where rownumber = 1
June 11, 2014
No Comments