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

William wrote:
>
> 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...

I'm not sure if I understand you correctly, but it looks to me
that you just need to group by the year and the week of the
year. Something like datepart(yy, date) and datepart(wk, date).
That would give you results like -

2003, 7
2003, 9
2003, 37
3004, 1
etc.

Is that what you want?

-am © 2003

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

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
>
>
>