Unfolding | Set Based Approach | Get Continuous Start Date of the Current Project – Episode 8

In this video, we are going to learn;

1. How and when to use Apply operator in SQL Server, and
2. How to compare rows on a given logic

You can download the script used in this example from below the video.

/******************************************************************************************************************************************************
Start using the SetBasedApproach database
******************************************************************************************************************************************************/
--Start using the existing database SetBasedApproach which has been already created in this series
USE SetBasedApproach
GO

/******************************************************************************************************************************************************
Create required objects
******************************************************************************************************************************************************/
--Drop the EmployeeMaster table if it exists already in the database
IF(OBJECT_ID('dbo.EmployeeMaster') IS NOT NULL)
DROP TABLE dbo.EmployeeMaster

--Create Employee master table
CREATE TABLE dbo.EmployeeMaster
(
EmpID INT NOT NULL PRIMARY KEY,
EmpName NVARCHAR(255) NOT NULL
)

--Drop the project master table if it exists already in the database
IF(OBJECT_ID('dbo.ProjectMaster') IS NOT NULL)
DROP TABLE dbo.ProjectMaster

--Create project master table
CREATE TABLE dbo.ProjectMaster
(
ProjectID INT NOT NULL PRIMARY KEY,
ProjectName VARCHAR(255)
)

--Drop the EmployeeProjectDetail table if it exists already in the database
IF(OBJECT_ID('dbo.EmployeeProjectDetail') IS NOT NULL)
DROP TABLE dbo.EmployeeProjectDetail

--Create project master table
CREATE TABLE dbo.EmployeeProjectDetail
(
RowID INT IDENTITY NOT NULL PRIMARY KEY,
EmpID INT, --REFERENCES dbo.EmployeeMaster(EMpID)
ProjectID INT, --REFERENCES dbo.ProjectMaster(ProjectID)
EffectiveFrom DATETIME NOT NULL,
EffectiveTo DATETIME NOT NULL
)

/******************************************************************************************************************************************************
Insert sample data
******************************************************************************************************************************************************/
--Add data into employee master table
INSERT dbo.EmployeeMaster ([EmpID], [EmpName])
VALUES (1, N'Adam'),
(2, N'Joy'),
(3, N'Roy'),
(4, N'Mac')

--Add data into project master table
INSERT INTO dbo.ProjectMaster(ProjectID, ProjectName)
VALUES(1, 'ERP'),
(2, 'Web Development'),
(3, 'Mobile App Development'),
(4, 'Architect Group')

--Add data into EmployeeProjectDetail
INSERT INTO dbo.EmployeeProjectDetail
(EmpID, ProjectID, EffectiveFrom, EffectiveTo)
VALUES('1', '1', '20120101', '20120630'),
('1', '2', '20120701', '20120930'),
('1', '3', '20121001', '20121031'),
('1', '1', '20121101', '20121231'),
('1', '4', '20130101', '20130508'),
('1', '1', '20130509', '20131231'),
('1', '1', '20140101', '20141231'),
('1', '1', '20150101', '20151231'),
('1', '1', '20160101', '20161231'),
('2', '2', '20151012', '20151231'),
('2', '2', '20160101', '20161231'),
('3', '1', '20140510', '20140910'),
('3', '3', '20140911', '20141018'),
('3', '1', '20141019', '20141231'),
('3', '1', '20150101', '20151231'),
('3', '1', '20160101', '20161231'),
('4', '4', '20160101', '20161231')

/******************************************************************************************************************************************************
Get Latest Continuous Start Date of the Current Project
******************************************************************************************************************************************************/
--Display employee master data
SELECT * FROM dbo.EmployeeMaster

--Display project master data
SELECT * FROM dbo.ProjectMaster

--Display EmployeeProjectDetail data
SELECT RowID, EmpID, ProjectID, CONVERT(VARCHAR(25), EffectiveFrom, 106) AS EffectiveFrom, CONVERT(VARCHAR(25), EffectiveTo, 106) AS EffectiveTo
FROM dbo.EmployeeProjectDetail

--Get the current project latest start date
SELECT
EM.EmpID, EM.EmpName, PM.ProjectName,
CONVERT(VARCHAR(25), OACurrentProjectStartDate.EffectiveFrom, 106) AS ProjectStartDate
FROM dbo.EmployeeMaster EM
--Get the current active project detail row
OUTER APPLY
(
SELECT TOP 1 EPD.ProjectID, EPD.EffectiveFrom
FROM dbo.EmployeeProjectDetail EPD
WHERE EPD.EmpID = EM.EmpID
ORDER BY EPD.EffectiveFrom DESC
) OACurrentActiveProject
--Get the previous project name and end date
OUTER APPLY
(
SELECT TOP 1 EPD.ProjectID, EPD.EffectiveTo
FROM dbo.EmployeeProjectDetail EPD
WHERE EPD.EmpID = EM.EmpID AND EPD.ProjectID <> ISNULL(OACurrentActiveProject.ProjectID, '')
AND EPD.EffectiveFrom < ISNULL(OACurrentActiveProject.EffectiveFrom, '19000101')
ORDER BY EPD.EffectiveFrom DESC
) OAPreviousProjectDetail
--Get the actual project start date
OUTER APPLY
(
SELECT TOP 1 EPD.ProjectID, EPD.EffectiveFrom
FROM dbo.EmployeeProjectDetail EPD
WHERE EPD.EmpID = EM.EmpID
AND EPD.ProjectID = ISNULL(OACurrentActiveProject.ProjectID, '')
AND EPD.EffectiveFrom > ISNULL(OAPreviousProjectDetail.EffectiveTo, '19000101')
ORDER BY EPD.EffectiveFrom ASC
) OACurrentProjectStartDate
INNER JOIN dbo.ProjectMaster PM ON PM.ProjectID = OACurrentProjectStartDate.ProjectID