?
Solved

Averaging day of week for past 4 weeks in SSRS

Posted on 2009-04-22
32
Medium Priority
?
915 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:xtrout
  • 13
  • 11
  • 5
  • +1
32 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24207359
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

0
 
LVL 43

Expert Comment

by:pcelba
ID: 24207659
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

0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24207900
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 43

Expert Comment

by:pcelba
ID: 24208052
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.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24208076
@pcelba, good point, you may be right.  Maybe @xtrout can clarify it for us.
0
 

Author Comment

by:xtrout
ID: 24208472
Correct pcelba, it is the past 4 Wednesday ( or whatever the day of the week is) not including today.
0
 

Author Comment

by:xtrout
ID: 24208552
@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.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24208572
he was missing the table alias on the inner query
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24208651
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

0
 

Author Comment

by:xtrout
ID: 24208676
Yup that was it. Unfortunately the query returned 0 results.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24208807
Do you have some data for past 5 weeks?
0
 

Author Comment

by:xtrout
ID: 24208814
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.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24208832
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)
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24209128
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

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24209293
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

0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24209487
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
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24211729
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

0
 

Author Comment

by:xtrout
ID: 24229947
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
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24230373
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

0
 

Author Comment

by:xtrout
ID: 24244650
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
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24245047
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

0
 

Author Comment

by:xtrout
ID: 24245265
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.
0
 

Author Comment

by:xtrout
ID: 24245371
..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.
0
 

Author Comment

by:xtrout
ID: 24245458
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
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24245539
>>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.
0
 

Author Comment

by:xtrout
ID: 24245715
oh ok. thanks. I appreciate your time doing this.
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 1600 total points
ID: 24246455
ok, you introduced the new column for StartHour which was not getting summed up per day before.  I have modified the query to sum FruitCount per Day and you get one row back per day with the DaySum and the 4 week average of same day of week before it.
You get back data that looks like this:

DECLARE @SelectedFruit VARCHAR(20)
SET @SelectedFruit = 'Apple'
--SELECT SUM(CONVERT(FLOAT,FruitCount)), convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2))) 
--FROM dbo.FruitData b 
--WHERE FruitName = @SelectedFruit 
--AND DATEPART(dw,'2009-03-29 00:00:00.000') = DATEPART(dw,convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2))))
--AND convert(datetime,'2009-03-29 00:00:00.000') > convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2)))
--GROUP BY convert(datetime, cast(StartYear as varchar(4)) + '/' + cast(StartMonth as varchar(2)) + '/' + cast(StartDay as varchar(2)))
--COMPUTE AVG(SUM(CONVERT(FLOAT,FruitCount)))
 
;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,
			SUM(FruitCount) FruitCount
	from FruitData
	WHERE FruitName = @SelectedFruit
	GROUP BY FruitName, 
			StartYear,
			StartMonth,
			StartDay
)
SELECT DISTINCT a.FruitName,
	   a.StartYear,
	   a.StartMonth,
	   a.StartDay,
	   a.FruitDate,
	   a.FruitCount DaySum,
	   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)
--WHERE a.FruitDate = '2009-03-29 00:00:00.000'
ORDER BY a.FruitName, a.FruitDate

Open in new window

FruitDataOutput.png
0
 

Author Closing Comment

by:xtrout
ID: 31573376
Awsome work. it worked perfectly.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24246697
Glad it worked.  Thanks for the feedback.
0
 

Author Comment

by:xtrout
ID: 24300060
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?
0
 

Author Comment

by:xtrout
ID: 24300301
Doh, nevermind...i figured it out.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24300433
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question