Date Calendar - Current Qtr

  • 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

    • This topic was modified 4 years, 10 months ago by  SQL_Kills.
  • Something like this?

     

    CASE WHEN 
    DATEPART(QUARTER,c.DateKey) = DATEPART(QUARTER,@TodaysDate)
    AND
    YEAR(c.DateKey) = YEAR(@TodaysDate)
    THEN 1 ELSE 0 END

    • This reply was modified 4 years, 10 months ago by  nigel..
    • This reply was modified 4 years, 10 months ago by  nigel.. Reason: Better formatting
  • 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

  • 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
  • Ah, but that won't work if the current quarter is Quarter 1 (due to the year comparison)

    Looking into alternatives...

  • Try doing  something with this :

    DATEDIFF(MONTH,0,c.DateKey)/3 - DATEDIFF(MONTH,0,@TodaysDate)/3

     

  • Hi Nigel, No it does not work either

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

  • 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

  • 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