asked on

Averaging day of week for past 4 weeks in SSRS

Hello experts,

I am trying to find a way to add a trendline to a chart in SQL/SSRS 2005 that shows the average for the same day for the past four weeks. Basically if today is Wednesday, then the plot point on the trendline for today would be the average of the past four Wednesdays. To make matters even more difficult is that I have integers for the day of the week, not a datetime field.

Anyone have any ideas or experience with this? attached is a mockup of the data i am using

FruitName StartYear StartMonth StartDay FruitSum
Apples	2009	3	1	49606
Apples	2009	3	2	1422836
Apples	2009	3	3	1549013
Apples	2009	3	4	1522078
Apples	2009	3	5	1525877
Apples	2009	3	6	1293827
Apples	2009	3	7	238913
Apples	2009	3	8	46805
Apples	2009	3	9	1293937
Apples	2009	3	10	1385616
Apples	2009	3	11	1718285
Apples	2009	3	12	1726526
Apples	2009	3	13	1380438
Apples	2009	3	14	226932
Apples	2009	3	15	50729
Apples	2009	3	16	1807873
Apples	2009	3	17	1386265
Apples	2009	3	18	1682727
Apples	2009	3	19	1314206
Apples	2009	3	20	1040648
Apples	2009	3	21	191573
Apples	2009	3	22	51034
Apples	2009	3	23	1735910
Apples	2009	3	24	1781176
Apples	2009	3	25	1743700
Apples	2009	3	26	1690839
Apples	2009	3	27	698834
Apples	2009	3	28	96139
Apples	2009	3	29	24316
Apples	2009	3	30	856188
Apples	2009	3	31	836201
Oranges	2009	3	1	11426
Oranges	2009	3	2	195107
Oranges	2009	3	3	213292
Oranges	2009	3	4	204481
Oranges	2009	3	5	267885
Oranges	2009	3	6	244490
Oranges	2009	3	7	14952
Oranges	2009	3	8	12845
Oranges	2009	3	9	231101
Oranges	2009	3	10	187125
Oranges	2009	3	11	252747
Oranges	2009	3	12	262454
Oranges	2009	3	13	218064
Oranges	2009	3	14	16495
Oranges	2009	3	15	12845
Oranges	2009	3	16	241238
Oranges	2009	3	17	180471
Oranges	2009	3	18	298332
Oranges	2009	3	19	367662
Oranges	2009	3	20	207925
Oranges	2009	3	21	26427
Oranges	2009	3	22	19269
Oranges	2009	3	23	280617
Oranges	2009	3	24	281794
Oranges	2009	3	25	292831
Oranges	2009	3	26	288242
Oranges	2009	3	27	217965
Oranges	2009	3	28	24176
Oranges	2009	3	29	18656
Oranges	2009	3	30	237767
Oranges	2009	3	31	238557

Open in new window

ralmada

What about something like this?

Select d.FruitName, 
       avg(d.FruitSum) 
from (
	select 	d.FruitName, 
		convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2))) as fruitdate,
		d.FruitSum
	from yourtable
) d
where d.fruitdate in (getdate(), getdate() - 7, getdate() - 14, getdate() - 21)
group by d.FruitName

Open in new window

Pavel Celba

This looks perfect, I would change the WHERE part and extend it to all week days:

Select d.FruitName, datepart(dw, fruitdate) DayOfWeek
       avg(d.FruitSum) 
from (
	select 	d.FruitName, 
		convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2))) as fruitdate,
		d.FruitSum
	from yourtable
) d
where d.fruitdate 
  BETWEEN 
    convert(datetime, convert(char(8), getdate()-7, 112), 112)
  AND
    convert(datetime, convert(char(8), getdate()-34, 112), 112)
group by d.FruitName, datepart(dw, fruitdate)

Open in new window

Chris Luttrell

This script worked in my sample script giving this output, will that work for you?
FruitName      DayOfWeek      (No column name)
Apples      1      24316
Oranges      1      18656
Apples      2      856188
Oranges      2      237767
Apples      3      836201
Oranges      3      238557
Apples      5      1690839
Oranges      5      288242
Apples      6      698834
Oranges      6      217965
Apples      7      96139
Oranges      7      24176


