SQL date logic - finding prior *non-standard* quarter

user_9599625 0
Viewed 119

SQL Server 2012.

We have a business unit which uses non-standard quarterly reporting. The quarters start in November and end in October. To labour the point, Q1: Nov to Jan; Q2: Feb to Apr; Q3: May to Jul; Q4: Aug to Oct.

I'm trying to create a "simple and efficient" query that can take a given date and return the first and last dates of the previous "quarter" according to this non-standard scheme.

A former colleague had dealt with this using a convoluted case expression which is pretty unwieldy, and I would like to find a better way.

I know SQL server has some built-in date logic that deals with quarters, but only the standard calendar quarters. So, the following will reliably return the first day of the previous quarter, but just the standard scheme of quarters beginning in January and ending in December...

SELECT DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()) - 1, 0) 
--Shows first day of prior quarter, but only for 'standard' quarters

I then naively thought just subtracting two months would do it, but this is wrong 2/3 of the time

SELECT DATEADD(month, -2, DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()) - 1, 0))
--Works for 1/3 dates

Since then I've tried various nested calculation schemes and come up with nothing that works.

Can anyone help me with this calculation - or alternately would you recommend I just create a date table that lets me look up the non-standard quarter? Thanks

sql-server-2012 date sql-server sql
3 Answers

I would add two months, take the first day of the quarter, and then subtract two months:

SELECT DATEADD(month, -2,
               DATEADD(quarter,
                       DATEDIFF(quarter, 0, DATEADD(month, 2, GETDATE())
                               ) - 1, 0
                       )
              ) as Special_Quarter_Date

I would be more inclined to write this using DATEFROMPARTS():

select dateadd(month, -2,
               datefromparts( year(dateadd(month, 2, getdate()),
                              datepart(quarter, dateadd(month, 2, getdate()) * 3 - 2
                              1
                            )
               )

But the logic is exactly the same: add two months, calculate the quarter, and subtract two months.

user_11440350

You can use below query:-

    DECLARE @mydate DATETIME ='2020-04-04'
    SELECT 
     CASE
            WHEN MONTH(@mydate) BETWEEN 2  AND 4  THEN  CAST(YEAR(@mydate) - 1 as varchar)+'-11-01'
            WHEN MONTH(@mydate) BETWEEN 5  AND 7  THEN  CAST(YEAR(@mydate) as varchar)+'-02-01' 
            WHEN MONTH(@mydate) BETWEEN 8 AND 10  THEN  CAST(YEAR(@mydate) as varchar)+'-05-01'
            WHEN MONTH(@mydate) in(11,12)       THEN  CAST(YEAR(@mydate) as varchar)+'-08-01'
            WHEN MONTH(@mydate)=1     THEN  CAST(YEAR(@mydate) -1 as varchar)+'-08-01'
     END AS Start_date,
     CASE
            WHEN MONTH(@mydate) BETWEEN 2  AND 4  THEN  CAST(YEAR(@mydate) as varchar)+'-01-31'
            WHEN MONTH(@mydate) BETWEEN 5  AND 7  THEN  CAST(YEAR(@mydate) as varchar)+'-04-30' 
            WHEN MONTH(@mydate) BETWEEN 8 AND 10  THEN  CAST(YEAR(@mydate) as varchar)+'-07-31'
            WHEN MONTH(@mydate) in(11,12)       THEN  CAST(YEAR(@mydate) as varchar)+'-10-31'
            WHEN MONTH(@mydate)=1     THEN  CAST(YEAR(@mydate) -1 as varchar)+'-10-31'
      END AS End_date
user_140802180

You can use below query to determine previous quarter's first and last date as you need:

declare @date date = getdate()
select case 
            when MONTH(@date) in (11,12,1) then  datefromparts(year(@date),8,1)
            when MONTH(@date) in (8,9,10)  then  datefromparts(year(@date),5,1)
            when MONTH(@date) in (5,6,7)   then  datefromparts(year(@date),2,1)
            when MONTH(@date) in (2,3,4)   then  datefromparts(year(@date),11,1)
       end StartOfPreviousQuarter,
       case 
            when MONTH(@date) in (11,12,1) then  datefromparts(year(@date),8,30)
            when MONTH(@date) in (8,9,10)  then  datefromparts(year(@date),5,31)
            when MONTH(@date) in (5,6,7)   then  
            CASE WHEN ISDATE(CAST(year(@date) AS char(4)) + '0229') = 1 THEN  datefromparts(year(@date),2,29) ELSE datefromparts(year(@date),2,28) END
            when MONTH(@date) in (2,3,4)   then  datefromparts(year(@date),11,30)
       end EndOfPreviousQuarter
user_86516010
Related