Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Ideas for Query with calendar table

Author  Topic  vbugarsk Starting Member 1 Post Posted - 2009-06-22 : 15:07:50 I have table ResourceRequirement, which has three columns (TaskID int, Date datetime, Hours int) I also have Calendar table with (Date datetime, IsWorkDay bit) What I would like to have is sproc/function which will have following parameters: @TaskID int, @EndDate datetime, @Hours int Basically, function should insert hours for each day in ResourceRequirement table, based on 8-hours working time, but taking into account only working days. For example: Function(1000, '20090622', 20) should insert following records into ResourceRequirement table: TaskID Date Hours 1000 2009-06-22 8 1000 2009-06-19 8 1000 2009-16-18 4 since 2009-06-21 and 2009-06-20 are not working days (have IsWorkDay=0 in Calendar table). Any idea, maybe with recursive queries ??