user2430812 user2430812 - 3 months ago 7
SQL Question

Group into 2 week periods

I have a table (called 'transactions') that contains customer transaction data. Each row contains a customer number and the date of a transaction. I need to display the total number of transactions each customer has had in a defined 2 week period. For all customers the 2 week period starts on 22/8/2016. So I need to know how many transactions each customer has had in a rolling 2 week period from 22/8/2106 onwards.

I need the return to display the customer number, the start date of each 2 week period and the number of transactions in that 2 week period.

As an example, the sample data below:


╔══════════╦════════════╗
║ Customer ║ Date ║
╠══════════╬════════════╣
║ 1234 ║ 22/08/2016 ║
╠══════════╬════════════╣
║ 1234 ║ 23/08/2016 ║
╠══════════╬════════════╣
║ 1234 ║ 24/08/2016 ║
╠══════════╬════════════╣
║ 1234 ║ 3/09/2016 ║
╠══════════╬════════════╣
║ 1234 ║ 3/09/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 26/08/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 27/08/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 28/08/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 29/08/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 30/08/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 30/08/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 30/08/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 6/09/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 6/09/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 7/09/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 8/09/2016 ║
╠══════════╬════════════╣
║ 5678 ║ 12/09/2016 ║
╚══════════╩════════════╝


Needs to return this:



╔══════════╦═════════════════╦═══════╗
║ Customer ║ Week Start Date ║ Count ║
╠══════════╬═════════════════╬═══════╣
║ 1234 ║ 22/08/2016 ║ 5 ║
╠══════════╬═════════════════╬═══════╣
║ 5678 ║ 22/08/2016 ║ 7 ║
╠══════════╬═════════════════╬═══════╣
║ 5678 ║ 5/09/2016 ║ 5 ║
╚══════════╩═════════════════╩═══════╝

Answer

Upon close inspection, your expected results are a bit unexpected to me, because you are listing customer ids along with an aggregate of the number of customers. Typically, you would just be expecting a total number of customers for each two week period, which is what my query below should generate.

SELECT t.Customer,
       DATEADD(day, (t.twoWeekPeriod-1)*14 + 3, '2016-01-01') AS [Week Start Date],
       t.customerCount AS [Count]
FROM
(
    SELECT Customer,
           FLOOR(DATEPART(week, DATEADD(day, -3, Date)) / 2) AS twoWeekPeriod,
           COUNT(*) AS customerCount
    FROM yourTable
    WHERE Date >= '2016-08-22'
    GROUP BY Customer,
             FLOOR(DATEPART(week, DATEADD(day, -3, Date)) / 2)
) t

Explanation:

  • SQL Server begins the first "week" of the year on January 1st. Since your starting date in 22-Aug-2016, the first week (and all subsequent weeks) began on Friday. Since your starting date is a Monday, I roll back all dates by 3 days to get everything aligned.
  • Having rolled back 22-Aug-2016 by 3 days, it is now the start of the 34th week of the year. This means that week 35 should also be a part of this first two week group.
  • FLOOR(DATEPART(week, DATEADD(day, -3, Date)) / 2) groups together sets of 14 days in a single two week period

Update:

As you pointed out, the above query won't work as intended if your data spans more than one calendar year. In this case, you can form two weeks groups using the number of days alone, with 2016-08-22 (or any other date you want) serving as the first two week period:

SELECT t.Customer,
       DATEADD(day, (t.twoWeekPeriod)*14, '2016-08-22') AS [Week Start Date],
       t.customerCount AS [Count]
FROM
(
    SELECT Customer,
           FLOOR(DATEDIFF(day, '2016-08-22', Date) / 14) AS twoWeekPeriod,
           COUNT(*) AS customerCount
    FROM yourTable
    WHERE Date >= '2016-08-22'
    GROUP BY Customer,
             FLOOR(DATEDIFF(day, '2016-08-22', Date) / 14)
) t
Comments