Select d.FruitName, datepart(dw, fruitdate) DayOfWeek,
       avg(d.FruitSum) 
from (
	select 	d.FruitName, 
		convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2))) as fruitdate,
		d.FruitSum
	from FruitData d
) d
where d.fruitdate > DATEADD(d,-28,getdate())
group by d.FruitName, datepart(dw, fruitdate)

Open in new window

FruitData.txt

Pavel Celba

The Question is: "Basically if today is Wednesday, then the plot point on the trendline for today would be the average of the past four Wednesdays."

I would say the avearge should not contain the current week (last 7 days) but all days from four previous weeks. The request is to calculate 4 weeks moving average and add it as a new column to existing data to allow some plots.

Chris Luttrell

@pcelba, good point, you may be right. Maybe @xtrout can clarify it for us.

xtrout

ASKER

Correct pcelba, it is the past 4 Wednesday ( or whatever the day of the week is) not including today.

xtrout

ASKER

@pcelba, your looks right but I get the errors

The multi-part identifier "d.FruitName" could not be bound.
The multi-part identifier "d.FruitSum" could not be bound.

Chris Luttrell

he was missing the table alias on the inner query

Pavel Celba

Sorry in the innermost select is local alias which should be removed:

Select d.FruitName, datepart(dw, d.fruitdate) DayOfWeek
       avg(d.FruitSum) 
from (
	select 	FruitName, 
		convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2))) as fruitdate,
		FruitSum
	from yourtable
) d
where d.fruitdate 
  BETWEEN 
    convert(datetime, convert(char(8), getdate()-7, 112), 112)
  AND
    convert(datetime, convert(char(8), getdate()-34, 112), 112)
group by d.FruitName, datepart(dw, d.fruitdate)

Open in new window

xtrout

ASKER

Yup that was it. Unfortunately the query returned 0 results.

Pavel Celba

Do you have some data for past 5 weeks?

xtrout

ASKER

I think it may have something to do with the WHERE date range. I used....

(testdatetime < DATEADD(d, - 7, GETDATE())) AND (testdatetime > DATEADD(d, - 34, GETDATE()))

and it looks like it works.

Pavel Celba

Of course, the BETWEEN arguments must be in reverse order ...

where d.fruitdate
BETWEEN
convert(datetime, convert(char(8), getdate()-34, 112), 112)
AND
convert(datetime, convert(char(8), getdate()-7, 112), 112)

Chris Luttrell

