Date Problem - Week of the year

[DelSenato]

I have a requirement to produce a report which pulls data from a table based on the completed date. The report has to count the number of records completed per week and give a total.

At present I am using the query below (SqlServer 2000)
This will allow me to group the data base on the week of the year but means that I have a list of numbers from 1 to 52 rather than the start date/ end date of the week.

I know I could write a hard coded function to convert numbers in range 1..52 to a start end date but it would hardly be an elegant solution.

Anyone got any tips on a more practical long term solution? (Go Reebo BoB

select
requirement_id,
datepart(ww,requirement_complete_date) CompleteddWeekofYear,
'Closed' as type,
requirement_added_date,
requirement_complete_Date ,
requirement_cancelled_date,
requirement_type
from
requirement
where
datepart(yy,requirement_complete_Date) = 2003 and
requirement_type = 'Production' and
requirement_complete_date is not null

 

[tonykblen]

Hi DelSenato,

One way that I could think of is a cross-tab in Crystal. I do something similar for sales figures here. If you go to Insert-->Cross-Tab and use the various options, you should be able to do this.

You can set the group option to show either beginning or end date for the particular time period (i.e. month,week,quarter). In order to show beginning and end date, you may need to do a custom cross-tab. An FAQ is available on this in one of the Crystal forums.

To illustrate the result of my one (which is extremely simple)


Week Ending Category1Sales Category2Sales

2/8/03 100 100
9/8/03 200 200
16/8/03 300 300

Hope this helps,

Tony.

Tony Kennedy BSc. B.I.S.,
MCSA Cand.

A good start is half the work.
Every start is difficult .
-Two Gaelic proverbs