In Employee master if any position vacant based on crews

Hi,

I have 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 location.
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]

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]

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.

example here set 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 below output.

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 are 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');

Thanks & regards,

Basit

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Basit Khan3y ago
Many thanks, yes that rights but CrewWiseDesignationRequired i define each crew how many position required.

example Crew-A has IT Manager 2 numbers, IT Assistant 2 numbers and if employee in Crew-A has only one IT manager then in employeeName show vacant and in Designation will Show IT Manager.

Regards,

Basit.
Rathrola Prem Kumar3y ago
SELECT
C.CrewName,
E.EmployeeName,
CASE
WHEN E.DesignationNo IS NULL THEN 'Vacant'
ELSE D.DesignationName
END AS DesignationName
FROM Crew C
LEFT JOIN (
SELECT
CrewNo,
MAX(CASE WHEN DesignationNo IS NOT NULL THEN EmployeeName ELSE NULL END) AS EmployeeName,
DesignationNo
FROM Employee
GROUP BY CrewNo, DesignationNo
) E ON C.CrewNo = E.CrewNo
LEFT JOIN Designation D ON E.DesignationNo = D.DesignationNo
ORDER BY C.CrewNo, D.DesignationNo;

This query retrieves the crew name, employee name, and designation for each employee in each crew. If there is no employee in a particular designation for a crew, the query displays 'Vacant' for that designation. Am I right?

Try this query and let me know whether it's returning data correctly or not.