January 8, 2016 at 10:48 am
@sql2012
Dear
if i change days from from 7 to 2 "DATEADD(DAY, num + 2",
from 7 to 2 " WHERE workday = 2" query doesnt give results for future2,3,4 dates
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 + 2, [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 = 2
)
Results:
Start DateFuture1 DateFuture2 DateFuture3 DateFuture4 Date
2016-01-01 00:00:00.0002016-01-08 00:00:00.0002016-01-12 00:00:00.0002016-03-08 00:00:00.0002016-03-28 00:00:00.000
2016-01-10 00:00:00.0002016-01-17 00:00:00.000NULLNULLNULL
2016-02-20 00:00:00.0002016-02-27 00:00:00.0002016-03-03 00:00:00.0002016-04-28 00:00:00.0002016-05-18 00:00:00.000
2016-03-13 00:00:00.0002016-03-20 00:00:00.000NULLNULLNULL
January 8, 2016 at 11:08 am
azawan (1/8/2016)
@ScotDear Scot same results
When I run the exact code I posted above, I get these results:
start_dateFuture1_Datefuture2_datefuture3_datefuture4_date
2016-01-012016-02-052016-03-032016-04-272016-05-16
2016-01-102016-02-142016-03-102016-05-042016-05-23
2016-02-202016-03-262016-04-212016-06-152016-07-04
2016-03-132016-04-172016-05-122016-07-062016-08-01
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 8, 2016 at 12:37 pm
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
Yeah, I was playing around with solving this while at the same time handling several phone calls, by the time I got any successful results I didn't get the chance to double check them manually to see if the results were correct.
Let me get back and try looking at it again to figure it out.
January 8, 2016 at 2:54 pm
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
I know the questions has been asked before, but what do you mean by "2 weeks"?
Is that:
2 full weeks = 14 days
- or -
2 full business weeks = 10 days?
December 3, 2018 at 11:36 am
Hello Gentlemen
Your kind support in above
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply