Week ending average in sql

Transact-SQL
1

How can I use sql to get average per week ending day.
(From Sunday to Saturday is one week)This will go on for an entire year. I could do a CTE any ideas

Date, value
01/01/2018,10
01/02/2018,10
01/03/2018,11
01/04/2018,12
01/05/2018,11
01/06/2018,12
01/07/2018,13
01/08/2018,100
01/09/2018,120
01/10/2018,140
01/11/2018,120
01/12/2018,120
01/13/2018,100
01/14/2018,100

Results

Weekending,value
01/06/2018,11
01/13/2018,101.85

2

please always provide data in the following format

SET DATEFIRST 7 ;  --Sunday, it is default but just in case

create table #stylez(style_date date, boonjooma int)


insert into #stylez
select '01/01/2018',10 union
select '01/02/2018',10 union
select '01/03/2018',11 union
select '01/04/2018',12 union
select '01/05/2018',11 union
select '01/06/2018',12 union
select '01/07/2018',13 union
select '01/08/2018',100 union 
select '01/09/2018',120 union 
select '01/10/2018',140 union 
select '01/11/2018',120 union 
select '01/12/2018',120 union 
select '01/13/2018',100 union 
select '01/14/2018',100 

select DATEADD(dd, 7-(DATEPART(dw, style_date)), style_date) [WeekEnd] ,
       avg(boonjooma) 
From #stylez
group by DATEADD(dd, 7-(DATEPART(dw, style_date)), style_date)

drop table #stylez
3

Hi
Hope this helps​:slightly_smiling_face::slightly_smiling_face:

You could use

Partition by
Or
between clause

For Day of week
1=Sunday
2=Monday

and then average
do group by

4

I'd suggest never changing the DATEFIRST setting, that's just asking for problems of one kind or another. The code below works for any DATEFIRST setting.

SELECT 
    Weekending,
    CAST(SUM(value) * 1.0 / COUNT(*) AS decimal(9, 1)) AS value
FROM #stylez /* your table name goes here */
CROSS APPLY (
    SELECT DATEADD(WEEK, DATEDIFF(DAY, 5, DATEADD(DAY, 6, style_date)) / 7, 5) AS Weekending
) AS calc1
GROUP BY Weekending
ORDER BY Weekending
5

Hi Scott

Please let me know

What not changing date first means

I mean in what context whom etc

Gotcha..:grinning::grinning:
Saw yosaiz post

7

hi

i think its possible to do this using partition

I am on 2008

select avg(value) OVER(partition by datepart(week,style_date) order by style_date)
from #stylez

8

Hi

another way of doing this without partition

SQL using datepart WEEK 
SELECT  Datepart (week, style_date), 
Avg (value) 
FROM #stylez 
GROUP BY  Datepart (week, style_date)
9

hi

I was able to do it using TALLY Table ..
I guess another way of doing it :slight_smile::slight_smile:

Why did i Use N+1<=53
because i am using datepart week
and there are 53 weeks in a year

-- and DStyles said it will be for a year

Create Data Script 
USE tempdb 
go 

drop table #stylez
go 

create table #stylez(style_date date, value int)


insert into #stylez
select '01/01/2018',10 union
select '01/02/2018',10 union
select '01/03/2018',11 union
select '01/04/2018',12 union
select '01/05/2018',11 union
select '01/06/2018',12 union
select '01/07/2018',13 union
select '01/08/2018',100 union 
select '01/09/2018',120 union 
select '01/10/2018',140 union 
select '01/11/2018',120 union 
select '01/12/2018',120 union 
select '01/13/2018',100 union 
select '01/14/2018',100 
go
SQL using tally table 
;WITH tallycte 
     AS (SELECT N=1 
         UNION ALL 
         SELECT n + 1 
         FROM   tallycte 
         WHERE  n + 1 <= 53) 
SELECT n, 
       Avg(value) 
FROM   tallycte a 
       JOIN #stylez b 
         ON a.n = Datepart(week, style_date) 
GROUP  BY n 

go
Result

image