CODEWITHSUNDEEP
sql-server
Swapnil Ganjegaonkar
Swapnil Ganjegaonkar

Reputation: 116

How to get weekly report with weekstart date and end date as column name in SQL Server

Data:

Date        Productivity
-------------------------
01/06/2015      50
01/06/2015      50
02/06/2015      60
02/06/2015      50
01/06/2015      55
03/06/2015      50
03/06/2015      50
03/06/2015      50
04/06/2015      50
04/06/2015      50
04/06/2015      50
05/06/2015      50
05/06/2015      50
05/06/2015      50
06/06/2015      50
06/06/2015      50
08/06/2015      50
08/06/2015      50
09/06/2015      50
10/06/2015      50
11/06/2015      50
12/06/2015      50
13/06/2015      50
13/06/2015      50
13/06/2015      50

I want output like this, which contains average of week productivity: 

Date                         Productivity
------------------------------------------
01/06/2015-06/06/2015           50.93
08/06/2015-13/06/2015            50

Upvotes: 0

Views: 43

Answers (2)

Binesh Nambiar C
Binesh Nambiar C

Reputation: 162

Lad's answer will do what you needs. But if there needs the weeks which don't have entry as Avg '0' you may have to write a cte for finding out the weeks which should left join with lad's answer

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

SqlFiddle

SELECT 
  [date]          = CONVERT(NVARCHAR(100), MIN([date]), 103 ) + '-' +
                    CONVERT(NVARCHAR(100), MAX([date]), 103 )
  ,[Productivity] = CAST(AVG(Productivity * 1.0) AS DECIMAL(10,2))
FROM tab
GROUP BY DATEPART(wk, [Date]);

EDIT:

Added calculating week start and end if data doesn't contain all days.

SqlFiddleDemo2

set datefirst 1;

SELECT 
   [date]          = CONVERT(NVARCHAR(100), DATEADD(dd, -(DATEPART(dw, MIN([date]))-1), MIN([date])), 103 ) + ' - ' +  CONVERT(NVARCHAR(100), DATEADD(dd, 7-(DATEPART(dw, MAX([date]))), MAX([date])), 103 )
   ,[Productivity] = CAST(AVG(Productivity * 1.0) AS DECIMAL(10,2))
FROM tab
GROUP BY DATEPART(wk, [Date]);

Upvotes: 2

Related Questions