grouping by week

[katgirl]

Hi,

I need to write a query that will go through an entire table containing data spanning several months, if not years.

What I need to do is a count of the "number of leads" per week for the given time span (months or years).

I know from having done a similar query that looks at the previous Sunday through Sunday's data that this can be complicated. Now I want to expand this query to go through a period of time, not just the previous Sunday!

Does anyone have a handy script I can use or modify to accomplish my goal???

THANKS much

 

[katgirl]

Here is an excerpt of my other sql script:

/* "strip" time component from datetime */
Set @RunDate = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

/* set @StartDate and @EndDate - Monday to Sunday of previous week*/
SELECT @EndDate = @RunDate - DatePart(Weekday, @RunDate) + 1
SELECT @StartDate = @EndDate - 6

 

[gmmastros]

Try this...

Select DateAdd(week, DateDiff(Week, 0, [!]DateField[/!]), 0),
       Count(1)
From   [!]TableName[/!]
Group By DateAdd(week, DateDiff(Week, 0, [!]DateField[/!]), 0)
Order By DateAdd(week, DateDiff(Week, 0, [!]DateField[/!]), 0)

Do a little research in Books on line regarding SET DATEFIRST because some people think Sunday is the first day of the week and others think Monday is.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[katgirl]

thanks george

once again, i need to break this down to understand how it works - the understanding lasts about 2 seconds with me and date functions

 

[gmmastros]

Let me try to explain this. It's important, so well worth it in my opinion. First, it's important to realize that SQL Server stores DateTime values as a numeric number. The whole number part represents the number of days that have elapsed since Jan 1, 1900. The fractional part represents the time (fractional part of a day). So .5 represents noon. .25 is 6 AM and .75 is 6 PM.

Let's use today's date (August 18, 2006) as an example.

DateDiff returns the difference in date/time for the 2 given values. The first argument represents the inteval for the calculations. DateDiff always returns an integer. So, doing a date diff between today and tomorrow but based on years will return 0 not 1/365.

So...

Select DateDiff([!]Year[/!], '20060816', '20060817')   -- [!]0[/!]
Select DateDiff([!]Month[/!], '20060816', '20060817')  -- [!]0[/!]
Select DateDiff([!]Week[/!], '20060816', '20060817')   -- [!]0[/!]
Select DateDiff([!]Day[/!], '20060816', '20060817')    -- [!]1[/!]
Select DateDiff([!]Hour[/!], '20060816', '20060817')   -- [!]24[/!]
Select DateDiff([!]Minute[/!], '20060816', '20060817') -- [!]1440[/!]
Select DateDiff([!]Second[/!], '20060816', '20060817') -- [!]86400[/!]

So far... no big deal. Now for the good stuff. Thinking about the grouping... if we had some really nice integer value to represent the week number already in the database, then this would be a no brainer, right? Just toss that in to the group by and you have your solution. Since you are not storing a week number (and I wouldn't recommend it either), we will have to manufacture a week number instead.

When you think about it, the week number doesn't really mean anything. It's only in comparison to 'other' weeks when the actual number has meaning. So... we can manufacture week numbers with ANY value we want, as long as the previous week has a number that is 1 less than the original week, and the next week has a number that is 1 more than the original week. Given this, the challenge is to manufacture a week number that will be consistent. So, my advice was to generate a week number for each date. To do this, we calculate the number of weeks that have expired since Jan 1, 1900. Which is exactly what the follow code sippet does.

[tt][blue]Select DateDiff(Week, 0, GetDate())[/blue][/tt]

Which is the same as...

[tt][blue]Select DateDiff(Week, '19000101', GetDate())[/blue][/tt]

For today, Aug 16, 2006, this returns 5563 which represents the number of weeks that have elapsed since Jan 1, 1900.

'19000101' represents year 1900 day 01 month 01. By using this syntax, a data conversion (from string to date) has to occur. String functions are slower than math functions, so the date is represented by 0 instead. It means the same thing but the conversion will be slightly faster.

We could ALMOST stop here because the grouping would work. Unfortunately, looking at a number like 5563 doesn't really mean anything to anybody in regards to dates. Instead, we would prefer to see a 'real' date. That's what the next part does.

DateAdd will add a value to a date and return a new date.

Select DateAdd(week, DateDiff(Week, 0, GetDate()), 0)

So... The DateDiff part returns an integer number of weeks that have elapsed since Jan 1, 1900. The DateAdd part adds that number of weeks to Jan 1, 1900.

It's easier to think of this in terms of days. Today's number is 38943. Meaning that there has been 38943 days that have elapsed since Jan 1, 1900. DateDiff returns an integer, adding that integer number of days back on to jan 1, 1900 still results in an integer. Effectively the time has been removed.

Using this logic, you could easily convert the code to accomodate years, quarters, months, weeks, days, etc....

I hope the explanation helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[katgirl]

thanks george, you are very kind to spell all this out for me

i will read it later when my head's clear!

 

[katgirl]

yes, makes sense (again) !