Date Quesion getting AVG by Week. Help.

[ghost2]

Hello all. I have a query where I am getting a total QTY by month/year. I need to now break it up into weeks. Is this possible? Thanks all. Here is the functions I am using for year and month.

Year([DateShipped]) AS [Year], Month([DateShipped]) AS [Month]

 

[vongrunt]

Try with datepart(ww, DateShipped) - it returns week number inside year.

 

[TJRTech]

One thing to be wary of with this approach, and that is partial weeks at year boundaries.

They are a fact of life and skew averages given that the 1st and 52nd or 53rd weeks of the year are usually NOT full weeks.

TR

 

[ghost2]

Thanks guys so much. TJRTech is there a way to work around it?

 

[donutman]

Select Floor((cast(YourDate as int)+@K)/7) as WeekNumber

The @K is the number 0 to 6 which makes your week end on the day of week that you like.
-Karl

 

[donutman]

Oops, the Floor function probably isn't needed.
-Karl

 

[TJRTech]

Donutman, I think your syntax and casting is off, and I think I know what you are trying to do, but I am not sure it is going to work.

Maybe I am over thinking this, but in the past, when doing just this sort of thing the challenge has been keeping my week numbers consistent with business week numbers, consistent with SQL datepart week numbers AND normalizing the summaries/averages in order to account for partial weeks.

Normalization is done by calculating the number of days per week for each week averaging. For week 1 and week 53, the number of days in the week are NOT always going to be 7, and actually are more often NOT going to be 7. However, in both cases, the number of days in the week can be calculate from the day of week of the date in questions. For example, 1/1/2004 (first day of this year) is a Thursday, or day of week # 5. That means there are 3 days in that week...7-5+1. The days of the week in the last week of the year is easier...its is the day of week num for that date; no calculation needed.

Consider the following:

DECLARE @dt as datetime
set @dt='12/30/2004'  -- Any old date

-- Gives first day of year
select dateadd(dy, -datepart(dy, @dt)+1, @dt)  

-- Gives first day of next year
select dateadd(year, 1, dateadd(dy, -datepart(dy, @dt)+1, @dt))  

-- Gives last day of year
select dateadd(dy, -1, dateadd(year, 1, dateadd(dy, -datepart(dy, @dt)+1, @dt)))

The code above is useful as for ANY date you can get the associated first day of the year and last day of the year.

And, for a sample, execute the following code:

DECLARE @dt as datetime

-- Loop through 1 year
set @dt='1/1/2004'
while datepart(year, @dt)=2004
  begin
	SELECT
               datepart(wk, @dt) as WEEK_NUM,              
               NUM_DAYS_IN_WEEK =
               CASE 
                  WHEN datepart(wk, @dt)=1 
                       THEN 7-datepart(dw, dateadd(dy, -datepart(dy, @dt)+1, @dt))+1
		  WHEN datepart(wk, @dt)=53 
                       THEN datepart(dw, dateadd(dy, -1, dateadd(year, 1, dateadd(dy, -datepart(dy, @dt)+1, @dt))))
                  ELSE 7
               END
	set @dt=dateadd(dd, 7, @dt)
  end

The code above should show all week numbers and the the number of days in that week.

I hope this helps.

TR

 

[donutman]

I guess that's a business decision, whether they want to report an average sale for a partial week or only report full weeks regardless if it crosses a boundry.
Most businesses have dramatically different sales for each weekday and a partial week average, imho, makes no sense even if you do it right. After all the whole point of a weekly average is to show "a level sales amount over 7-day period". That number shouldn't change for the split ends of a week at the end of a year.
I would argue for always using full weeks to compute an averate, at least in my bakery business. For total sales for the PERIOD (i.e. the sum of the weeks must equal the year's sales), then I would agree with you.
-Karl

 

[TJRTech]

Karl, yeah, I hear you.

I have worked on dozens of reporting systems and sales that are broken down by year, quarter, month, week and are difficult to "explain" to customers.

Most of my clients won't accept a Y2004W1 average sales average that is significantly less than Y2004W2 sales average. They fixate on those kinds of things, and when you say...well it is a small average because it is a short week, then some say "oh" and others say "I don't think so!".

It gets more complicated when you normalize to number of business/sales day. Same approach as above, just on steroids.

TR