CODEWITHSUNDEEP
sqlsql-server
Visionist
Visionist

Reputation: 35

SQL Server: whole weeks total in a calendar month

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

Upvotes: 2

Views: 292

Answers (5)

aclong
aclong

Reputation: 349

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

Upvotes: 0

Visionist
Visionist

Reputation: 35

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)

Upvotes: 1

Horaciux
Horaciux

Reputation: 6477

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:

Upvotes: 0

Kilren
Kilren

Reputation: 415

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.

Upvotes: 1

Mike Mirabelli
Mike Mirabelli

Reputation: 410

    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.

Upvotes: -1

Related Questions