I have the below table structure.
Having All Designations.
CREATE TABLE [dbo].[Designation](
[DesignationNo] [int] IDENTITY(1,1) NOT NULL,
[DesignationName] [nvarchar](50) NULL,
CONSTRAINT [PK_Designation] PRIMARY KEY CLUSTERED
(
[DesignationNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Having All crew
CREATE TABLE [dbo].[Crew](
[CrewNo] [int] IDENTITY(1,1) NOT NULL,
[CrewName] [nvarchar](50) NULL,
CONSTRAINT [PK_Crew] PRIMARY KEY CLUSTERED
(
[CrewNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Having all locations:
CREATE TABLE [dbo].[Location](
[LocationNo] [int] IDENTITY(1,1) NOT NULL,
[LocationName] [nvarchar](50) NULL,
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[LocationNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Here setting or fixing the structure what should be in particular crew based on crew and location show that if employee position is vacant or not.
CREATE TABLE [dbo].[CrewWiseDesignationRequired](
[CrewDesignNo] [int] IDENTITY(1,1) NOT NULL,
[CrewDesig_DesignationNo] [int] NULL,
[CrewDesig_CrewNo] [int] NULL,
[CrewDesig_LocationNo] [int] NULL,
CONSTRAINT [PK_CrewWiseDesignationRequired] PRIMARY KEY CLUSTERED
(
[CrewDesignNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] WITH CHECK ADD CONSTRAINT [FK_CrewWiseDesignationRequired_Crew] FOREIGN KEY([CrewDesig_CrewNo])
REFERENCES [dbo].[Crew] ([CrewNo])
GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] CHECK CONSTRAINT [FK_CrewWiseDesignationRequired_Crew]
GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] WITH CHECK ADD CONSTRAINT [FK_CrewWiseDesignationRequired_Designation] FOREIGN KEY([CrewDesignNo])
REFERENCES [dbo].[Designation] ([DesignationNo])
GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] CHECK CONSTRAINT [FK_CrewWiseDesignationRequired_Designation]
GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] WITH CHECK ADD CONSTRAINT [FK_CrewWiseDesignationRequired_Location] FOREIGN KEY([CrewDesig_LocationNo])
REFERENCES [dbo].[Location] ([LocationNo])
GO
ALTER TABLE [dbo].[CrewWiseDesignationRequired] CHECK CONSTRAINT [FK_CrewWiseDesignationRequired_Location]
GO
below employee master.
CREATE TABLE [dbo].[Employee](
[EmployeeNo] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [nvarchar](50) NULL,
[DesignationNo] [int] NULL,
[CrewNo] [int] NULL,
[CrewDesignNo] [int] NULL,
[LocationNo] [int] NULL,
CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED
(
[EmployeeNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Crew] FOREIGN KEY([CrewNo])
REFERENCES [dbo].[Crew] ([CrewNo])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Crew]
GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_CrewWiseDesignationRequired] FOREIGN KEY([CrewDesignNo])
REFERENCES [dbo].[CrewWiseDesignationRequired] ([CrewDesignNo])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_CrewWiseDesignationRequired]
GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Designation] FOREIGN KEY([DesignationNo])
REFERENCES [dbo].[Designation] ([DesignationNo])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Designation]
GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Location] FOREIGN KEY([LocationNo])
REFERENCES [dbo].[Location] ([LocationNo])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Location]
GO
How to write the query to show the vacant position in query.
The example here sets the crew designation wise.
Crew-A
IT Manager
IT Manager
IT Assistant
Programmer
Crew-B
IT Head
IT Team Lead
IT Team Lead
How to show the below output:
Employee Master
Crew-A
Alex IT Manager
Vacant IT Manager
Allen IT Assistant
Rahul Programmer
Crew-B
Sachin IT Head
Anil IT Team Lead
Vacant IT Team Lead
Ramesh Office Boy
Below is the
insert
query:
INSERT INTO Crew (CrewNo, CrewName) VALUES ('1', 'Crew-A');
INSERT INTO Crew (CrewNo, CrewName) VALUES ('2', 'Crew-B');
INSERT INTO Designation (DesignationNo, DesignationName) _
VALUES ('1', 'IT Manager');
INSERT INTO Designation (DesignationNo, DesignationName) _
VALUES ('2', 'IT Assistant');
INSERT INTO Designation (DesignationNo, DesignationName) _
VALUES ('3', 'Programmer');
INSERT INTO Designation (DesignationNo, DesignationName) _
VALUES ('4', 'IT Head');
INSERT INTO Designation (DesignationNo, DesignationName) _
VALUES ('5', 'IT Team Lead');
INSERT INTO Designation (DesignationNo, DesignationName) _
VALUES ('6', 'Office Boy');
INSERT INTO Location (LocationNo, LocationName) VALUES ('1', 'Location-A');
INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) _
VALUES ('2', '1', '1', '1');
INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) _
VALUES ('3', '1', '1', '1');
INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) _
VALUES ('4', '1', '2', '1');
INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) _
VALUES ('5', '1', '3', '1');
INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) _
VALUES ('6', '2', '4', '1');
INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) _
VALUES ('14', '2', '5', '1');
INSERT INTO CrewWiseDesignationRequired (CrewDesignNo, CrewDesig_CrewNo,CrewDesig_DesignationNo,CrewDesig_LocationNo) _
VALUES ('15', '2', '6', '1');
INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,_
CrewDesignNo,LocationNo) VALUES ('3', 'Alex', '1',2, '1');
INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,_
CrewDesignNo,LocationNo) VALUES ('4', 'Allen', '1',3, '1');
INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,_
CrewDesignNo,LocationNo) VALUES ('5', 'Rahul', '1', 4,'1');
INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,_
CrewDesignNo,LocationNo) VALUES ('6', 'Sachin', '1',5, '1');
INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,_
CrewDesignNo,LocationNo) VALUES ('7', 'Anil', '1', 6,'1');
INSERT INTO Employee (EmployeeNo, EmployeeName,CrewNo,_
CrewDesignNo,LocationNo) VALUES ('8', 'Ramesh', '1',null, '1');
What I have tried:
I tried the below query, but even the result was not exactly coming.
SELECT
'Employee Master' AS [Employee Master],
c.[CrewName] AS [Crew],
COALESCE(e.[EmployeeName], 'Vacant') AS [EmployeeName],
COALESCE(d.[DesignationName], 'Vacant') AS [Designation]
FROM
[dbo].[Crew] c
CROSS JOIN
[dbo].[Designation] d
CROSS JOIN
[dbo].[Location] l
LEFT JOIN
[dbo].[CrewWiseDesignationRequired] cr _
ON c.[CrewNo] = cr.[CrewDesig_CrewNo]
AND d.[DesignationNo] = cr.[CrewDesig_DesignationNo]
AND l.[LocationNo] = cr.[CrewDesig_LocationNo]
LEFT JOIN
[dbo].[Employee] e ON cr.[CrewDesignNo] = e.[CrewDesignNo]
AND cr.[CrewDesig_LocationNo] = e.[LocationNo]
AND c.[CrewNo] = e.[CrewNo]
ORDER BY
c.[CrewName], d.[DesignationName], e.[EmployeeName];