Employee Month-Wise Service Length and Average Salary Using Sql
-
Hello! I've tried to get employee unit-wise service length (How many months or years an employee has completed in a company) and got it worked. In the same time, trying to get the average salary as well. Here is the sample that I've used: (Getting the service length but stuck in the average salary within that range. Example: 0 - 6 months average salary)
Tried this query:
SELECT o.UnitName, p.DeptName, m.SecID, l.SecName, COUNT(m.EmpId) AS cnt,
SUM(CASE WHEN DATEDIFF(MONTH, m.Joindate, GETDATE()) >= 0 AND DATEDIFF(MONTH, m.Joindate, GETDATE()) <= 6
THEN 1 ELSE 0
END) AS [0 - 6 Months],
AVG(CASE WHEN (m.GrossSal + m.SkillBonus) > 0
THEN 1 ELSE 0
END) AS [0 - 6 Months Average Salary], ---- [Between range 0 - 6 months]
SUM(CASE WHEN DATEDIFF(MONTH, m.Joindate, GETDATE()) >= 7 AND DATEDIFF(MONTH, m.Joindate, GETDATE()) <= 12
THEN 1 ELSE 0
END) AS [7 - 12 Months],
SUM(CASE WHEN DATEDIFF(MONTH, m.Joindate, GETDATE()) >= 13 AND DATEDIFF(MONTH, m.Joindate, GETDATE()) <= 24
THEN 1 ELSE 0
END) AS [13 - 24 Months]
FROM EmpInf m INNER JOIN
Department k
ON k.DeptId = m.DeptId INNER JOIN
Section l
ON l.secId = m.SecID INNER JOIN
UnitInf o
ON o.UnitID = l.UnitName INNER JOIN
Department p
ON p.DeptId = m.DeptId
WHERE Desg IN ('Jr. Operator', 'Operator') AND Active = 'Active' AND
p.DeptName = 'Production'
GROUP BY o.UnitName, p.DeptName, l.SecName, m.SecIDThis is the database script:
USE [Sample]
GO
/****** Object: Table [dbo].[UnitInf] Script Date: 05/11/2017 21:19:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UnitInf](
[UnitID] [int] IDENTITY(1,1) NOT NULL,
[UnitName] [nvarchar](100) NULL,
CONSTRAINT [PK_UnitInf] PRIMARY KEY CLUSTERED
(
[UnitID] 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
SET IDENTITY_INSERT [dbo].[UnitInf] ON
INSERT [dbo].[UnitInf] ([UnitID], [UnitName]) VALUES (1, N'Unit-01')
INSERT [dbo].[UnitInf] ([UnitID], [UnitName]) VALUES (2, N'Unit-02')
INSERT [dbo].[UnitInf] ([UnitID], [UnitName]) VALUES (3, N'Unit-03')
INSERT [dbo].[UnitInf] ([UnitID], [UnitName]) VALUES (4, N'Unit-04')
INSERT [dbo].[UnitInf] ([UnitID], [UnitName]) VALUES (5, N'Unit-05')
SET IDENTITY_INSERT [dbo].[UnitInf] OFF
/****** Object: Table [dbo].[Section] Script Date: 05/11/2017 21:19:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Section](
[secId] [int] IDENTITY(1,1) NOT NULL,
[SecName] [nvarchar](100) NULL,
[UnitName] [int] NULL,
CONSTRAINT [PK_Section] PRIMARY KEY CLUSTERED
(
[secId] 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
SET IDENTITY_INSERT [dbo].[Section] ON
INSERT [dbo].[Section] ([secId], [SecName], [UnitName]) VALUES (1, N'B-001', 1)
INSERT [dbo].[Section] ([secId], [SecName], [UnitName]) VALUES (2, N'C-001', 2)
INSERT [dbo].[Section] ([secId], [SecName], [UnitName]) VALUES (3, N'B-002', 1)
INSERT [dbo].[Section] ([secId], [SecName], [UnitName]) VALUES (4, N'D-004', 4)
SET IDENTITY_INSERT [dbo].[Section] OFF
/****** Object: Table [dbo].[EmpInf] Script Date: 05/11/2017 21:19:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmpInf](
[EmpId] [int] IDENTITY(1,1) NOT NULL,
[DeptId] [int] NULL,
[SecID] [int] NULL,
[EmpName] [nvarchar](100) NULL,
[GrossSal] [float] NULL,
[Desg] [nvarchar](100) NULL,
[SkillBonus] [float] NULL,
[Active] [nvarchar](10) NULL,
[JoinDate] [datetime] NULL,
CONSTRAINT [PK_EmpInf] PRIMARY KEY CLUSTERED
(
[EmpId] 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
SET IDENTITY_INSERT [dbo].[EmpInf] ON
INSERT [dbo].[EmpInf] ([EmpId], [DeptId], [SecID], [EmpName], [GrossSal], [Desg], [SkillBonus], [Active], [JoinDate]) VALUES (1, 2, 2, N'John', 10000, N'Operator', 2000, N'Active', CAST(0x0000A59F00000000 AS DateTime))
INSERT [dbo].[EmpInf] ([EmpId], [DeptId], [SecID], [EmpName], [GrossSal], [Desg], [SkillBonus], [Active], [JoinDate]) VALUES (2, 2, 2, N'Jack', 12000, N'Operator', 5000, N'Active', CAST(0x0000A5BC00000000 AS DateTime))
INSERT [dbo].[EmpInf] ([EmpId], [DeptId], [SecID], [EmpName], [GrossSal], [Desg], [SkillBonus], [Active], [JoinDate]) VALUES (3, 2, 4, N'Nick', 14000, N'Jr. Operator', 6000, N'Active', CAST(0x0000A75100000000 AS DateTime))
INSERT [dbo].[EmpInf] ([EmpId], [DeptId], [SecID], [EmpName], [GrossSal], [Desg], [SkillBonus], [Active], [JoinDate]) VALUES (4, 2, 4, N'Bruce', 15000, N'Operator', 7000, N'Active', CAST(0x0000A79000000000 AS DateTime))
INSERT [dbo].[EmpInf] ([EmpId], [DeptId], [SecID], [EmpName], [GrossSal], [Desg], [SkillBonus], [Active], [JoinDate]) VALUES (5, 2, 1, N'Willy', 16000, N'Jr. Operator', 8000, N'Active', CAST(0x0000A7B800000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[EmpInf] OFF
/****** Object: Table [dbo].[Department] Script Date: 05/11/2017 21:19:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
[DeptId] [int] IDENTITY(1,1) NOT NULL,
[DeptName] [nvarchar](100) NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DeptId] 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
SET IDENTITY_INSERT [dbo].[Department] ON
INSERT [dbo].[Department] ([DeptId], [DeptName]) VALUES (1, N'Admin')
INSERT [dbo].[Department] ([DeptId], [DeptName]) VALUES (2, N'Production')
SET IDENTITY_INSERT [dbo].[Department] OFF -
is the salary annualized? If so, you'll want to find the monthly amt ((gross+skill)/12) and add up the months at that level, will change your avg.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses -
You need to define that columns, and provide some results that you'd like to see. It's hard to guess exactly what you want. If you give us input and output data, that's helpful.
-
Just got it done. Here is the part that I've used in the query for month-wise average salary:
AVG(CASE WHEN (m.GrossSal + m.SkillBns) > 0 AND DATEDIFF(MONTH, m.Joindate, GETDATE()) >= 0 AND DATEDIFF(MONTH, m.Joindate, GETDATE()) <= 6 THEN ISNULL((m.GrossSal + m.SkillBns), 0) END) AS [0 - 6 Months AvgSal]Thanks all for the replies. Have a great day!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply