CODEWITHSUNDEEP
sqlsql-serversql-date-functions
Andreabic
Andreabic

Reputation: 5

sql group by end of the week on Sundays

I have 3 columns in my table:

bill_date (daily data), country, revenues

How to group by country and get sum of revenues if I want to group my bill_date by the end of every week that fall on Sundays ?

To be clearer for the current month ,November 2023 I would like to get following:

I have tried with week datepart function using SQL Server Management Studio 2018 version but with no success... I cannot see dateweek function in SQL Server Management Studio 2018 ...

Upvotes: 0

Views: 269

Answers (3)

Vito Anania
Vito Anania

Reputation: 26

if it can help, in Python, (and in general the algorithm) would be:

df['week'] = (
np.ceil(
(
df['DATE'].dt.day
- 7 +
df['DATE'].dt.to_period('M').dt.start_time.dt.dayofweek
)
/7)
).astype(int) + 1

you're using the first day of each month to establish when the first week of the month ends. example: first day is Monday. you'll have week calculated as ceil((1 - 7 + 0)/7) + 1 = 0 + 1 = 1 Tuesday: ceil((2 - 7 + 0)/7) + 1 = 0 + 1 = 1 ... the first next Monday: ceil((8 - 7 + 0)/7) + 1 = 1 + 1 = 2 and so on

if the first day of the week was Sunday, you'd get ceil((1-7+6)/7) + 1 = 0 + 1 = 1 but on the second day ceil((2-7+6)/7) + 1 = 1 + 1 = 2

the offset must equal the .dt.dayofweek of the first day of the month (because Monday = 0)

Upvotes: 0

T N
T N

Reputation: 10101

You can calculate the period-ending date by separately calculating the last-day-of-the-week and last-day-of-the-month and taking the lesser of the two. This can be wrapped up in a CROSS APPLY and the result used in the GROUP BY, SELECT, and ORDER BY clauses.

SET DATEFIRST 1  -- Set Monday as first day of week

SELECT country, period_ending, SUM(revenues) AS revenues
FROM @Data
CROSS APPLY (
    SELECT CASE
        WHEN DATEADD(day, 7 - DATEPART(weekday, bill_date), CONVERT(DATE, bill_date))
             < EOMONTH(bill_date)
        THEN DATEADD(day, 7 - DATEPART(weekday, bill_date), CONVERT(DATE, bill_date))
        ELSE EOMONTH(bill_date)
        END AS period_ending
) PE
GROUP BY country, period_ending
ORDER BY country, period_ending

If using SQL Server 2022 or later, the DATETRUNC() and LEAST() functions simplify the calculations.

SET DATEFIRST 1  -- Set Monday as first day of week

SELECT country, period_ending, SUM(revenues) AS revenues
FROM @Data
CROSS APPLY (
    SELECT LEAST(
            DATEADD(day, 6, DATETRUNC(week, bill_date)),
            EOMONTH(bill_date)
        ) AS period_ending
) PE
GROUP BY country, period_ending
ORDER BY country, period_ending

The DATEBUCKET() function can also be used if you wish to avoid SET DATEFIRST.

Upvotes: 0

Kristofer
Kristofer

Reputation: 823

You will need to group your data both by month and week to get the right output.

To group by week specify the first day of week as Monday with SET DATEFIRST 1 and group the data with DATEPART(wk, bill_date).

To group by month use CONVERT(nvarchar(7), bill_date, 121) to get the first 7 characters of a the date ISO-formatted.

 -- Set first day of week to Monday
SET DATEFIRST 1;

-- Generate some data and put it into a table
DECLARE @data TABLE (bill_date date, country nvarchar(max), revenues int)

-- Generate 500 rows
;WITH r AS (SELECT top 500 ROW_NUMBER() OVER(ORDER BY object_id) as n FROM sys.all_objects)

-- Insert into a table
INSERT INTO @data
SELECT
  DATEADD(DAY, r.n, '2022-12-31'), -- Start at the beginning of 2023 and take 500 days
  country.name,
  r.n * country.factor -- use a country factor to get different values for each country
FROM r
-- Cross apply with a few countries
CROSS APPLY (VALUES (1, 'SE'), (2, 'NO'), (3, 'FI')) AS country(factor, name)

-- Select data grouped by week and month
SELECT
  country,
  MAX(bill_date) AS last_day_week,
  SUM(revenues) revenue_sum
FROM @data
GROUP BY
  DATEPART(wk, bill_date), -- Group by week
  CONVERT(nvarchar(7), bill_date, 126), -- .. and by month
  country
ORDER BY MAX(bill_date), country

Upvotes: 0

Related Questions