You can use the "OVER ( PARTITON BY )" in a statement like below to put your average on the same line of output like this:
FruitName      StartYear      StartMonth      StartDay      FruitSum      FourWeekAvg
Apples      2009      3      8      46805      49606
Apples      2009      3      9      1293937      1422836
Apples      2009      3      10      1385616      1549013
Apples      2009      3      11      1718285      1522078
Apples      2009      3      12      1726526      1525877
Apples      2009      3      13      1380438      1293827
Apples      2009      3      14      226932      238913
Apples      2009      3      15      50729      48205
Apples      2009      3      15      50729      48205
Apples      2009      3      16      1807873      1358386
Apples      2009      3      16      1807873      1358386
Apples      2009      3      17      1386265      1467314
Apples      2009      3      17      1386265      1467314
Apples      2009      3      18      1682727      1620181
Apples      2009      3      18      1682727      1620181
Apples      2009      3      19      1314206      1626201
Apples      2009      3      19      1314206      1626201
Apples      2009      3      20      1040648      1337132
Apples      2009      3      20      1040648      1337132
Apples      2009      3      21      191573      232922
Apples      2009      3      21      191573      232922
Apples      2009      3      22      51034      49046
Apples      2009      3      22      51034      49046
Apples      2009      3      22      51034      49046
Apples      2009      3      23      1735910      1508215
Apples      2009      3      23      1735910      1508215
Apples      2009      3      23      1735910      1508215
Apples      2009      3      24      1781176      1440298
Apples      2009      3      24      1781176      1440298
Apples      2009      3      24      1781176      1440298
Apples      2009      3      25      1743700      1641030
Apples      2009      3      25      1743700      1641030
Apples      2009      3      25      1743700      1641030
Apples      2009      3      26      1690839      1522203
Apples      2009      3      26      1690839      1522203
Apples      2009      3      26      1690839      1522203
Apples      2009      3      27      698834      1238304
Apples      2009      3      27      698834      1238304
Apples      2009      3      27      698834      1238304
Apples      2009      3      28      96139      219139
Apples      2009      3      28      96139      219139
Apples      2009      3      28      96139      219139
Apples      2009      3      29      24316      49543
Apples      2009      3      29      24316      49543
Apples      2009      3      29      24316      49543
Apples      2009      3      29      24316      49543
Apples      2009      3      30      856188      1565139
Apples      2009      3      30      856188      1565139
Apples      2009      3      30      856188      1565139
Apples      2009      3      30      856188      1565139
Apples      2009      3      31      836201      1525517
Apples      2009      3      31      836201      1525517
Apples      2009      3      31      836201      1525517
Apples      2009      3      31      836201      1525517
Oranges      2009      3      8      12845      11426
Oranges      2009      3      9      231101      195107
Oranges      2009      3      10      187125      213292
Oranges      2009      3      11      252747      204481
Oranges      2009      3      12      262454      267885
Oranges      2009      3      13      218064      244490
Oranges      2009      3      14      16495      14952
Oranges      2009      3      15      12845      12135
Oranges      2009      3      15      12845      12135
Oranges      2009      3      16      241238      213104
Oranges      2009      3      16      241238      213104
Oranges      2009      3      17      180471      200208
Oranges      2009      3      17      180471      200208
Oranges      2009      3      18      298332      228614
Oranges      2009      3      18      298332      228614
Oranges      2009      3      19      367662      265169
Oranges      2009      3      19      367662      265169
Oranges      2009      3      20      207925      231277
Oranges      2009      3      20      207925      231277
Oranges      2009      3      21      26427      15723
Oranges      2009      3      21      26427      15723
Oranges      2009      3      22      19269      12372
Oranges      2009      3      22      19269      12372
Oranges      2009      3      22      19269      12372
Oranges      2009      3      23      280617      222482
Oranges      2009      3      23      280617      222482
Oranges      2009      3      23      280617      222482
Oranges      2009      3      24      281794      193629
Oranges      2009      3      24      281794      193629
Oranges      2009      3      24      281794      193629
Oranges      2009      3      25      292831      251853
Oranges      2009      3      25      292831      251853
Oranges      2009      3      25      292831      251853
Oranges      2009      3      26      288242      299333
Oranges      2009      3      26      288242      299333
Oranges      2009      3      26      288242      299333
Oranges      2009      3      27      217965      223493
Oranges      2009      3      27      217965      223493
Oranges      2009      3      27      217965      223493
Oranges      2009      3      28      24176      19291
Oranges      2009      3      28      24176      19291
Oranges      2009      3      28      24176      19291
Oranges      2009      3      29      18656      14096
Oranges      2009      3      29      18656      14096
Oranges      2009      3      29      18656      14096
Oranges      2009      3      29      18656      14096
Oranges      2009      3      30      237767      237015
Oranges      2009      3      30      237767      237015
Oranges      2009      3      30      237767      237015
Oranges      2009      3      30      237767      237015
Oranges      2009      3      31      238557      215670
Oranges      2009      3      31      238557      215670
Oranges      2009      3      31      238557      215670
Oranges      2009      3      31      238557      215670

SELECT a.FruitName,
	   a.StartYear,
	   a.StartMonth,
	   a.StartDay,
	   a.FruitSum,
	   avg(b.FruitSum) OVER (PARTITION BY a.FruitName, a.StartYear, a.StartMonth, a.StartDay) FourWeekAvg
