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