SQL Server: whole weeks total in a calendar month

Question

I want weekly totals in a month. It will not include any partial week or future weeks. Week starts from Monday to Sunday.

I have a table structure like

Date          Value      -- Comments
----------------------------------------------------------------------   
2016-10-01      7        Ignore this because its not a whole  week in a month
2016-10-05      8        Week 1  
2016-10-07      5        Week 1  
2016-10-11      2        Week 2  
2016-10-15      1        Week 2    
2016-10-17      9        Ignore this because the week is not finished yet

OUTPUT

 WeekNo         Total
    41             13
    42              3

For example: In the current calendar month. we will ignore the 26Sep-2Oct week and 31Oct-6Nov week because these are not the whole weeks within current month. — Visionist, Oct 17 '16 at 13:45
it would mean that values from arbitrary date ranges are omitted, even when you query data of consecutive months... an interesting puzzle, but before investing time, I'd like to question the underlying specification. is this really a valid requirement? — Cee McSharpface, Oct 17 '16 at 15:19

score 1 · Answer 1 · answered Oct 17 '16 at 15:30

The easier way would be to build a Tally "date" table. you can generate it from any Tally Table like:

DECLARE @StartDate DATE = '20160101'
  , @EndDate DATE = '20161231';

WITH cte AS (
SELECT  DATEADD(DAY, n - 1, @StartDate) AS date
FROM    tally
WHERE   n - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
)
SELECT 
    c.date 
    ,YEAR(c.date) AS Year
    ,MONTH(c.date) AS Month
    ,DAY(c.date) AS Month
    ,DATEPART(WEEK,c.date) AS Week
    ,CASE WHEN 7<>COUNT(c.date) OVER (PARTITION BY YEAR(c.date),MONTH(c.date),DATEPART(WEEK,c.date)) THEN  0 ELSE 1 END AS isFullWeek
FROM cte c

Then you just need to Join it to what ever query you need.

Visionist · Accepted Answer · 2016-10-18T08:40:36.543

DECLARE @StartDate datetime = '2011-10-01';
DECLARE @EndDate datetime = '2016-10-31';
SELECT
  CAST(DATEADD(dd, -DATEPART(dw, tblData.RecordDate) + 2, tblData.RecordDate) AS date) AS WeekStart,
  CAST(DATEADD(dd, -DATEPART(dw, tblData.RecordDate) + 8, tblData.RecordDate) AS date) AS WeekEnd,
  SUM(Value) AS Total
FROM tblData

WHERE (@StartDate IS NULL
OR CAST(DATEADD(dd, -DATEPART(dw, tblData.RecordDate) + 2, tblData.RecordDate) AS date) >= CAST(@StartDate AS date))
AND (@EndDate IS NULL
OR CAST(DATEADD(dd, -DATEPART(dw, tblData.RecordDate) + 8, tblData.RecordDate) AS date) <= CAST(@EndDate AS date))
AND CAST(DATEADD(dd, -DATEPART(dw, tblData.RecordDate) + 8, tblData.RecordDate) AS date) < CAST(GETDATE() AS date)
GROUP BY CAST(DATEADD(dd, -DATEPART(dw, tblData.RecordDate) + 2, tblData.RecordDate) AS date),
         CAST(DATEADD(dd, -DATEPART(dw, tblData.RecordDate) + 8, tblData.RecordDate) AS date)

Horaciux · Answer 3 · 2016-10-17T16:43:29.730

Create a calendar table that meets your request, like this:

    create table calendarTable ([date] date, weekNro int)
        go

    insert into  calendarTable 
    select dateadd(d,n,'20160101'), DATEPART(WK,dateadd(d,n,'20151231')) 
    from  numbers where n<500

If you don't have a Numbers Table, you must create it first. like this

SET NOCOUNT ON    
CREATE TABLE Numbers (n bigint PRIMARY KEY)    
GO    
DECLARE @numbers table(number int);  
WITH numbers(number) as  (   
SELECT 1 AS number   
UNION all   
SELECT number+1 FROM numbers WHERE number<10000  
)  
INSERT INTO @numbers(number)  
SELECT number FROM numbers OPTION(maxrecursion 10000)
INSERT INTO Numbers(n)  SELECT number FROM @numbers

Then query your table joining calendar table having in mind actual date for completed week, like this:

score 0 · Answer 4 · answered Aug 17 '21 at 10:57

Similar to @Kilren but translated into postgres and using generate series from https://stackoverflow.com/a/11391987/10087503 to generate the dates

DECLARE @StartDate DATE = '20160101'
  , @EndDate DATE = '20161231';

WITH cte AS (
SELECT i::date AS date FROM generate_series(@StartDate, 
  @EndDate, '1 day'::interval) i
)
SELECT 
    c.date 
    ,DATE_TRUNC('month' ,c.date) AS month_trunc
    ,DATE_PART('week',c.date) AS week
    ,CASE WHEN 7<>COUNT(c.date) 
    OVER (PARTITION BY DATE_TRUNC('month' ,c.date),DATE_PART('week',c.date)) 
    THEN  0 ELSE 1 END AS is_full_week
FROM cte c

Mike Mirabelli · Answer 5 · 2016-10-17T14:15:27.537

-1

    Select DATEPART(ww, date) , SUM(Case When Comments Like '%1' then Value when Comments Like '%2' then Value else Value end)

from schema.tablename

group by DATEPART(ww,date)

I'm sorry if this doesn't work, it's the only way I thought to structure it.

edited Oct 17 '16 at 14:15

answered Oct 17 '16 at 14:10

Mike Mirabelli

402
3
16

not clear how this would address the problem described in the question – Cee McSharpface Oct 17 '16 at 14:12

SQL Server: whole weeks total in a calendar month

5 Answers5

Linked