December 3, 2018 at 9:40 pm
Dears
I am adding work days which includes weekends and holidays. The calculated day should not be on weekend,hand result result as well not on weekend should jump to next working day. But i am not getting the desired results. Can you please fix query below which is providing inaccurate results. Consider weekend is Friday and Saturday.
Table 1: Holiday
Holiday_Date
2018-12-01 00:00:00.000
2018-12-02 00:00:00.000
2018-12-03 00:00:00.000
2018-12-04 00:00:00.000
2018-12-05 00:00:00.000
2018-12-06 00:00:00.000
2018-12-07 00:00:00.000
2018-12-08 00:00:00.000
Table 2: Cal
NewDate Cal Date
2018-12-01 00:00:00.000 NULL
2018-12-10 00:00:00.000 NULL
2018-12-11 00:00:00.000 NULL
2018-12-12 00:00:00.000 NULL
2018-12-13 00:00:00.000 NULL
2018-12-14 00:00:00.000 NULL
2018-12-15 00:00:00.000 NULL
2018-12-16 00:00:00.000 NULL
Query:-
UPDATE [dbo].[Cal]
SET [Cal Date] = (
SELECT b
FROM (SELECT b,
(DATEDIFF(dd, a, b))
-(DATEDIFF(wk, a, b) * 2)
-(CASE WHEN DATENAME(dw, a) = 'Saturday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, b) = 'Friday' THEN 1 ELSE 0 END)
-COUNT(o.Holiday_Date) AS Workday
FROM (SELECT [NewDate] AS a, DATEADD(DAY, num + 10, [NewDate]) AS b
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
FROM Information_Schema.columns) AS t) AS dt
LEFT JOIN dbo.Holiday AS o
ON o.Holiday_Date BETWEEN a AND b
AND DATENAME(dw, o.Holiday_Date) NOT IN ('Friday','Saturday')
WHERE DATENAME(dw, b) NOT IN ('Friday','Saturday')
AND b NOT IN (SELECT Holiday_Date FROM dbo.Holiday WHERE Holiday_Date BETWEEN a AND b)
GROUP BY a,b) AS du
WHERE workday = 10
Query Results (Which are inaccurate)
NewDate Cal Date
2018-12-01 00:00:00.000 2018-12-25 00:00:00.000
2018-12-10 00:00:00.000 2018-12-24 00:00:00.000
2018-12-11 00:00:00.000 2018-12-25 00:00:00.000
2018-12-12 00:00:00.000 2018-12-26 00:00:00.000
2018-12-13 00:00:00.000 2018-12-27 00:00:00.000
2018-12-14 00:00:00.000 2018-12-30 00:00:00.000
2018-12-15 00:00:00.000 2019-01-01 00:00:00.000
2018-12-16 00:00:00.000 2018-12-30 00:00:00.000
December 4, 2018 at 5:12 am
Suggestion:
Make an Auxilary table 'Calendar' and use that table to determine the number of days in a period, or between two dates.
In the Auxilary table each day has a row and each row has the attributes which belong to a row. For an example see below the signature.
The query to determine the number of working_days excluding holidays.
DECLARE @Working_days INT = -99
select @Working_days = COUNT(*) from tempdb.dbo.SP_Calendar WHERE dt>getdate() and dt < '20251226' and isweekday = 1 and isholiday = 0
Print @working_days
With other simple queries and calculations the number of days, working hours etc. can be determined.
(Between two days, since two days.)
With the table it is even simple to determine the 100th working day from today, or the 6th monday from now or the next holiday.
Once the table is filled it is highly reusable.
(Calculation how old you are in days).
(Calculating the number of working days till your pension).
(Calculating 80 working days into the future).
(Calculating the number of working days in a year).
(Calculating the number of working hours in each year).
etc. etc.
Ben
A possible definition for the table :
CREATE TABLE tempdb.dbo.SP_Calendar
(
dt DATETIME NOT NULL PRIMARY KEY CLUSTERED,
isWeekday BIT,
isHoliday BIT,
Holidayname varchar(200),
Y SMALLINT,
FY SMALLINT, -- Fiscal Year
Q TINYINT, -- Quarter
M TINYINT, -- Month
D TINYINT, -- Day
DW TINYINT, -- Weekday
-- Dy int, -- day within the year
-- day -- Auxilary day, for example the number of days since 1880 - 01 - 01
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT, -- Weeknumber
spare1 varchar(30), -- To be determined by a user
spare2 varchar(30), -- To be determined by a user.
spare3 varchar(30) -- To be determined by a user.
)
Populating the table is dependend on the region, local holydays and a number of other things.
Advised is to fill the table from 1900 to 2100 or more. (One row for each day).
The redundancy in the table (dayname and D for example) is to make queries simpler.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply