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

Grouping (sum) query

I have the following information in a table

ACCNO Rundate TRDCAP TRANQTY DLPRCE NOTEAMNT
275479 20031202 A -17000 215000 0
275479 20031202 A -2741 215000 0
275479 20031202 A -259 215000 -42501729

I need to create a query that totals TRANQTY and arrives at a result as in the following record.

ACCNO Rundate TRDCAP TRANQTY DLPRCE NOTEAMNT
275479 20031202 A -20000 215000 -42501729

and now for the $1M question...How ? I've tried the following select, but it is not working the way I want it to..

SELECT c2.ACCNO, c2.Rundate, c2.TrdCap, c2.TRANQTY, c2.DLPRCE, c2.NOTEAMNT
FROM CLIENTSHAREDEALS c2 FULL OUTER JOIN
(SELECT c1.ACCNO, c1.SHARENAME, SUM(c1.TRANQTY) AS Expr1
FROM CLIENTSHAREDEALS c1
WHERE (c1.ACCNO = '275479')
GROUP BY c1.ACCNO, c1.RUNDATE, c1.SHARENAME) c1 ON c1.ACCNO = c2.ACCNO AND c1.RUNDATE = c2.RUNDATE
WHERE (c2.ACCNO = '275479')
ORDER BY c1.RUNDATE

ThanksOriginally posted by Odie
I have the following information in a table

ACCNO Rundate TRDCAP TRANQTY DLPRCE NOTEAMNT
275479 20031202 A -17000 215000 0
275479 20031202 A -2741 215000 0
275479 20031202 A -259 215000 -42501729

I need to create a query that totals TRANQTY and arrives at a result as in the following record.

ACCNO Rundate TRDCAP TRANQTY DLPRCE NOTEAMNT
275479 20031202 A -20000 215000 -42501729

and now for the $1M question...How ? I've tried the following select, but it is not working the way I want it to..

SELECT c2.ACCNO, c2.Rundate, c2.TrdCap, c2.TRANQTY, c2.DLPRCE, c2.NOTEAMNT
FROM CLIENTSHAREDEALS c2 FULL OUTER JOIN
(SELECT c1.ACCNO, c1.SHARENAME, SUM(c1.TRANQTY) AS Expr1
FROM CLIENTSHAREDEALS c1
WHERE (c1.ACCNO = '275479')
GROUP BY c1.ACCNO, c1.RUNDATE, c1.SHARENAME) c1 ON c1.ACCNO = c2.ACCNO AND c1.RUNDATE = c2.RUNDATE
WHERE (c2.ACCNO = '275479')
ORDER BY c1.RUNDATE

Thanks

What about this?

SELECT ACCNO, Rundate, TrdCap, sum(TRANQTY),DLPRCE, sum(NOTEAMNT) FROM CLIENTSHAREDEALS
group by ACCNO, Rundate, TrdCap,DLPRCE|||Thanks - I'll give it a shot!