CODEWITHSUNDEEP
sqlsql-server-2000
AT-2017
AT-2017

Reputation: 3149

Employee Month-Wise Service Length Using Sql

I am trying to create employee unit-wise service length report from their joining date and have used the following query to do so:

SELECT o.UnitName, p.DeptName, COUNT(m.EmpId) AS cnt,  

(SELECT COUNT(m.EmpId) FROM EmpInf m
WHERE m.Desg IN ('Jr. Operator', 'Operator') AND m.Active = 'Active' AND m.DeptId = 2
AND DATEDIFF(MONTH, m.Joindate, GETDATE()) BETWEEN 0 AND 6) AS '0 - 6 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

Expected output as below: (As unit 1 and 4 have entry in the year 2017 means during 0 - 6 months of the year 2017 and there will be many others like 7 - 12, 13 - 24 etc)

sample_01
Currently getting this:

sample_02
I guess, having issue with the query and would be glad to know if there are any changes or alternates to do so.

Below is the 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

Upvotes: 0

Views: 143

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

I think that simple conditional aggregation is the best approach:

SELECT o.UnitName, p.DeptName, COUNT(m.EmpId) AS cnt,  
       SUM(CASE WHEN DATEDIFF(MONTH, m.Joindate, GETDATE()) BETWEEN 0 AND 6
                THEN 1 ELSE 0
           END) AS [0 - 6 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

Upvotes: 1

Scott
Scott

Reputation: 3732

I haven't run your code, but it looks like you're hardcoding too much in your subquery. 

(SELECT COUNT(m.EmpId) FROM EmpInf m
WHERE m.Desg IN ('Jr. Operator', 'Operator') AND m.Active = 'Active' AND m.DeptId = 2
AND DATEDIFF(MONTH, m.Joindate, GETDATE()) BETWEEN 0 AND 6) AS '0 - 6 Months'

Problem 1: your alias for EmpInf in the subquery is "m" and your alias for EmpInf in your main query is "m". Make them different so you can link them.

Problem 2: Connect your variables in the subquery to values in the main query. So:

(SELECT COUNT(m.EmpId) FROM EmpInf subm
WHERE subm.Desg=m.Desg AND subm.Active = m.Active AND subm.DeptId = m.DeptId
AND DATEDIFF(MONTH, subm.Joindate, GETDATE()) BETWEEN 0 AND 6) AS '0 - 6 Months'

Upvotes: 1

Related Questions