day-of-week/frequency analysis in SQL?

[Fat Tone]

I have a table on SQL server which is essentially an event log. One field contains a datetime stamp, and there are currently ~8000 entries, spread across half a year.

I'd like to analyse the events in various ways, such as how many per week, average each each weekday, average each hour etc. Presumably there's some standard SP for this somewhere, but I can't find one as yet.

Any ideas welcome. I've done the weekly count already as a report, but I'd like to see something more versatile, and ideally be able to show the results on a .asp page in a variety of ways.

TIA

T.

[Sasq]

too tired to think up sql, though it would help the others if you can tell us which sql server it is, all have quirks and shortcuts for different functions.

[Fat Tone]

Cheers Dan. It is an IIS6 front end to SQL Server 2000.

[uberlad]

How overengineered do you want it?

I recently had to do a graph of hours logged in half-day periods against defects logged in that time. I'll dig out the code - I think with modification you could get

DAY 1 2 3 4....

01-01-2006 0 1 3 0

etc etc, from which you could break it down by hours, days, weeks, whatever.

Uberlad

[VJ]

Does SQL Server allow grouping on e.g. WEEKDAY(date-of-entry) ?
(Oracle 9 didn't allow this, Oracle 10 does allow it)

If so, for the number per week, you just need to count with a group by-clause, grouping on the weeknumber of each date of entry. If it spans multiple years, bear in mind to also group on year as well.

Similarly, for the averages per weekday, you just need to perform a count, grouped by weekday(date-of-entry).


Jörg

[uberlad]

DATEPART(dw, <date>)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2mic.asp

Uberlad

[cjolley]

Does SQL Server allow grouping on e.g. WEEKDAY(date-of-entry) ?
(Oracle 9 didn't allow this, Oracle 10 does allow it)

Jörg

eh?

What's wrong with this?
By week and then by month.
just change the second argument to truncate the date
none = day
'ww' = week
'mm' = month

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Feb 27 10:44:16 2006

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL> @connect chajol@tax_test
Enter password:
Connected.
chajol@tax.experior>select trunc(created_date, 'ww'), count(*)
  2	from tax.batch_lines
  3   where created_date between sysdate - 180  and sysdate
  4   group by trunc(created_date, 'ww')
  5   order by 1	;

TRUNC(CRE   COUNT(*)
--------- ----------
27-AUG-05		210
03-SEP-05		419
10-SEP-05		677
17-SEP-05		406
24-SEP-05		469
01-OCT-05		627
08-OCT-05		288
15-OCT-05		238
22-OCT-05	   2115
24-DEC-05		 42

10 rows selected.

Elapsed: 00:00:00.63


chajol@tax.experior>ed
Wrote file afiedt.buf

  1  select trunc(created_date, 'mm'), count(*)
  2	from tax.batch_lines
  3   where created_date between sysdate - 180  and sysdate
  4   group by trunc(created_date, 'mm')
  5*  order by 1
chajol@tax.experior>/

TRUNC(CRE   COUNT(*)
--------- ----------
01-AUG-05		 53
01-SEP-05	   2128
01-OCT-05	   3268
01-DEC-05		 42

Elapsed: 00:00:00.78

and that's in 8.1.7

[cjolley]

and one that doesn't use a group by at all...

chajol@tax.experior>select distinct trunc(created_date, 'ww') week,
2 count(*) over (partition by trunc(created_date, 'ww')) cnt
3 from tax.batch_lines
4 where created_date between sysdate - 180 and sysdate
5 order by 1 ;

WEEK CNT
--------- ----------
27-AUG-05 210
03-SEP-05 419
10-SEP-05 677
17-SEP-05 406
24-SEP-05 469
01-OCT-05 627
08-OCT-05 288
15-OCT-05 238
22-OCT-05 2115
24-DEC-05 42

10 rows selected.

Elapsed: 00:00:00.34

[cjolley]

Fat Tone
"convert" might help too: http://msdn.microsoft.com/library/de...ca-co_2f3o.asp

What you may be looking for is any of these types of functiions where you can send in the size of truncation you want as an argument to your query.
Then your users can just pick with radio buttons or check boxes or something.

so my oracle example would become

select trunc(date_col, :ar_how_wide_to_group_by), count(*)
from etc.

Then you send in whatever string you want to control how things are split up.

Is that what you are talking about?

[Fat Tone]

Thanks guys, that points me in the right direction for modifying what I already have.

I've seen server stats that break down access by week, day, hour of day etc and show it in graphical form. Was hoping there'd be something off-the-(free)shelf I could tweak to show all that without too much effort.

[Fat Tone]

Seems you Oracle guys have it easy handling dates. This page neatly summarizes what I've spent the last 2 hours figuring out for myself: http://weblogs.asp.net/cszurgot/arch...01/273285.aspx

[Fat Tone]

So the final version of the SP looks like this:

ALTER PROCEDURE dbo.weeklyactivities

AS

SELECT Week = dateadd(dd,datediff(dd, 0, DateAdd(day, -1 * datepart(dw, datecompleted), datecompleted)),0), count(*) as total_records

FROM tblactivities

group by dateadd(dd,datediff(dd, 0, DateAdd(day, -1 * datepart(dw, datecompleted), datecompleted)),0)

order by dateadd(dd,datediff(dd, 0, DateAdd(day, -1 * datepart(dw, datecompleted), datecompleted)),0) DESC


RETURN

This bit: DateAdd(day, -1 * datepart(dw, datecompleted), datecompleted)) modifies the date to all be on the first day of that week.

The rest is just to set the time to midnight so that all values with that date can be counted together.

If I want to do an hourly analysis that, perversly, is somewhat easier!

[cjolley]

Cool!

Can you hide that mess in a function in sqlserver?

eg. (using some Oracle syntax)

function trunc_to_week(ar_date date) return date
is

return dateadd(dd,datediff(dd, 0, DateAdd(day, -1 * datepart(dw, ar_date), ar_date)),0);

end;


then you could

select = trunc_to_week(datecompleted), count(*) as total_records
from tblactivities
group by trunc_to_week(datecompleted)
order by 1 asc;

You might be able to use it in other places too.

[Fat Tone]

I guess I could, and certainly will if I want to do any more of this. It is hardly light-reading in its current format!

I've used it twice, modding was simple enough while fresh in the mind.

Cheers,

T.

[cjolley]

Infact, the trunc_to_week() function would be deterministic.
So (again, in oracle) you could create an index on it.

Might be usefull if you have a lot of records.
Your query would not even have to look at the table, just the index.