June 22, 2009 at 12:59 pm
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 will have to make 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 ??
June 22, 2009 at 1:22 pm
Please have a look at Jeff's Tally table article[/url].
At the very end of the article, shortly before the conclusion paragraph, you'll find a short and efficient example of making a "shift" table with 3 shifts per day.
It's probably exactly what you need.
Btw: A Tally table has a lot more functionality than just this one,
[SET humor ON]
So everybody should purchase one!!! (Afaik Jeff is giving the Tally table def away for half price off this month, so you might want to get two - just in case!)
[SET humor OFF].
Seriously: Using the Tally table properly can and mostly will speed up code significantly.
@jeff: Do I get my Tally table for free for making adverts? 😀
June 24, 2009 at 2:56 pm
Here is my take:
SET NOCOUNT ON
--
-- Build calendar table
--
IF OBJECT_ID('tempdb..#Calendar') IS NOT NULL
DROP TABLE #Calendar
--
CREATE TABLE #Calendar (
CalendarDate datetime not null primary key,
IsWorkDate bit not null
)
--
DECLARE
@count int,
@date datetime
--
SET @count = 0
--
WHILE @count <= 365
BEGIN
SET @date = dateadd(dd, @count, '1/1/2008')
--
INSERT #Calendar (
CalendarDate,
IsWorkDate )
SELECT
@date,
CASE WHEN datepart(dw, @date) IN (1,7) THEN 0 ELSE 1 END -- Set weekends to non workdays
--
SET @count = @count + 1
END
--
-- Declare variables for calculations
--
DECLARE
@TaskID int,
@EndDate datetime,
@Hours int,
@FullWorkDay int,
@DaysToWork int,
@Remainder int
--
-- Set variables for example
--
SELECT
@TaskID = 1000,
@EndDate = '6/1/2008',
@Hours = 20,
@FullWorkDay = 8
--
-- Determine how many days must be shown (also include day for remainder hours)
--
SELECT
@DaysToWork = (@Hours + @FullWorkDay - 1) / @FullWorkDay,
@Remainder = @Hours % @FullWorkDay
--
-- Show data for testing
--
SELECT @DaysToWork, @Remainder
--
-- Use derived table to get view of all the available working days before the
-- end date. Row_number function will keep running count of the working day.
--
-- The total days required (in this example is 3: -- 20 hours = 8 + 8 + 4)
-- is matched up against the row number of the calendar days
--
-- Use case statement to determine whether to use the value of a full work day
-- or to show the hours remainder (@Hours modulo @FullWorkDay) on the last day
--
-- If there was no hours remainder, then show the full work day on the last day
--
--
SELECT
@TaskID,
CalendarDate,
CASE WHEN RowNum < @DaysToWork OR @Remainder = 0 THEN @FullWorkDay ELSE @Remainder END AS Hours
FROM (
SELECT
CalendarDate,
ROW_NUMBER() OVER (ORDER BY CalendarDate DESC) AS RowNum
FROM #Calendar
WHERE CalendarDate <= @EndDate
AND IsWorkDate = 1
) x
WHERE RowNum <= @DaysToWork
June 26, 2009 at 5:16 am
Hey 8kb,
Don't forget to include a SET DATEFIRST statement if you use DATEPART(WEEKDAY... to find weekends.
Not everyone gives the same numbers to the same days of the week.
(You can also do a calculation with @@DATEFIRST, but the SET is easier)
Looks like you put some work into that script 😎
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 26, 2009 at 2:55 pm
Excellent job !!
I've come up with something more complicated.... 🙂
If you are still "in the mood", there are two more "issues".
First is similar function which will calculate finish for given start, but this will be very easy to accomplish with your approach. Probably something like:
------
SELECT
@TaskID,
CalendarDate,
CASE WHEN RowNum = @StartDate
AND IsWorkDate = 1
) x
WHERE RowNum @EndDate.
Of course we can first check if
@DaysToWork > SELECT COUNT(CalendarDate) FROM #Calendar WHERE CalendarDate >= GETDATE() AND IsWorkDate = 1
and then pass parameters two oposite function, but probably there is more elegant solution ?? 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply