William,

If I understand your results correctly what you want to do is something
like this:

SELECT
DATEADD( dd, -1 * DATEPART( dw, Date ), Date ),
Explanation,
SUM( TotalCount )
FROM
DSL_History
WHERE
Date BETWEEN '3/1/03' AND '6/6/03'
GROUP BY
DATEADD( dd, -1 * DATEPART( dw, Date ), Date ),
Explanation,


Regards,

Chuck Fox

nnnnnn wrote:
> search google for "characteristic encoded functions" - WARNING: they are
> not very intuitive
>
>
> "William" <william@williamcole.org> wrote in message
> news:2975e5a1.0306300739.47ec0253@posting.google.c om...
>
>>I am trying to write a query that will capture data for a particular
>>date range and then group the data by week to be graphed. The script
>>below works well now but it will not be correct starting in January
>>because I am adding the number of weeks for the date. So in January
>>the dates in December will be around 50 so when I add that to the
>>first Monday of the year it will come back with December of 2004
>>instead of December 2003. I can not think of any way around this,
>>does anyone have a better solution? Any help would be great...
>>
>>Query:
>>
>>--Get date of first monday of the year
>>DECLARE @startDate datetime
>>select @startDate = '1/1/2003'
>>
>>while (select DATENAME(dw, @startDate)) <> "Monday"
>>begin
>> select @startDate = (dateadd(day, 1, @startDate))
>>end
>>
>>--Find out what week the date in the Date column is and
>>--add it to the first Monday of the year to get the date
>>--of that week
>>select
>> dateadd(wk,datepart(wk, Date),@startDate),
>> Explanation,
>> sum(TotalCount)
>>from
>> DSL_OrderHist
>>where
>> Date between '3/1/03' and '6/6/03'
>>group by
>> datepart(wk, Date),
>> Explanation
>>
>>Sample of data returned:
>>3/10/03 Awaiting Disconnect 33
>>3/10/03 Cancel Completed 24
>>3/10/03 Disconnect Acknowledged Received 576
>>3/10/03 Disconnect Completed 64
>>3/17/03 Awaiting Disconnect 356
>>3/17/03 Cancel Completed 786
>>3/17/03 Disconnect Acknowledged Received 554
>>3/17/03 Disconnect Completed 3324
>>3/24/03 Awaiting Disconnect 56
>>3/24/03 Cancel Completed 874
>>3/24/03 Disconnect Acknowledged Received 343
>>3/24/03 Disconnect Completed 3567
>
>
>