Customized date range in sql

Question

We have got a customized quarter list from client which we need to take reference to get data in SQL. Could you please help.

30-Oct  29-Jan AS Quarter1
30-Jan  30-Apr AS Quarter2
01-May  30-Jul AS Quarter3
31-Jul  29-Oct AS Quarter4

If today falls between any of the quarter we need start date in date format for example if today is Jan/02/2018 then it shall return start date as 30-10-2017 00:00:00 and end date as 29-01-2018 00:00:00 if today is Dec/30/2017 then it shall return start date as 30-10-2017 00:00:00 and end date as 29-01-2018 00:00:00

Answer 1

Assumption:

• some batch work (either as part of a SQL batch, or perhaps a user-defined function) is acceptable (otherwise the batch code could be rolled into a couple additional CTEs)

A batch/CTE solution:

declare @mydate  datetime,

        @mymon   int,
        @myday   int,
        @myyr    int,

        @startyr char(4),
        @endyr   char(4)

select  @mydate = '01/02/2018'

-- break @mydate into numeric components to make it a little easier in the
-- next step to determine our start/end years

select  @mymon = month(@mydate), 
        @myday = day(@mydate),
        @myyr  = year(@mydate)

-- determine our start/end years based on current year of @mydate; since we'll
-- be feeding these variables into a convert(datetime,<string>) construct, go
-- ahead and convert to char(4) here in order to minimize coding later

select  @startyr = convert(char(4), case when @mymon = 10 and @myday >= 30 then @myyr
                                         when @mymon in (11,12)            then @myyr
                                                                           else @myyr - 1
                                    end),
        @endyr   = convert(char(4), case when @mymon = 10 and @myday >= 30 then @myyr + 1
                                         when @mymon in (11,12)            then @myyr + 1
                                                                           else @myyr
                                    end);

-- use a CTE to build our list of quarters, using the @starty/@endyr variables
-- to create the datetime ranges that make up the quarters in which 
-- @mydate date can fall within

with
quarters as

(select *
 from   (values ('Quarter1', convert(datetime,'10/30/'+@startyr), convert(datetime,'01/29/'+@endyr) ),
                ('Quarter2', convert(datetime,'01/30/'+@endyr),   convert(datetime,'04/30/'+@endyr) ),
                ('Quarter3', convert(datetime,'05/01/'+@endyr),   convert(datetime,'07/30/'+@endyr) ),
                ('Quarter4', convert(datetime,'07/31/'+@endyr),   convert(datetime,'10/29/'+@endyr) ) ) as q(qtr, qstart, qend)
)

-- now it's just a matter of finding which datetime range (aka quarter)
-- @mydate falls between

select q.qtr, q.qstart, q.qend

from   quarters q
where  @mydate between q.qstart and q.qend;

Some sample runs (changing the value of @mydate):

-- @mydate = '10/29/2017'

 qtr      | qstart              | qend               
 -------- | ------------------- | -------------------
 Quarter4 | 07/31/2017 00:00:00 | 29/10/2017 00:00:00

-- @mydate = '10/30/2017'

 qtr      | qstart              | qend               
 -------- | ------------------- | -------------------
 Quarter1 | 30/10/2017 00:00:00 | 29/01/2018 00:00:00

-- @mydate = '08/17/2016'

 qtr      | qstart              | qend               
 -------- | ------------------- | -------------------
 Quarter4 | 31/07/2016 00:00:00 | 29/10/2016 00:00:00

-- @mydate = '01/23/2016'

 qtr      | qstart              | qend               
 -------- | ------------------- | -------------------
 Quarter1 | 30/10/2015 00:00:00 | 29/01/2016 00:00:00

Here's a dbfiddle

Answer 2

I'm not sure why this question is poorly received, what you want is something like this.. Here I use PostgreSQL which is a free database with convenient row syntax.

SELECT m,d,
  CASE
    WHEN (m,d) BETWEEN (1,1)   AND (1,29)   THEN 'Quarter1'
    WHEN (m,d) BETWEEN (1,30)  AND (4,30)   THEN 'Quarter2'
    WHEN (m,d) BETWEEN (5,1)   AND (7,30)   THEN 'Quarter3'
    WHEN (m,d) BETWEEN (7,31)  AND (10,29)  THEN 'Quarter4'
    WHEN (m,d) BETWEEN (10,29) AND (12,31)  THEN 'Quarter1'
  END AS q

FROM generate_series(timestamp '1-1-2016', timestamp '1-1-2017', '1 day')
  AS gs(dt)
CROSS JOIN LATERAL ( VALUES
  ( EXTRACT(month FROM gs.dt)::smallint, EXTRACT(day FROM gs.dt)::smallint )
)
  AS t(m,d);

You can put that lengthy case statement in a SQL function to make things a bit nicer.

CREATE FUNCTION my_quarter( m smallint, d smallint )
RETURNS smallint
AS $$
  SELECT CASE
        WHEN (m,d) BETWEEN (1,1)   AND (1,29)   THEN 1::smallint
        WHEN (m,d) BETWEEN (1,30)  AND (4,30)   THEN 2::smallint
        WHEN (m,d) BETWEEN (5,1)   AND (7,30)   THEN 3::smallint
        WHEN (m,d) BETWEEN (7,31)  AND (10,29)  THEN 4::smallint
        WHEN (m,d) BETWEEN (10,29) AND (12,31)  THEN 1::smallint
  END;
$$ LANGUAGE 'sql'
IMMUTABLE;

Now it's simple..

SELECT m,d, 'Quarter' || my_quarter(m,d) AS q
FROM generate_series(timestamp '1-1-2016', timestamp '1-1-2017', '1 day')
  AS gs(dt)
CROSS JOIN LATERAL ( VALUES
  ( EXTRACT(month FROM gs.dt)::smallint, EXTRACT(day FROM gs.dt)::smallint )
)
  AS t(m,d);

All that stuff that may look a bit foreign, all it does is generates date-sequence (test data), and extract from that sequence the month and the day.

Output looks like this,

 m  | d  |    q     
----+----+----------
  1 |  6 | Quarter1
  1 | 15 | Quarter1
  1 | 25 | Quarter1
  3 | 29 | Quarter2
  4 | 12 | Quarter2
  4 | 15 | Quarter2
  5 | 22 | Quarter3
  6 | 11 | Quarter3
  6 | 18 | Quarter3
  6 | 28 | Quarter3
  7 |  3 | Quarter3
  9 |  2 | Quarter4
  9 | 22 | Quarter4
 10 | 17 | Quarter4
 10 | 25 | Quarter4
(15 rows)

Microsoft's syntax is usually a little bit longer, and less polished.