Calculate the next working day (excluding weekends and vacation/holidays) in SQL Server
We can add days fairly easily in SQL Server. But what about the next working day?
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: https://rebrand.ly/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): https://rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): https://rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): https://rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): https://rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): https://rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
----
In this video, I'll show you how to calculate the next working day (excluding weekends and vacation/holidays) in SQL Server. This video is perfect for students and professionals who need to schedule appointments or manage their work schedule.
I'll walk you through the steps needed to calculate the next working day in SQL Server, and I'll provide examples to make the process easier to understand. You'll be able to use this information to manage your work schedule and avoid conflicts with other commitments. So don't wait any longer, watch this video and learn how to calculate the next working day in SQL Server!
The end code is (replace "Less Than" with the appropriate symbol):
DROP TABLE IF EXISTS DateTable;
DROP TABLE IF EXISTS Holidays;
CREATE TABLE DateTable
(Dates Date)
INSERT INTO DateTable
SELECT DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '2009-12-31')
FROM sys.objects AS O1
CROSS JOIN sys.objects AS O2
CREATE TABLE Holidays
(Holiday date)
INSERT INTO Holidays
VALUES ('2023-12-25'),('2023-12-26'),('2024-01-01')
--SELECT *
--FROM DateTable
--WHERE DATEPART(WEEKDAY, Dates) NOT IN (7, 1)
DECLARE @myDate date = '2023-12-24';
With LEADTable AS (
SELECT Dates, LEAD(Dates, 3) OVER(ORDER BY Dates) as ThreeDaysLater
FROM DateTable
WHERE DATEPART(WEEKDAY, Dates) NOT IN (7, 1)
AND Dates NOT IN (SELECT Holiday FROM Holidays)
)
SELECT *
FROM LEADTable
WHERE Dates = (SELECT MAX(Dates) from DateTable
WHERE DATEPART(WEEKDAY, Dates) NOT IN (7, 1)
AND Dates NOT IN (SELECT Holiday FROM Holidays)
AND Dates
Replace with "Less Than"
=@myDate)
----
Links to my website are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: http://idodata.com/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): http://idodata.com/database-fundament...
SQL Server Essential in an Hour: http://idodata.com/sql-server-essenti...
70-462 SQL Server Database Administration (DBA): http://idodata.com/sql-server-databas...
DP-300: Administering Relational Databases: http://idodata.com/dp-300-administeri...
Microsoft SQL Server Reporting Services (SSRS): http://idodata.com/microsoft-sql-serv...
SQL Server Integration Services (SSIS): http://idodata.com/sql-server-integra...
SQL Server Analysis Services (SSAS): http://idodata.com/sql-server-ssas-mu...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
1Z0-071 Oracle SQL Developer – certified associate: http://idodata.com/iz0-071-oracle-sql...
SQL for Microsoft Access: http://idodata.com/sql-for-microsoft-...
DP-900: Microsoft Azure Data Fundamentals: http://idodata.com/dp-900-microsoft-a...
