February 4, 2014 at 2:35 am
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
February 4, 2014 at 2:55 am
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
February 4, 2014 at 6:46 am
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. 😉
-- Itzik Ben-Gan 2001
February 4, 2014 at 8:14 am
Thanks for your response Sowbhari and Alan. It really helped me alot 🙂
February 5, 2014 at 7:34 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply