How to Count Workdays in SQL: Two Effective Methods Explained!

In this video, we'll explore two powerful methods to calculate the number of workdays in a specific month using SQL. Whether you're managing a holiday calendar or simply need to exclude weekends, these approaches will help you get accurate results.

Method 1: Leveraging the Holiday_Table and Interview_Calendar to count workdays while accounting for holidays.
Method 2: Utilizing a CalendarTable to generate dates for the entire year and a HolidayTable to exclude holidays and weekends from the count.
By the end of this video, you'll understand how to implement these SQL queries to count workdays in any month and enhance your database management skills. Perfect for SQL beginners and experts alike!

--Network days
--Method1
CREATE TABLE Holiday_Table (
calendar_date DATE,
time TIME,
day VARCHAR(10),
holiday BIT -- 1 for holiday, 0 for working day
);

CREATE TABLE Interview_Calendar (
cal_date DATE,
working_day BIT -- 1 for working day, 0 for non-working day
);

-- Inserting data into Holiday_Table
INSERT INTO Holiday_Table (calendar_date, time, day, holiday) VALUES
('2024-08-01', '09:00:00', 'Thursday', 0),
('2024-08-02', '09:00:00', 'Friday', 0),
('2024-08-03', '09:00:00', 'Saturday', 1), -- Holiday
('2024-08-04', '09:00:00', 'Sunday', 1), -- Holiday
('2024-08-05', '09:00:00', 'Monday', 0);

-- Inserting data into Interview_Calendar
INSERT INTO Interview_Calendar (cal_date, working_day) VALUES
('2024-08-01', 1),
('2024-08-02', 1),
('2024-08-03', 0),
('2024-08-04', 0),
('2024-08-05', 1);

SELECT COUNT(*)
FROM Interview_Calendar
WHERE working_day = 1
AND MONTH(cal_date) = 8 -- August
AND YEAR(cal_date) = 2024;

--Method2
CREATE TABLE CalendarTable (
DateColumn DATE PRIMARY KEY
);

-- Fill the CalendarTable with dates for the whole year
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-12-31';

WHILE @StartDate (lessthan)= @EndDate
BEGIN
SET @StartDate = DATEADD(DAY, 1, @StartDate);
END;

select * from CalendarTable
CREATE TABLE HolidayTable (
HolidayDate DATE PRIMARY KEY,
HolidayName VARCHAR(50)
);

-- Insert some sample holidays
INSERT INTO HolidayTable (HolidayDate, HolidayName)
VALUES ('2023-08-15', 'Independence Day'),
('2023-01-26', 'Republic Day');
select * from HolidayTable
SELECT
COUNT(*) AS Workdays
FROM
CalendarTable AS c
LEFT JOIN
HolidayTable AS h ON c.DateColumn = h.HolidayDate
WHERE
c.DateColumn BETWEEN '2023-08-01' AND '2023-08-31'
AND DATENAME(WEEKDAY, c.DateColumn) NOT IN ('Saturday', 'Sunday')
AND h.HolidayDate IS NULL;

SQL Interview Problem: Splitting Full Names into First, Middle, and Last Names

Top SQL Interview Questions: RANK, DENSE_RANK, ROW_NUMBER Explained

Top 3 Products by Sales & Employees by Salary | SQL Ranking Functions Explained

SQL Interview Challenge: Find Hidden Likes in Your Friends' Favorites!

SQL Magic: Aggregating Marks & Delivery Dates with Advanced Queries!

Top 20% vs Bottom 20% of Students in SQL: NTILE vs TOP PERCENT

SQL Tricks: Find the 3rd Lowest Salary in Each Department with Two Powerful Methods!

SQL Tutorial: Find Employees Who Joined Before Their Managers

SQL Tutorial: Retrieve Specific Rows with OFFSET & ROW_NUMBER - Top 2 Methods Compared!

How to Build a Location-Based Hierarchy in SQL | Recursive Query Tutorial

How to Count Weekends in Any Month Using SQL Server: A Step-by-Step Guide

SQL Date Magic: Find & Format the Last Day of the Previous Month

#SQLTutorial
#WorkdayCalculation
#SQLQueryMethods
#CalendarandHolidayTable
#SQL forBeginners
#DatabaseManagement
#SQLTipsandTricks
#SQLWorkdayCount