August 6, 2016

SQL Date Practical Question

Question here was we need to find the maximum date from the list of rows.
Consider an example…….



with Dataset (dt1, dt2, dt3)
as  (

select Getdate() as dt1 , getdate() + 10 as dt2 , getdate() + 3 as dt3
union
select Getdate() + 5 as dt1 , getdate() -5   as dt2 , getdate() + 12 as dt3
union
select Getdate() + 10 as dt1 , getdate() -3   as dt2 , getdate() + 4 as dt3


)


SELECT * FROM Dataset



The Question is we need a maximum date in each row

We can write the query in multiple ways. But simplest way to get the output is as follows

SELECT
CASE WHEN (dt1 > dt2) and (dt1> dt3) then dt1 end,

CASE WHEN (dt2 > dt3) and (dt2> dt1) then dt2 end,

CASE WHEN (dt3 > dt1) and (dt3> dt2) then dt3 end,

dt1, dt2, dt3


FROM dataset







Add your comments or different ways to get the above output

No comments:

Post a Comment