In Employee master if any position vacant based on crews

Question

Hi I have below table structure. Having All Designations. CREATE TABLE [dbo].[Designation]( [DesignationNo] [int] IDENTITY(11) 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(11) 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(11) 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(11) 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(11) 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. 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_CrewNoCrewDesig_DesignationNoCrewDesig_LocationNo) VALUES ('2' '1' '1' '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo CrewDesig_CrewNoCrewDesig_DesignationNoCrewDesig_LocationNo) VALUES ('3' '1' '1' '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo CrewDesig_CrewNoCrewDesig_DesignationNoCrewDesig_LocationNo) VALUES ('4' '1' '2' '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo CrewDesig_CrewNoCrewDesig_DesignationNoCrewDesig_LocationNo) VALUES ('5' '1' '3' '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo CrewDesig_CrewNoCrewDesig_DesignationNoCrewDesig_LocationNo) VALUES ('6' '2' '4' '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo CrewDesig_CrewNoCrewDesig_DesignationNoCrewDesig_LocationNo) VALUES ('14' '2' '5' '1'); INSERT INTO CrewWiseDesignationRequired (CrewDesignNo CrewDesig_CrewNoCrewDesig_DesignationNoCrewDesig_LocationNo) VALUES ('15' '2' '6' '1'); INSERT INTO Employee (EmployeeNo EmployeeNameCrewNoCrewDesignNoLocationNo) VALUES ('3' 'Alex' '1'2 '1'); INSERT INTO Employee (EmployeeNo EmployeeNameCrewNoCrewDesignNoLocationNo) VALUES ('4' 'Allen' '1'3 '1'); INSERT INTO Employee (EmployeeNo EmployeeNameCrewNoCrewDesignNoLocationNo) VALUES ('5' 'Rahul' '1' 4'1'); INSERT INTO Employee (EmployeeNo EmployeeNameCrewNoCrewDesignNoLocationNo) VALUES ('6' 'Sachin' '1'5 '1'); INSERT INTO Employee (EmployeeNo EmployeeNameCrewNoCrewDesignNoLocationNo) VALUES ('7' 'Anil' '1' 6'1'); INSERT INTO Employee (EmployeeNo EmployeeNameCrewNoCrewDesignNoLocationNo) VALUES ('8' 'Ramesh' '1'null '1'); Thanks & regards Basit

Basit Khan · Answer

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 Kumar · Answer

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.