FROM FruitData a
INNER JOIN FruitData b ON a.FruitName = b.FruitName AND 
	convert(datetime, cast(b.StartYear as varchar(4)) + '/' + cast(b.StartMonth as varchar(2)) + '/' + cast(b.StartDay as varchar(2)))
	BETWEEN convert(datetime, cast(a.StartYear as varchar(4)) + '/' + cast(a.StartMonth as varchar(2)) + '/' + cast(a.StartDay as varchar(2))) - 29
	AND convert(datetime, cast(a.StartYear as varchar(4)) + '/' + cast(a.StartMonth as varchar(2)) + '/' + cast(a.StartDay as varchar(2))) -1
	AND DATEPART(dw,convert(datetime, cast(a.StartYear as varchar(4)) + '/' + cast(a.StartMonth as varchar(2)) + '/' + cast(a.StartDay as varchar(2)))) =
	DATEPART(dw,convert(datetime, cast(b.StartYear as varchar(4)) + '/' + cast(b.StartMonth as varchar(2)) + '/' + cast(b.StartDay as varchar(2))))

Open in new window

ralmada

I would still make sure that you remove the time from the current date.

Select d.FruitName, datepart(dw, d.fruitdate) as dofweek, 
       avg(d.FruitSum) 
from (
	select 	FruitName, 
		convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2))) as fruitdate,
		FruitSum
	from yourtable
) d
where d.fruitdate <= dateadd(d, -7, cast(convert(varchar(8), getdate(), 112) as datetime)
and d.fruitdate >= dateadd(d, -34, cast(convert(varchar(8), getdate(), 112) as datetime)
group by d.FruitName, datepart(dw, d.fruitdate)

Open in new window

Chris Luttrell

My example never uses any date values excepte those built from the data which do not have time portions. I did not use getdate() because that limited the range of what data you could work with to Today. If you want to look at todays value from my query add:

Where a.StartYear = 2009 and a.StartMonth=4 and a.StartDay=22
And a.FruitName = 'Oranges' -- if desired

which means you could parameterize this and run it for any day and any fruit

ralmada

If you're calculating moving avg from today then my comment 24209293 should do it. If you are looking for CGLuttrell approach, I would just modify it like this:

;with CTE as (
	select 	FruitName, 
			convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2))) as fruitdate,
			FruitSum
	from yourtable
)
Select d.FruitName, datepart(dw, d.fruitdate) as dofweek, 
       avg(d.FruitSum) 
from CTE d
where d.fruitdate <= dateadd(d, -7, (select max(fruitdate) from CTE where CTE.FruitName = d.FruitName))
and d.fruitdate >= dateadd(d, -34, (select max(fruitdate) from CTE where CTE.FruitName = d.FruitName))
group by d.FruitName, datepart(dw, d.fruitdate)

Open in new window

xtrout

ASKER

Hi Guys, it is kind of getting close. I attached a spreadsheet so you can see in Column F what the results would look like.

The code you provided is close but i end up with errors, not sure if its syntax or what.
example.xls

Chris Luttrell

This one works for me on the data in the spread sheet. I thought I would go ahead and see if it worked for you.
I dislike having to use the Distinct and will still try to come up with a solution without that. I always feel that sticking a Distinct in is a crutch for not getting the query right in the first place.

DECLARE @SelectedFruit VARCHAR(20)
SET @SelectedFruit = 'Apples'
SELECT * FROM dbo.FruitData b WHERE FruitName = @SelectedFruit
 
;with CTE as (
	select 	FruitName, 
			StartYear,
			StartMonth,
			StartDay,
			convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2))) as fruitdate,
			FruitCount 
	from FruitData
	WHERE FruitName = @SelectedFruit
)
SELECT DISTINCT a.FruitName,
	   a.StartYear,
	   a.StartMonth,
	   a.StartDay,
	   a.FruitCount,
	   CASE WHEN (SELECT COUNT(*) FROM CTE c WHERE a.FruitName = c.FruitName AND DATEADD(week,-4,a.FruitDate) = c.FruitDate) > 0 THEN  
	   ROUND(avg(CONVERT(FLOAT,b.FruitCount)) OVER (PARTITION BY a.FruitName, a.StartYear, a.StartMonth, a.StartDay, a.FruitCount, a.FruitDate),0) 
	   ELSE 0 END FourWeekAvg
FROM CTE a
LEFT OUTER JOIN CTE b ON a.FruitName = b.FruitName 
AND b.fruitdate BETWEEN DATEADD(week,-4,a.fruitdate) AND DATEADD(week,-1,a.fruitdate)
AND DATEPART(dw,a.fruitdate) = DATEPART(dw,b.fruitdate)

