sql case when statement

Question

Post reply

sql case when statement

niha.736

Ten Centuries

Points: 1210
More actions
February 4, 2014 at 3:13 am

#396217

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 6 posts - 1 through 5 (of 5 total)

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

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 1

Do you must replace the hard coded values with variables/dynamic code?

Having those 6 lines of code for those years, and adding some additional could resolve your issue for "forever".

Does it worth replacing with variables/functions the above code in this case?

Igor Micev,My blog: www.igormicev.com

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 2

I find the last case to be confusing... soemthing before 6/4/2009 or after 5/4/2014 gets the same 2008 trans date?

Anyway, you can add a table with a start and stop date and the transdate. You'll need to do a "SELECT TOP 1" or equivilent (Row numbers = 1 etc) and add an ordering to it (so that if you have a 6/5/2013 you get the correct trans date).

SET @StartDate = ISNULL((

SELECT TOP 1 [TranslatedDate]

FROM dbo.[TranslateDates]

WHERE [StartDate] <= @Trandate AND

[EndDate] >= @TranDate

ORDER BY [StartDate] DESC), '1/1/1900')

niha.736 (2/4/2014)
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

Lowell SSC Guru Points: 323518 More actions · Answer 3

there's a lot of logic holes in here; are we trying to find the fiscal start date or something? is the 05 date a copy paste err, and it should be 06?

if todays date is 02-04-2014, i assume the fiscal period is from 06-04-2013 to 05-04-2014, is that right?

what is the fiscal period for the date 05-05-2013 then, since it is outside of your 11 month parameters you seem to be propigating. there's a black hole of no data if it's in that no mans land of 05-05 to

i would think this is nothing more than a simple date add; if the date is < 06/04 of the current year, it's simply last years period, right?

/*

TrandateFiscalStartFiscalEndStartdate

2014-02-04 09:40:00.3802013-06-04 00:00:00.0002014-06-04 00:00:00.0002013-06-04 00:00:00.000

2013-05-05 00:00:00.0002012-06-04 00:00:00.0002013-06-04 00:00:00.0002012-06-04 00:00:00.000

2012-11-05 00:00:00.0002011-06-04 00:00:00.0002012-06-04 00:00:00.0002011-06-04 00:00:00.000

1962-12-11 00:00:00.0001961-06-04 00:00:00.0001962-06-04 00:00:00.0001961-06-04 00:00:00.000

*/

with MyFakeData

AS

(

SELECT getdate() AS Trandate UNION ALL

SELECT '2013-05-05' UNION ALL

SELECT '2012-11-05' UNION ALL

SELECT '1962-12-11'

)

select Trandate ,

--year(Trandate),

--DATEDIFF(dd,Trandate,dateadd(yy,year(Trandate) - 1900,'1900-06-04')),

dateadd(yy,year(Trandate) - 1901,'1900-06-04') As FiscalStart,

dateadd(yy,year(Trandate) - 1900,'1900-06-04') As FiscalEnd,

Startdate= dateadd(yy,year(Trandate) - 1901,'1900-06-04')

from MyFakeData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

below86 SSChampion Points: 11550 More actions · Answer 4

See if this does what you want.

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))

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.

David McKinney SSChampion Points: 10472 More actions · Answer 5

Lowell (2/4/2014)
there's a lot of logic holes in here; are we trying to find the fiscal start date or something? is the 05 date a copy paste err, and it should be 06?
if todays date is 02-04-2014, i assume the fiscal period is from 06-04-2013 to 05-04-2014, is that right?
what is the fiscal period for the date 05-05-2013 then, since it is outside of your 11 month parameters you seem to be propigating. there's a black hole of no data if it's in that no mans land of 05-05 to

I think what you're missing is that the dates are in dd/mm/yyyy format and not mm/dd/yyyy. I think we need something like you suggest above along the lines of the following.

DECLARE @d DATE

SELECT @d=getdate()

SELECT dateadd(yy,datediff(yy,'6 april 1900',@d),'6 april 1900')