CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
MDMalik
MDMalik

Reputation: 4001

SQL Group By Day Range

I have a table OutletVisit which have a column DTOEntry which is in DateTime format.

Table Structure 

DTOEntry  |  OutletID 
18-01-2016|     1     
18-01-2016|     2    
18-01-2016|     3     
18-01-2016|     4     
27-01-2016|     1     
27-01-2016|     2    
27-01-2016|     3     
29-01-2016|     4

Now what output do I desire is basically group by From Thursday[Inclusive] to Wednesday

From      |  To        |  OutletID |  ActualDate 
14-01-2016| 20-01-2016 |   1       |  18-01-2016
14-01-2016| 20-01-2016 |   2       |  18-01-2016
14-01-2016| 20-01-2016 |   3       |  18-01-2016
14-01-2016| 20-01-2016 |   4       |  18-01-2016
21-01-2016| 27-01-2016 |   1       |  27-01-2016
21-01-2016| 27-01-2016 |   2       |  27-01-2016
21-01-2016| 27-01-2016 |   3       |  27-01-2016
28-01-2016| 03-02-2016 |   4       |  29-01-2016

How to write a dynamic SQL for this.
Thanks in advance

Upvotes: 1

Views: 178

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35623

It is not clear why you want "dynamic sql", but you can calculate the week starting each Thursday using date functions.

  • datediff(day,fromdate,todate) returns the number of days as an integer
  • datediff(day,-4,DTOEntry) is the number of days from a Thursday in Dec 1899
  • divide that number by 7 (nb. this is an integer result)
  • multiply that number by 7
  • add that number to -4 to arrive at the date for the week starting a Thursday

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE OutletVisit 
    ([DTOEntry] datetime, [OutletID] int)
;

INSERT INTO OutletVisit 
    ([DTOEntry], [OutletID])
VALUES
    ('2016-01-18 00:00:00', 1),
    ('2016-01-18 00:00:00', 2),
    ('2016-01-18 00:00:00', 3),
    ('2016-01-18 00:00:00', 4),
    ('2016-01-27 00:00:00', 1),
    ('2016-01-27 00:00:00', 2),
    ('2016-01-27 00:00:00', 3),
    ('2016-01-29 00:00:00', 4)
;

Query 1:

SELECT
      ca.weekstart
    , DATEADD(DAY, 6, ca.weekstart) AS weekfinish
    , ov.*
FROM OutletVisit ov
      CROSS APPLY (
            SELECT
                  DATEADD(DAY, (DATEDIFF(DAY, -4, DTOEntry) / 7) * 7, -4) AS weekstart
      ) AS ca

An alternative that could be used, using a "derived table" (or "subquery"):

SELECT
      weekstart
    , DATEADD(DAY, 6, weekstart) AS weekfinish
    , DTOEntry
    , OutletID
FROM (
      SELECT
            DATEADD(DAY, (DATEDIFF(DAY, -4, DTOEntry) / 7) * 7, -4) AS weekstart
          , ov.DTOEntry
          , ov.OutletID
      FROM OutletVisit ov
      ) AS derived
;

Results:

|                 weekstart |                 weekfinish |                  DTOEntry | OutletID |
|---------------------------|----------------------------|---------------------------|----------|
| January, 14 2016 00:00:00 |  January, 20 2016 00:00:00 | January, 18 2016 00:00:00 |        1 |
| January, 14 2016 00:00:00 |  January, 20 2016 00:00:00 | January, 18 2016 00:00:00 |        2 |
| January, 14 2016 00:00:00 |  January, 20 2016 00:00:00 | January, 18 2016 00:00:00 |        3 |
| January, 14 2016 00:00:00 |  January, 20 2016 00:00:00 | January, 18 2016 00:00:00 |        4 |
| January, 21 2016 00:00:00 |  January, 27 2016 00:00:00 | January, 27 2016 00:00:00 |        1 |
| January, 21 2016 00:00:00 |  January, 27 2016 00:00:00 | January, 27 2016 00:00:00 |        2 |
| January, 21 2016 00:00:00 |  January, 27 2016 00:00:00 | January, 27 2016 00:00:00 |        3 |
| January, 28 2016 00:00:00 | February, 03 2016 00:00:00 | January, 29 2016 00:00:00 |        4 |

Note to control the display of datetime values at output use FORMAT() or CONVERT(). Sqlfiddle's display defaults have been used in the result above.

Please also note that really a week starts at (say) January, 14 2016 00:00:00 and finishes at January, 21 2016 00:00:00, a duration of 7 days. The column "weekfinishes" uses a duration of 6 days to match your expected result.

Upvotes: 3

Related Questions