Open in new window

xtrout

ASKER

This is like 98% of the way there. the last part I need to fix is that the first3 lines of your query ( i think ).

I need bout the regular sum and the 4 week averages in separate columns. In the chart, the regular daily sum would be a column and the 4 week average would be the trendline.

I tried changing the first part to ...

DECLARE @SelectedFruit VARCHAR(20)
SET @SelectedFruit = 'Apples'
SELECT fruitname, startyear,startmonth, startday, sum(fruitcount) as fruitsum, avg(fruitcount) as fruit avg
FROM dbo.FruitData b WHERE FruitName = @SelectedFruit
Group by fruitname, startyear,startmonth, startday

But to no avail

Chris Luttrell

Try this, I added the column to get the sum along with the avg.

DECLARE @SelectedFruit VARCHAR(20)
SET @SelectedFruit = 'Apples'
SELECT * FROM dbo.FruitData b WHERE FruitName = @SelectedFruit
 
;with CTE as (
	select 	FruitName, 
			StartYear,
			StartMonth,
			StartDay,
			convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2))) as fruitdate,
			FruitCount 
	from FruitData
	WHERE FruitName = @SelectedFruit
)
SELECT DISTINCT a.FruitName,
	   a.StartYear,
	   a.StartMonth,
	   a.StartDay,
	   a.FruitCount,
	   CASE WHEN (SELECT COUNT(*) FROM CTE c WHERE a.FruitName = c.FruitName AND DATEADD(week,-4,a.FruitDate) = c.FruitDate) > 0 THEN  
	   ROUND(avg(CONVERT(FLOAT,b.FruitCount)) OVER (PARTITION BY a.FruitName, a.StartYear, a.StartMonth, a.StartDay, a.FruitCount, a.FruitDate),0) 
	   ELSE 0 END FourWeekAvg,
	   SUM(b.FruitCount) OVER (PARTITION BY a.FruitName, a.StartYear, a.StartMonth, a.StartDay, a.FruitCount, a.FruitDate) FourWeekSum
FROM CTE a
LEFT OUTER JOIN CTE b ON a.FruitName = b.FruitName 
AND b.fruitdate BETWEEN DATEADD(week,-4,a.fruitdate) AND DATEADD(week,-1,a.fruitdate)
AND DATEPART(dw,a.fruitdate) = DATEPART(dw,b.fruitdate)

Open in new window

xtrout

ASKER

Thanks. It still only returns the one column. I need one for actual count and one for the 4 week average (like in the spreadsheet). I also can't use SELECT * in the first part because there are columns I don't want to see and I need to group. I appreciate your help so far though.

xtrout

ASKER

..oh and the sum would be a daily sum, not a four week sum if that makes a difference. only the average would be the four week by the given day.

xtrout

ASKER

I think I may have originally posted a poor example of the table. Attached is a better example of the data I see in the table.
table-example.xls

Chris Luttrell

>>I also can't use SELECT * in the first part because there are columns I don't want to see and I need to group. I appreciate your help so far though.
Sorry, that was just a line I had in my script to show all the raw data in the table, not meant to be part of the solution.
I am loading your latest data in my test table and will get back to you.
If I am understanding correctly, you are wanting the daily sum (over all the hours that day) on each line along with the average of that weekday over the past 4 weeks.
Let me take a look and see what I can do.

xtrout

ASKER

oh ok. thanks. I appreciate your time doing this.

ASKER CERTIFIED SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xtrout

ASKER

Awsome work. it worked perfectly.

Chris Luttrell

Glad it worked. Thanks for the feedback.

xtrout

ASKER

Well, I found I needed to do an hourly version of the report where say on 4/25 at 11am, I need the average of the past 4 same days AND same hours. I adjust the the conversions of datetime to convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2)) + ' ' + cast(StartHour as varchar(2)) + ':00:00') as fruitdate as well as adding StartHour to the various select statments thinking that it would just split it up using the same methodology.

CG, could direct me on that one as well?

xtrout

ASKER

Doh, nevermind...i figured it out.

Chris Luttrell

cool, I was just starting to look at it. If you do have other related questions use the "ask a related question" link in the top of this Discussion box and I will get notification on it.