Write a query In Employee master if any position vacant based on crew then show vacant

1

Hi,

I have below table structure.

  1. 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
  1. 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
  1. 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]

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.

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.

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

kindly help on this.

Thanks & regards,
Basit.

2

When you create your query to pull the names and positions for viewing, use this (you may need to revise slightly to fit your actual table/field names):

Case WHEN EmployeeName IS NULL THEN 'Vacant' ELSE EmployeeName END As EmployeeName