Return a row with columns for each day in a date range

Given in a record in from a Table called WorkSchedule:

idWorkSchedul StartDate EndDate HoursWorked
1 1/1/2000 1/1/2006 8

I need to return for each record in the WorkSchedule Table

1/1/2000 1/2/2000 1/3/2000 1/4/2000..........1/1/2006

8 8 8 8..................8

Please help.

Thank you.

-Robert

Hi Robert,

Let's assume your source table was called "WS":

with Hours (MinDate, MaxDate, WorkDate, WorkHrs)

AS

(

SELECT StartDate as [MinDt], EndDate AS [MaxDt], StartDate AS [WorkDt], HoursWorked

FROM WS

WHERE idWorkSchedul = 1

UNIONALL

SELECT MinDate, MaxDate,DATEADD(day,1, WorkDate)AS [WorkDate], WorkHrs

FROM Hours h

WHERE WorkDate <= MaxDate

)

select*from Hours

This will I think give you a few ideas anyway (you can pivot the resultset if indeed you needed the resultset to mimic the example output you supplied). Also note that we need to return the MaxDt and MinDt so we can limit the recursive function via the WHERE WorkDate <= MaxDate clause as a recursive CTE will not allow a sub query in the where clause.

Cheers,

Rob

|||

is the employee column needed

|||

hi,

Sql server has a limitation of 1024 columns

your requirements exceeds that limitations

regards

joey

here's a tests script. its not finished becaused i encountered the limitation

use northwind
create table dates
(
dateid int identity(1,1),
date1 datetime
)
go

declare @.mydate datetime
select @.mydate ='1/1/2000'
while @.mydate<>'1/31/2010'
Begin
insert dates(date1) values ( @.mydate)
select @.mydate=dateadd(day,1,@.mydate)
end
go

select * from dates
go

create table worksched(
idWorkSchedul int identity(1,1),
StartDate datetime,
EndDate datetime,
HoursWorked int
)
insert worksched(startdate,enddate,hoursworked)
values( '1/1/2000','1/1/2006',8)

declare @.startdate datetime
declare @.enddate datetime
select @.startdate='1/1/2000'
select @.enddate='1/1/2006'
select IDENTITY(int, 1,1) AS ID_Num,
date1 INTO #MYTEMP from dates where date1
between @.startdate and @.enddate

--drop table mytest
CREATE TABLE MYTEST1
(EMPLOYEE_ID VARCHAR(10)
)

DECLARE @.CMD nVARCHAR(200)
DECLARE @.CTR INT
DECLARE @.NAME VARCHAR(10)
SELECT @.CTR=0
WHILE @.CTR<>(SELECT MAX (ID_NUM) FROM #MYTEMP)
BEGIN
SELECT @.CTR=@.CTR+1
SELECT @.NAME = CONVERT( VARCHAR(10), DATE1 ,110) FROM #MYTEMP WHERE ID_NUM=@.CTR
select @.cmd ='ALTER TABLE MYTEST1 ADD ['+ @.NAME +'] INT'
--select @.cmd
exec sp_executesql @.cmd
END

select * from mytest1

|||

Here's an idea that may be of use, though I wouldn't really call it 'rows and columns', it's more of a play-with-strings for display purposes only. Each date will not be a separate column, it's just one long formatted string for the specific purpose.

Using the following example, to generate the days in the range is pretty straight forward with a number table.

create table #workSched
( id int not null, StartDate datetime not null, EndDate datetime not null, hrs int not null )

insert #workSched
select 1, '20060101', '20060331', 8 union all
select 2, '20060401', '20060831', 8

Assuming we have these two rows, then this query would produce a 'normal' resultset for each day between start and end
(the 'n - 1' is due to my numberstable starts with one, not zero)
Also, it's necessary to do this one workid at a time, it won't work for all in one go with just a straight query. However, it may be possible to package the idea into a UDF to get a simulation of a 'single-pass' (though performance may still be an issue)

select id,
dateadd(day, n -1, startDate) as workDay,
hrs
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1

We could use this and build two strings, one with days and the other with the hours, keeping formatting in mind so that the two would be spaced accordingly.

declare @.workDay varchar(8000), @.hrs varchar(8000)
select @.workDay = '', @.hrs = ''

-- build the 'row' of dates
select @.workDay = @.workDay + convert(char(10), dateadd(day, n -1, startDate), 121) + ' '
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1

-- buld the 'row' of hours, evenly spaced according to date
select @.hrs = @.hrs + convert(char(10), hrs) + ' '
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1

-- display
select @.workDay
union all
select @.hrs

-- ....
2006-01-01 2006-01-02 2006-01-03 ....
8 8 8 ....

If you're looking for something for display or reporting use, then perhaps this idea could work for you..?
(it's not that pretty, but it works.. =;o)

/Kenneth

|||You can do the pivoting on the client side easily especially since you may have large number of date values. If you are building a report then it is a very trivial operation. So send the data as rows (dates as rows) and pivot on the client side. Solutions in TSQL will require dynamic SQL or fixed column names and other procedural techniques which will slow in terms of performance.