1

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

  • I believe this SO question should have your answer. Use two CASE statements to return the start and end dates of your custom date ranges if the value falls between. – LowlyDBA Oct 6 '17 at 16:28
  • but the date and month part is static and year is dynamic, that is where i am stuck. – aman6496 Oct 6 '17 at 16:31
  • @aman6496 Then return the month/day from your static list and return the year value from today's date. Shame you're not on 2012 to use DATEFROMPARTS to make that super simple. – LowlyDBA Oct 6 '17 at 16:42
  • 2
    I usually use a Calendar table to perform these lookups. The one I have is based upon this one by Aaron Bertrand. You can customize the table to contain the date formats you want to use and assign your own quarters to each date. A quick and simple lookup. – Mr.Brownstone Oct 6 '17 at 17:59
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

-3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.