Home > Archive > SQL Server Programming > November 2005 > Grouping by daterange
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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!
| |
| Stijn Verrept 2005-11-28, 7:05 pm |
| 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.
>
| |
| Stijn Verrept 2005-11-29, 7:05 pm |
| 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.
| |
| Chris2 2005-11-30, 4:01 am |
|
"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.
|
|
|
|
|