January 5, 2016 at 9:42 am
Hello Gents
I have task to calculate future dates which excludes weekends and holidays for few columns. Example is below.
workdb contains following
Columns in sql server 2014 dbo.work (query should populate table automatically dbo.work)
Start Date Future1 Date Future2 Date Future3 Date Future 4 Date
01-01-2016
10-01-2016
20-02-2016
13-03-2016
05-07-2016
1) I need to calculate future1 date column from the start date criteria add 5 weeks (i.e 01-01-2016+ 5 weeks) includes holidays and weekends
then it update all the columns of future1 date in dbo.work table
2) Then it will take future1 date column and add 20 working days which exclude weekends & holidays to update future2 Date column and all the columns updated in future2 Date column in dbo.work table
3) Then it will take Future2 date column and add 40 working days which exclude weekends & holidays to update future3 Date and all the columns updated in future3 Date column in dbo.work table
4) Then it will take Future3 date column and add 2 weeks which exclude weekends & holidays to update future4 Date and all the columns updated in future4 Date column in dbo.work table
Please find following weekends and holidays:
Weekends:
Fridays, Saturdays
Working Days:
Sunday, Monday, Tuesday, Wednesday, Thursday
Holidays:
7-Jul-2016 to 13-Jul-2016
13-Sep-2016 to 16-Sep-2016
20-Sep-2016
Note:
If this is possible to done in single query and kindly attach working queries
thanks in advance
Az
January 5, 2016 at 12:19 pm
Question, I assume the following is incorrect:
Holidays:
7-Jul-2016 to 13-Jul-2015
13-Sep-2016 to 16-Sep-2015
20-Sep-2016
and that what you really mean is:
Holidays:
7-Jul-2016 to 13-Jul-2016
13-Sep-2016 to 16-Sep-2016
20-Sep-2016
I'll wait to hear your answer before doing any more work.
January 5, 2016 at 12:57 pm
Thanks for reply and correction , all dates in 2016 🙂
January 5, 2016 at 1:06 pm
azawan (1/5/2016)
Hello GentsI have task to calculate future dates which excludes weekends and holidays for few columns. Example is below.
workdb contains following
Columns in sql server 2014 workdb.
Start Date Future1 Date Future2 Date Future3 Date Future 4 Date
01-01-2016
10-01-2016
20-02-2016
13-03-2016
1) I need to calculate future1 date column from the start date criteria add 5 weeks (i.e 01-01-2016+ 5 weeks) includes holidays and weekends
then it update all the columns of future1 date in workdb
2) Then it will take future1 date column and add 20 working days which exclude weekends & holidays to update future2 Date column and all the columns updated in future2 Date column in workdb
3) Then it will take Future2 date column and add 40 working days which exclude weekends & holidays to update future3 Date and all the columns updated in future3 Date column in workdb
4) Then it will take Future3 date column and add 2 weeks which exclude weekends & holidays to update future4 Date and all the columns updated in future4 Date column in workdb
Please find following weekends and holidays:
Weekends:
Fridays, Saturdays
Working Days:
Sunday, Monday, Tuesday, Wednesday, Thursday
Holidays:
7-Jul-2016 to 13-Jul-2016
13-Sep-2016 to 16-Sep-2016
20-Sep-2016
Note:
If this is possible to done in single query and kindly attach working queries
thanks in advance
Az
think this would be a whole lot clearer if you provided your expected results based on the sample you have provided.
(not sure your holiday dates come into play with your sample data)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 5, 2016 at 1:32 pm
I can't use an inline tally table because a filter at work blocks it, so I'm using a physical tally table. The table is named "tally" and its column is "number". Naturally you'll need to change that part of the code to match your own tally cte / table.
--DROP TABLE dbo.Nonwork_Dates
CREATE TABLE dbo.Nonwork_Dates (
nonwork_date date NOT NULL CONSTRAINT Nonwork_Dates__PK PRIMARY KEY WITH (FILLFACTOR = 100),
reason_code tinyint NOT NULL
)
INSERT INTO dbo.Nonwork_Dates ( nonwork_date, reason_code )
SELECT nonwork_date, 1
FROM dbo.tally t
CROSS APPLY (
SELECT DATEADD(DAY, t.number, '20160101') AS nonwork_date
) AS assign_alias_names
WHERE
nonwork_date < '20170101' AND
DATEDIFF(DAY, 0, nonwork_date) % 7 IN (4, 5)
ORDER BY nonwork_date
INSERT INTO dbo.Nonwork_Dates ( nonwork_date, reason_code )
SELECT DATEADD(DAY, t.number, first_nonwork_date) AS nonwork_date, 2
FROM (
SELECT CAST('20160707' AS date) AS first_nonwork_date, CAST('20160713' AS date) AS last_nonwork_date UNION ALL
SELECT '20160913', '20160916' UNION ALL
SELECT '20160920', '20160920'
) AS holidays
INNER JOIN dbo.tally t ON t.number BETWEEN 0 AND DATEDIFF(DAY, first_nonwork_date, last_nonwork_date)
WHERE
NOT EXISTS(SELECT 1 FROM dbo.Nonwork_Dates nd WHERE nd.nonwork_date = DATEADD(DAY, t.number, first_nonwork_date))
ORDER BY nonwork_date
ALTER INDEX ALL ON dbo.Nonwork_Dates REBUILD
SELECT *
FROM (
SELECT CAST('20160101' AS date) AS start_date UNION ALL
SELECT '20160110' UNION ALL
SELECT '20160220' UNION ALL
SELECT '20160313'
) AS test_dates
CROSS APPLY (
SELECT DATEADD(DAY, 35, start_date) AS Future1_Date
) AS assign_alias_names1
CROSS APPLY (
SELECT MAX(future2) AS future2_date
FROM (
SELECT TOP (20) DATEADD(DAY, t.number, Future1_Date) AS future2
FROM dbo.tally t
WHERE NOT EXISTS(SELECT 1 FROM dbo.Nonwork_Dates nd WHERE nd.nonwork_date = DATEADD(DAY, t.number, Future1_Date))
) AS derived
) AS assign_alias_names2
CROSS APPLY (
SELECT MAX(future3) AS future3_date
FROM (
SELECT TOP (40) DATEADD(DAY, t.number, Future2_Date) AS future3
FROM dbo.tally t
WHERE NOT EXISTS(SELECT 1 FROM dbo.Nonwork_Dates nd WHERE nd.nonwork_date = DATEADD(DAY, t.number, Future2_Date))
) AS derived
) AS assign_alias_names3
CROSS APPLY (
SELECT MAX(future4) AS future4_date
FROM (
SELECT TOP (14) DATEADD(DAY, t.number, Future3_Date) AS future4
FROM dbo.tally t
WHERE NOT EXISTS(SELECT 1 FROM dbo.Nonwork_Dates nd WHERE nd.nonwork_date = DATEADD(DAY, t.number, Future3_Date))
) AS derived
) AS assign_alias_names4
Edit: Commented out DROP of Nonwork_Dates table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 5, 2016 at 2:26 pm
Dear Scot
First thanks for your reply, i am getting following error message
Msg 8116, Level 16, State 1, Line 8
Argument data type date is invalid for argument 2 of dateadd function.
Second date should be taken automatically from work table and fill the same table with future dates, i have attached sample table structure. I tried even with a new table as dbo.tally but same error
Thanks
Az
January 5, 2016 at 3:12 pm
azawan (1/5/2016)
Dear ScotFirst thanks for your reply, i am getting following error message
Msg 8116, Level 16, State 1, Line 8
Argument data type date is invalid for argument 2 of dateadd function.
Second date should be taken automatically from work table and fill the same table with future dates, i have attached sample table structure. I tried even with a new table as dbo.tally but same error
Thanks
Az
Then the tally table "number" is not an integer value.
CREATE TABLE dbo.tally (
number int NOT NULL,
CONSTRAINT tally__PK PRIMARY KEY CLUSTERED ( number )
)
INSERT INTO dbo.tally VALUES(0);
--code using cte / cross joins to insert values from 1 to 1,000,000 to the tally table goes here
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 5, 2016 at 3:19 pm
azawan (1/5/2016)
Dear ScotFirst thanks for your reply, i am getting following error message
Msg 8116, Level 16, State 1, Line 8
Argument data type date is invalid for argument 2 of dateadd function.
Second date should be taken automatically from work table and fill the same table with future dates, i have attached sample table structure. I tried even with a new table as dbo.tally but same error
Thanks
Az
Then the tally table "number" is not an integer value.
CREATE TABLE dbo.tally (
number int NOT NULL,
CONSTRAINT tally__PK PRIMARY KEY CLUSTERED ( number )
)
INSERT INTO dbo.tally VALUES(0);
--add code here to use cte / cross joins to insert values from 1 to 1,000,000 to the tally table
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 5, 2016 at 3:56 pm
Ok, Az ... this is not the most streamlined, and it's not formatted pretty, but it works:
Also, I included a temp table named #workdb to get it to work of course. You can remove that code
IF OBJECT_ID('tempdb.dbo.#workdb') IS NOT NULL DROP TABLE #workdb
GO
CREATE TABLE #workdb
(
[Start Date] datetime NOT NULL,
[Future1 Date] datetime,
[Future2 Date] datetime,
[Future3 Date] datetime,
[Future4 Date] datetime
)
GO
INSERT INTO #workdb ([Start Date]) VALUES ('01/01/2016')
INSERT INTO #workdb ([Start Date]) VALUES ('01/10/2016')
INSERT INTO #workdb ([Start Date]) VALUES ('02/20/2016')
INSERT INTO #workdb ([Start Date]) VALUES ('03/13/2016')
IF OBJECT_ID('tempdb.dbo.#Holiday') IS NOT NULL DROP TABLE #Holiday
GO
CREATE TABLE #Holiday (Holiday_Date datetime NOT NULL)
GO
INSERT INTO #Holiday VALUES ('07/07/2016')
INSERT INTO #Holiday VALUES ('07/08/2016')
INSERT INTO #Holiday VALUES ('07/09/2016')
INSERT INTO #Holiday VALUES ('07/10/2016')
INSERT INTO #Holiday VALUES ('07/11/2016')
INSERT INTO #Holiday VALUES ('07/12/2016')
INSERT INTO #Holiday VALUES ('07/13/2016')
INSERT INTO #Holiday VALUES ('09/13/2016')
INSERT INTO #Holiday VALUES ('09/14/2016')
INSERT INTO #Holiday VALUES ('09/15/2016')
INSERT INTO #Holiday VALUES ('09/16/2016')
INSERT INTO #Holiday VALUES ('09/20/2016')
GO
UPDATE #workdb
SET [Future1 Date] = DATEADD(week, 5, [Start Date])
UPDATE #workdb
SET [Future2 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 [Future1 Date] AS a, DATEADD(DAY, num + 20, [Future1 Date]) AS b
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
FROM Information_Schema.columns) AS t) AS dt
LEFT JOIN #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 #Holiday WHERE Holiday_Date BETWEEN a AND b)
GROUP BY a,b) AS du
WHERE workday = 20
)
UPDATE #workdb
SET [Future3 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 [Future2 Date] AS a, DATEADD(DAY, num + 40, [Future2 Date]) AS b
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
FROM Information_Schema.columns) AS t) AS dt
LEFT JOIN #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 #Holiday WHERE Holiday_Date BETWEEN a AND b)
GROUP BY a,b) AS du
WHERE workday = 40)
UPDATE #workdb
SET [Future4 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 [Future3 Date] AS a, DATEADD(DAY, num + 14, [Future3 Date]) AS b
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
FROM Information_Schema.columns) AS t) AS dt
LEFT JOIN #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 #Holiday WHERE Holiday_Date BETWEEN a AND b)
GROUP BY a,b) AS du
WHERE workday = 14)
SELECT * FROM #workdb
January 5, 2016 at 4:12 pm
azawan (1/5/2016)
Then it will take Future3 date column and add 2 weeks which exclude weekends & holidays to update future4 Date
What does it really mean?
14 working days? Why do not simply say so?
Or 10 working days, as per 2 "normal" weeks?
The requirements need to be defined more clearly.
_____________
Code for TallyGenerator
January 6, 2016 at 10:54 am
thank you for your time on this please can you check the difference between manual calculation and your query results why its different can u explain thanks
Your Query:
Start Date Future1 Date Future2 Date Future3 Date Future4 Date
2016-01-01 00:00:00.0002016-02-05 00:00:00.0002016-03-06 00:00:00.0002016-05-01 00:00:00.0002016-05-19 00:00:00.000
Manual Calculation
Start Date Future1 Date Future2 Date Future3 Date Future4 Date
2016-01-01 00:00:00.0002016-02-05 00:00:00.0002016-03-03 00:00:00.0002016-04-28 00:00:00.0002016-05-12 00:00:00.000
January 6, 2016 at 10:54 am
thank you for your time on this please can you check the difference between manual calculation and your query results why its different can u explain thanks
Your Query:
Start Date Future1 Date Future2 Date Future3 Date Future4 Date
2016-01-01 00:00:00.0002016-02-05 00:00:00.0002016-03-06 00:00:00.0002016-05-01 00:00:00.0002016-05-19 00:00:00.000
Manual Calculation
Start Date Future1 Date Future2 Date Future3 Date Future4 Date
2016-01-01 00:00:00.0002016-02-05 00:00:00.0002016-03-03 00:00:00.0002016-04-28 00:00:00.0002016-05-12 00:00:00.000
January 6, 2016 at 11:01 am
Thanks for your reply
After running your query i got below results
start_dateFuture1_Datefuture2_datefuture3_datefuture4_date
2016-01-012016-02-05 2016-02-05 2016-02-05 2016-02-05
2016-01-102016-02-14 2016-02-14 2016-02-14 2016-02-14
2016-02-202016-03-26 2016-03-26 2016-03-26 2016-03-26
2016-03-132016-04-17 2016-04-17 2016-04-172016-04-17
Regards
Az
January 6, 2016 at 12:25 pm
azawan (1/6/2016)
@ScotThanks for your reply
After running your query i got below results
start_dateFuture1_Datefuture2_datefuture3_datefuture4_date
2016-01-012016-02-05 2016-02-05 2016-02-05 2016-02-05
2016-01-102016-02-14 2016-02-14 2016-02-14 2016-02-14
2016-02-202016-03-26 2016-03-26 2016-03-26 2016-03-26
2016-03-132016-04-17 2016-04-17 2016-04-172016-04-17
Regards
Az
Sorry, I updated the code to correct the "TOP (20)" in every query to the correct number of days. Here's the final code:
--DROP TABLE dbo.Nonwork_Dates
CREATE TABLE dbo.Nonwork_Dates (
nonwork_date date NOT NULL CONSTRAINT Nonwork_Dates__PK PRIMARY KEY WITH (FILLFACTOR = 100),
reason_code tinyint NOT NULL
)
INSERT INTO dbo.Nonwork_Dates ( nonwork_date, reason_code )
SELECT nonwork_date, 1
FROM dbo.tally t
CROSS APPLY (
SELECT DATEADD(DAY, t.number, '20160101') AS nonwork_date
) AS assign_alias_names
WHERE
nonwork_date < '20170101' AND
DATEDIFF(DAY, 0, nonwork_date) % 7 IN (4, 5)
ORDER BY nonwork_date
INSERT INTO dbo.Nonwork_Dates ( nonwork_date, reason_code )
SELECT DATEADD(DAY, t.number, first_nonwork_date) AS nonwork_date, 2
FROM (
SELECT CAST('20160707' AS date) AS first_nonwork_date, CAST('20160713' AS date) AS last_nonwork_date UNION ALL
SELECT '20160913', '20160916' UNION ALL
SELECT '20160920', '20160920'
) AS holidays
INNER JOIN dbo.tally t ON t.number BETWEEN 0 AND DATEDIFF(DAY, first_nonwork_date, last_nonwork_date)
WHERE
NOT EXISTS(SELECT 1 FROM dbo.Nonwork_Dates nd WHERE nd.nonwork_date = DATEADD(DAY, t.number, first_nonwork_date))
ORDER BY nonwork_date
ALTER INDEX ALL ON dbo.Nonwork_Dates REBUILD
SELECT *
FROM (
SELECT CAST('20160101' AS date) AS start_date UNION ALL
SELECT '20160110' UNION ALL
SELECT '20160220' UNION ALL
SELECT '20160313'
) AS test_dates
CROSS APPLY (
SELECT DATEADD(DAY, 35, start_date) AS Future1_Date
) AS assign_alias_names1
CROSS APPLY (
SELECT MAX(future2) AS future2_date
FROM (
SELECT TOP (20) DATEADD(DAY, t.number, Future1_Date) AS future2
FROM dbo.tally t
WHERE NOT EXISTS(SELECT 1 FROM dbo.Nonwork_Dates nd WHERE nd.nonwork_date = DATEADD(DAY, t.number, Future1_Date))
) AS derived
) AS assign_alias_names2
CROSS APPLY (
SELECT MAX(future3) AS future3_date
FROM (
SELECT TOP (40) DATEADD(DAY, t.number, Future2_Date) AS future3
FROM dbo.tally t
WHERE NOT EXISTS(SELECT 1 FROM dbo.Nonwork_Dates nd WHERE nd.nonwork_date = DATEADD(DAY, t.number, Future2_Date))
) AS derived
) AS assign_alias_names3
CROSS APPLY (
SELECT MAX(future4) AS future4_date
FROM (
SELECT TOP (14) DATEADD(DAY, t.number, Future3_Date) AS future4
FROM dbo.tally t
WHERE NOT EXISTS(SELECT 1 FROM dbo.Nonwork_Dates nd WHERE nd.nonwork_date = DATEADD(DAY, t.number, Future3_Date))
) AS derived
) AS assign_alias_names4
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply