Grouping by daterange

Okay. I have this dataset, looks like this:
propnum rundate genregroup totalcharges
-- --- -- --
16 2005-05-27 00:00:00.000 A 131.8800
16 2005-05-27 00:00:00.000 L 239.7600
16 2005-05-27 00:00:00.000 T 27.9800
16 2005-05-27 00:00:00.000 W 21.9900
16 2005-05-28 00:00:00.000 A 373.6600
16 2005-05-28 00:00:00.000 L 159.8400
.....
16 2005-08-01 00:00:00.000 T 41.9700
16 2005-08-02 00:00:00.000 W 21.9900
16 2005-08-02 00:00:00.000 A 461.5800
What I'm looking to do, is do a count on all the items by w. I'm
currently doing it by day, so it looks something like this...
2005-11-28 00:00:00.000 191
2005-11-27 00:00:00.000 463
2005-11-26 00:00:00.000 471
2005-11-25 00:00:00.000 474
2005-11-24 00:00:00.000 466
2005-11-23 00:00:00.000 453
2005-11-22 00:00:00.000 433
2005-11-21 00:00:00.000 453
so I have a count of the items purchaced by day. I'd like to figure how to
do it by w, so it would look something like this:
2005-11-06 - 2005-11-12 191
2005-11-13 - 2005-11-19 310
2005-11-20 - 2005-11-26 440
2005-11-27 - 2005-12-03 101
2005-12-04 - 2005-12-10 96
2005-12-11 - 2005-12-17 317
...is there a way to do this using SQL?
Thanks in advance for the info!Casey wrote:

> ...is there a way to do this using SQL?
Of course ;)
Select min(dateColumn) as BeginDate, max(dateColumn) as EndDate,
count(*) as Total from Table
group by Year(dateColumn), datepart(w, dateColumn)
The year(dateColumn) is needed when your data spans more than 1 year so
best to include it.
HTH,
Stijn Verrept.|||WOW! That worked great. Except the group only seems to be in 6 day groups,
not the expected 7... here's my query...
SELECT TOP 10 min(rundate) as beginDate, max(rundate) as EndDate,
count(*) as Total
FROM dbo.SiteDay
where rundate between '09/04/2005' and '11/30/2005'
group by Year(rundate), datepart(w, rundate)
order by beginDate
...and here's the result...
beginDate EndDate Total
-- -- --
2005-09-04 00:00:00.000 2005-09-10 00:00:00.000 3191
2005-09-11 00:00:00.000 2005-09-17 00:00:00.000 3221
2005-09-18 00:00:00.000 2005-09-24 00:00:00.000 3196
2005-09-25 00:00:00.000 2005-10-01 00:00:00.000 3235
2005-10-02 00:00:00.000 2005-10-08 00:00:00.000 3195
2005-10-09 00:00:00.000 2005-10-15 00:00:00.000 3220
2005-10-16 00:00:00.000 2005-10-22 00:00:00.000 3228
2005-10-23 00:00:00.000 2005-10-29 00:00:00.000 3249
2005-10-30 00:00:00.000 2005-11-05 00:00:00.000 3285
2005-11-06 00:00:00.000 2005-11-12 00:00:00.000 3270
(10 row(s) affected)
...any idea why this might be?
"Stijn Verrept" wrote:

> Casey wrote:
>
> Of course ;)
> Select min(dateColumn) as BeginDate, max(dateColumn) as EndDate,
> count(*) as Total from Table
> group by Year(dateColumn), datepart(w, dateColumn)
> The year(dateColumn) is needed when your data spans more than 1 year so
> best to include it.
> --
> HTH,
> Stijn Verrept.
>|||Casey wrote:

> WOW! That worked great. Except the group only seems to be in 6 day
> groups, not the expected 7... here's my query...

> ...and here's the result...
> beginDate EndDate Total
> -- -- --
> 2005-09-04 00:00:00.000 2005-09-10 00:00:00.000 3191
> 2005-09-11 00:00:00.000 2005-09-17 00:00:00.000 3221
> 2005-09-18 00:00:00.000 2005-09-24 00:00:00.000 3196
> 2005-09-25 00:00:00.000 2005-10-01 00:00:00.000 3235
> 2005-10-02 00:00:00.000 2005-10-08 00:00:00.000 3195
> 2005-10-09 00:00:00.000 2005-10-15 00:00:00.000 3220
> 2005-10-16 00:00:00.000 2005-10-22 00:00:00.000 3228
> 2005-10-23 00:00:00.000 2005-10-29 00:00:00.000 3249
> 2005-10-30 00:00:00.000 2005-11-05 00:00:00.000 3285
> 2005-11-06 00:00:00.000 2005-11-12 00:00:00.000 3270
What do you mean?
From 2005-09-04 till 2005-09-10 is 7 days. If you want the next day
included as well you should do:
SELECT TOP 10 min(rundate) as beginDate, max(rundate) + 1 as
EndDate,
count(*) as Total
FROM dbo.SiteDay
where rundate between '09/04/2005' and '11/30/2005'
group by Year(rundate), datepart(w, rundate)
order by beginDate
Kind regards,
Stijn Verrept.|||"Casey" <Casey@.discussions.microsoft.com> wrote in message
news:8425A074-5F7D-471C-9909-254B3B621158@.microsoft.com...
> WOW! That worked great. Except the group only seems to be in 6 day
groups,
> not the expected 7... here's my query...
<snip>

> ...and here's the result...
> beginDate EndDate Total
> -- -- --
> 2005-09-04 00:00:00.000 2005-09-10 00:00:00.000 3191
> 2005-09-11 00:00:00.000 2005-09-17 00:00:00.000 3221
<snip>

> 2005-10-30 00:00:00.000 2005-11-05 00:00:00.000 3285
> 2005-11-06 00:00:00.000 2005-11-12 00:00:00.000 3270
> (10 row(s) affected)
> ...any idea why this might be?
>
Casey,
Your own sample desired results shows the same thing:
2005-11-06 - 2005-11-12 191
<snip>
2005-12-11 - 2005-12-17 317
The periods noted are 7 days in both cases.
Sincerely,
Chris O.sql