AVG per week for Last 52 Full Weeks

Question

AVG per week for Last 52 Full Weeks

sharonsql2013

SSCertifiable

Points: 5908
More actions
May 28, 2015 at 3:19 pm

#308524

I am trying to get the avg of Tickets closed in last 52 Full weeks.
However , The query I have does not filter for full weeks and I get numbers for current week too.
How can I take care of that?
Select CONCAT(YEAR ,'-',Week)YearWeek
,AVGTickets From
(
Select
DATEPART(YYYY,Opened)Year
,DATEPART(WK,Opened)Week
,ROUND(AVG(CAST (Tickets AS FLOAT)),2)AVGTickets
From Dbo.Tickets
Where Opened > = DATEADD(WK, -52, getdate())
Group By DATEPART(YYYY,Opened),DATEPART(WK,Opened))A
Order BY Year Desc , Week Desc

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

sharonsql2013 (5/28/2015)
I am trying to get the avg of Tickets closed in last 52 Full weeks.
However , The query I have does not filter for full weeks and I get numbers for current week too.
How can I take care of that?
Select CONCAT(YEAR ,'-',Week)YearWeek
,AVGTickets From
(
Select
DATEPART(YYYY,Opened)Year
,DATEPART(WK,Opened)Week
,ROUND(AVG(CAST (Tickets AS FLOAT)),2)AVGTickets
From Dbo.Tickets
Where Opened > = DATEADD(WK, -52, getdate())
Group By DATEPART(YYYY,Opened),DATEPART(WK,Opened))A
Order BY Year Desc , Week Desc

First, your WHERE clause is open ended. You aren't limiting it to a specific date. To answer more fully we would need to know what the date range should be based on running the query today.

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 2

52 weeks from current week. If its the middle of the week then 52 weeks from past week So, The starting date will be based of current date

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

sharonsql2013 (5/28/2015)
52 weeks from current week. If its the middle of the week then 52 weeks from past week So, The starting date will be based of current date

Okay, what does that mean. What is the starting date and what is the ending date if the query is run today (2015-05-28 15:52:00.000).

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 4

If its today it should show data from last week to last week - 52 weeks

Because we are in the middle of the week.. Hope I am not confusing

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

sharonsql2013 (5/28/2015)
If its today it should show data from last week to last week - 52 weeks
Because we are in the middle of the week.. Hope I am not confusing

Still not answering the question. What is the Opened >= start date (i.e. 2014-05-29) and the Opened < end date (i.e 2015-05-28).

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

There is still some that is not clear. Assuming your weeks start on Sunday at midnight, does this mean that you would want a calculation run today to start with 20140525 at midnight, and stop at 20150524 at midnight?

If so, then expressions like the ones below could be used:

SELECT

Last52FullWeekStart=dateadd(week,datediff(week,@@datefirst-1,getdate())-52,@@datefirst-1),

Last52FullWeekEnd=dateadd(week,datediff(week,@@datefirst-1,getdate()),@@datefirst-1)

It works by getting the difference in weeks between now and the first day in 1900 that matches the first day of week being used by the WEEK datepart function (something that can be changed with SET DATEFIRST), since you're using that for grouping.

In other words, if your weeks start with Sunday, it gets the difference in weeks between the first Sunday of 1900, and adds that difference minus 52 weeks to get the starting datetime, and adds that difference to get the ending datetime.

Using @@DATEFIRST like that makes it a little more robust, in case you're using a different day for the first day of the week than the US English default of Sunday (i.e., SET DATEFIRST 7).

Cheers!

EDIT: Actually, after I wrote this my brain started working again, and I remembered that DATEDIFF always uses Sundays to determine the difference in weeks, no matter what DATEFIRST is set to.

That means relying on DATEDIFF in WEEKs as above will sometimes include the ending partial week anyway when DATEFIRST is not 7. To get around that, we just have to get the difference in days and use integer math (divide by integer 7 and multiply by integer 7) to get days until the end of the last full week, as below:

SELECT

Last52FullWeekStart=dateadd(day,((datediff(DAY,@@datefirst-1,getdate())/7)-52)*7,@@datefirst-1),

Last52FullWeekEnd=dateadd(day,(datediff(DAY,@@datefirst-1,getdate())/7)*7,@@datefirst-1)

One of these days I'll remember everything and not make such silly mistakes (I know it's not true, but that's what I tell myself). Until then... :blush:

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Jacob Wilkins (5/28/2015)
There is still some that is not clear. Assuming your weeks start on Sunday at midnight, does this mean that you would want a calculation run today to start with 20140525 at midnight, and stop at 20150524 at midnight?
If so, then expressions like the ones below could be used:
SELECT
Last52FullWeekStart=dateadd(week,datediff(week,@@datefirst-1,getdate())-52,@@datefirst-1),
Last52FullWeekEnd=dateadd(week,datediff(week,@@datefirst-1,getdate()),@@datefirst-1)
It works by getting the difference in weeks between now and the first day in 1900 that matches the first day of week being used by the WEEK datepart function (something that can be changed with SET DATEFIRST), since you're using that for grouping.
In other words, if your weeks start with Sunday, it gets the difference in weeks between the first Sunday of 1900, and adds that difference minus 52 weeks to get the starting datetime, and adds that difference to get the ending datetime.
Using @@DATEFIRST like that makes it a little more robust, in case you're using a different day for the first day of the week than the US English default of Sunday (i.e., SET DATEFIRST 7).
Cheers!
EDIT: Actually, after I wrote this my brain started working again, and I remembered that DATEDIFF always uses Sundays to determine the difference in weeks, no matter what DATEFIRST is set to.
That means relying on DATEDIFF in WEEKs as above will sometimes include the ending partial week anyway when DATEFIRST is not 7. To get around that, we just have to get the difference in days and use integer math (divide by integer 7 and multiply by integer 7) to get days until the end of the last full week, as below:
SELECT
Last52FullWeekStart=dateadd(day,((datediff(DAY,@@datefirst-1,getdate())/7)-52)*7,@@datefirst-1),
Last52FullWeekEnd=dateadd(day,(datediff(DAY,@@datefirst-1,getdate())/7)*7,@@datefirst-1)
One of these days I'll remember everything and not make such silly mistakes (I know it's not true, but that's what I tell myself). Until then... :blush:

If you think you're embarrassed about "silly mistakes", then I'm totally humiliated by what your good post just reminded me of. I had totally forgotten about how DATEDIFF handles weeks differently than weeks and days of the week are handled by things like DATENAME and DATEPART. I think that part of the reason I forgot is because I had gotten into the habit of doing the days/7*7 thing but still no excuse for forgetting especially since my very first article on this site relied heavily on DATEDIFF(wk,,).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8