May 23, 2019 at 6:53 am
Hi,
I have a Date calendar table which has a range of dates between 01/01/2000 to 31/12/2050.
It current has various Flag fields like Current Day, Previous Day, Month To Date where they will show a 1 or 0 where it is set to 1 if condition is matched.
I want to add a field "Current Qtr" flag but not sure what use as a condition? So for the example below, this is what I am doing to get the Flags for CurrentDayFlag and PreviousDayFlag :
Declare @TodaysDate Date
Declare @CurrentYear int
Set @TodaysDate = GETDATE()
--Set @TodaysDate = '2018-12-30 00:00:00.000'
Set @YesterdayDate = DATEADD(D,-1,@TodaysDate)
Set @CurrentYear = YEAR(@TodaysDate)
Select
case when convert(date,c.DateKey) = @TodaysDate THEN 1 ELSE 0 END as CurrentDay
,case when convert(date,c.DateKey) = @YesterdayDate THEN 1 ELSE 0 END as PreviousDay
FROM
dbo.Calendar c
So at time of writing this todays date = 23rd May 2019, so the for the row on the table dbo.Calendar where they DateKey = '2019-05-23 00:00:00' the field CurrentDay will show as 1 and the field PreviousDay will be 0, for the DateKey = '2019-05-22 00:00:00' the field CurrentDay will show as 0 and the field PreviousDay will be 1
For the CurrentQtr which is based on the Calendar Year:
Months
Jan, Feb, Mar = Qtr 1
Apr, May, Jun = Qtr 2
Jul, Aug, Sep = Qtr 3
Oct, Nov, Dec= Qtr 4
As Today's date is in the Month of May I want the CurrentQtr field set to 1 for the dates between 01/04/2019 and 30/06/2019 and all the other dates to be set to 0 as they don't meet the condition.
Thanks
Thanks
May 23, 2019 at 7:48 am
Something like this?
CASE WHEN
DATEPART(QUARTER,c.DateKey) = DATEPART(QUARTER,@TodaysDate)
AND
YEAR(c.DateKey) = YEAR(@TodaysDate)
THEN 1 ELSE 0 END
May 23, 2019 at 8:12 am
Thanks Nigel, This seems to work. What would need to be done for Previous Qtr, so the dates between 01/01/2019 to 31/03/2019 they would be set to 1 for this field
May 23, 2019 at 8:14 am
Actually I think I've got it now, I have done the following
case when DATEPART(QUARTER,c.DateKey)+1 = DATEPART(QUARTER,@TodaysDate) AND YEAR(c.DateKey) = YEAR(@TodaysDate) THEN 1 ELSE 0 END
May 23, 2019 at 8:38 am
Ah, but that won't work if the current quarter is Quarter 1 (due to the year comparison)
Looking into alternatives...
May 23, 2019 at 9:01 am
Try doing something with this :
DATEDIFF(MONTH,0,c.DateKey)/3 - DATEDIFF(MONTH,0,@TodaysDate)/3
May 23, 2019 at 9:29 am
Hi Nigel, No it does not work either
May 23, 2019 at 9:31 am
This was the query I made if that is what you meant.
case when (DATEDIFF(MONTH,0,c.DateKey)/3 - DATEDIFF(MONTH,0,@TodaysDate)/3 = DATEPART(QUARTER,@TodaysDate)) THEN 1 ELSE 0 END
May 23, 2019 at 9:43 am
The calc I suggested will give you an offset in quarters from the 'DateKey' , so:
0 = current quarter
-1 = Previous quarter
1=Next
etc.
(Hint: try it in a select statement just to see what it does)
May 23, 2019 at 3:14 pm
You're overlooking the obvious. There is a QUARTER date part. This becomes simple if you use that.
CASE WHEN DATEDIFF(QUARTER, c.DATEKEY, @TodaysDate) = 0 THEN 1 ELSE 0 END AS CurrentQuarter,
CASE WHEN DATEDIFF(QUARTER, c.DATEKEY, @TodaysDate) = 1 THEN 1 ELSE 0 END AS PrevQuarter
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 24, 2019 at 9:03 am
Ah, yes of course. Doh! 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply