SQL case when statement( without hardcode values)

Question

Post reply

SQL case when statement( without hardcode values)

niha.736

Ten Centuries

Points: 1210
More actions
February 4, 2014 at 2:35 am

#287893

Hi below is the scenario which I have currently in my query.
I need to write this query without any hardoce values , so that it will work til n number of years without modifications.
please can anybody shed light here...
Startdate =
CASE WHEN
Trandate between '06-04-2013' and '05-04-2014' then '06-04-2013'
Trandate between '06-04-2012' and '05-04-2013' then '06-04-2012'
Trandate between '06-04-2011' and '05-04-2012' then '06-04-2011'
Trandate between '06-04-2010' and '05-04-2011' then '06-04-2010'
Trandate between '06-04-2009' and '05-04-2010' then '06-04-2009'
Trandate between '06-04-2008' and '05-04-2019' then '06-04-2008'
END
Kind regards,
Niharika

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

KS Hall of Fame Points: 3323 More actions · Answer 1

This might not be the most elegant way but give it a try

SELECT StartDate =

CASE WHEN CONVERT(VARCHAR(8),@Trandate,112)

BETWEEN CONVERT(VARCHAR,YEAR(@Trandate)) + '0406'

AND CONVERT(VARCHAR,YEAR(@Trandate)+1) + '0405'

THEN CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@Trandate)) + '0406')

WHEN CONVERT(VARCHAR(8),@Trandate,112)

BETWEEN CONVERT(VARCHAR,YEAR(@Trandate)-1) + '0406'

AND CONVERT(VARCHAR,YEAR(@Trandate)) + '0405'

THEN CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@Trandate)-1) + '0406')

ELSE NULL

END

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

Sowbhari (2/4/2014)
This might not be the most elegant way but give it a try
SELECT StartDate =
CASE WHEN CONVERT(VARCHAR(8),@Trandate,112)
BETWEEN CONVERT(VARCHAR,YEAR(@Trandate)) + '0406'
AND CONVERT(VARCHAR,YEAR(@Trandate)+1) + '0405'
THEN CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@Trandate)) + '0406')
WHEN CONVERT(VARCHAR(8),@Trandate,112)
BETWEEN CONVERT(VARCHAR,YEAR(@Trandate)-1) + '0406'
AND CONVERT(VARCHAR,YEAR(@Trandate)) + '0405'
THEN CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@Trandate)-1) + '0406')
ELSE NULL
END

Well done. Tip: no need for the ELSE NULL. If you remove the ELSE NULL the behavior will still be NULL if the condition is not met. 😉

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

niha.736 Ten Centuries Points: 1210 More actions · Answer 3

Thanks for your response Sowbhari and Alan. It really helped me alot 🙂

below86 SSChampion Points: 11510 More actions · Answer 4

This looks to be a double posted question.

Here is what I replied to the other post. Why would you need 2 when statements? Never mind I see why, didn't run enough examples in my version.

It looks like I interpreted the dates differently.

declare @startdate datetime;

declare @trandate datetime;

set @trandate = '02/05/2012';

set @startdate = (select casewhen @trandate between '06/04/' + cast(year(@trandate) - 1 as char(4)) and '05/04/' + cast(year(@trandate) as char(4))

then '06/04/' + cast((year(@trandate) - 1) as char(4))

ELSE -- added after original post

'06/04/' + cast((year(@trandate)) as char(4)) -- added this after original post

end );

select @startdate as startdate, @trandate as trandate;

/*

startdatetrandate

2011-06-04 00:00:00.0002012-02-05 00:00:00.000

*/

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.