Basit Khan

Basit Khan

  • 1.3k
  • 336
  • 115.2k

In Employee master if any position vacant based on crews

Aug 30 2023 5:22 AM

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]

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.

 
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

 


Answers (2)