Partition over weeks in SQL Server 2005

Question

I have in table with two columns with dates. One date represent expected date and other one date when job is finished. I want to make query to get how many jobs were finished including specific week in current year and how many jobs should be finished to that week.

So if I transform my dates in weeks in those two colums as:

wkx:7,7,7,8,8,9,10,10,10,10,11,11
wkf:7,8,10,10,12,13,14,15,16,17,18,19

response should be smth like:

wk:7,8,9,10,11,12,13,14,15,16,17,18,19
numx:3,5,6,10,12,12,12,12,12,12,12,12,12
numf:1,2,2,4,4,5,6,7,8,9,10,11,12

I am using SQL Server 2005, and have no idea even how to get distinct weeks in one column as is in example.

Regards

score 0 · Accepted Answer · edited May 23 '17 at 11:43

The easiest way (?) might be to use an appropriate source of number to generate the interval of weeks and then do a conditional aggregation based of week number. The system table master..spt_values has a column that can be used for the week numbers and the week number for the expected and finished dates can be extracted using datepart(wk, expected) but that can give some odd results so I would recommend using the dbo.f_isoweek() function from this excellent answer.

The function:

CREATE function f_isoweek(@date datetime)
RETURNS INT
AS
BEGIN
  RETURN (datepart(DY, datediff(d, 0, @date) / 7 * 7 + 3)+6) / 7
END

And the query would look like this:

select 
    week = number,     
    numx = sum(case when dbo.f_isoweek(expected) = number then 1 else 0 end),
    numf = sum(case when dbo.f_isoweek(finished) = number then 1 else 0 end),   
from master..spt_values, your_table 
where type='p' -- the type for the numbers in spt_values
  -- limit to current year
  and datepart(year, expected) = datepart(year, getdate()) 
  and datepart(year, finished) = datepart(year, getdate())
group by number 
-- limit weeks to available range (could be replaced with number between 1 and 53)
having number >= dbo.f_isoweek(min(expected)) and number <= dbo.f_isoweek(max(finished))

Given an input table like:

expected    finished
2015-01-05  2015-01-16
2015-01-06  2015-01-07
2015-01-18  2015-01-20
2015-01-25  2015-01-26
2015-01-26  2015-01-27
2015-02-08  2015-02-10
2015-02-10  2015-02-16

The output would be:

week    numx    numf
2       2       1
3       1       1
4       1       1
5       1       2
6       1       0
7       1       1
8       0       1

Partition over weeks in SQL Server 2005

1 Answers1