Query for Turnover Report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query for Turnover Report

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-03-30 : 16:37:38

I am trying to create an employee turnover report but have no idea on how to create the query.

For example purposes, there is an EmployeeDateDetails table like the following.
ID EmpID HiredDate TerminationDate(NULL)
1 10 2011-01-01 NULL
2 11 2011-02-01 2011-03-25
3 12 2011-03-01 NULL
4 13 2011-04-01 NULL

I want to have a query that will return results something like this:

Date OpenBal #Joined #Left ClosingBalance
Jan 2011 0 1 0 1
Feb 2011 1 1 0 2
Mar 2011 2 1 1 2
Apr 2011 2 1 0 3
May 2011 3

My sql skills are fairly limited and don't even know where to start with something like this. Any help would be greatly appreciated.

Thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-03-30 : 19:51:38

[CODE]declare
@StartDate Date

declare @table table (
ID int identity(1, 1),
EmpID varchar(20),
HiredDate date,
TerminationDate date null
)

insert into @table (
EmpID,
HiredDate,
TerminationDate
)
values
('10', '2011-01-01', NULL),
('11', '2011-02-01', '2011-03-25'),
('12', '2011-03-01', NULL),
('13', '2011-04-01', NULL)

select @StartDate = min(DateAdd(month, DateDiff(month, 0, HiredDate), 0))
from @table

;with MonthDates -- Convert dates to first of month
as (
select
cast(DateAdd(month, DateDiff(month, 0, HiredDate), 0) as date) HiredDate,
cast(DateAdd(month, DateDiff(month, 0, TerminationDate), 0) as date) TerminationDate
from @table
),
MonthDatesAggregated -- Aggregate counts (hired/fired) per month
as (
select
HiredDate RptYearMonth,
count(HiredDate) HiredCount,
count(TerminationDate) TerminatedCount
from
MonthDates
group by
HiredDate
),
Turnover
as (
select
@StartDate RptYearMonth,
0 OpeningHeadCount,
HiredCount,
TerminatedCount,
HiredCount - TerminatedCount ClosingHeadCount
from MonthDatesAggregated
where RptYearMonth = @StartDate

union all

select
cast(DateAdd(month, 1, t.RptYearMonth) as date),
ClosingHeadCount,
m.HiredCount,
m.TerminatedCount,
ClosingHeadCount + m.HiredCount - m.TerminatedCount
from Turnover t
inner join
MonthDatesAggregated m
on t.RptYearMonth = m.RptYearMonth
)
select *
from Turnover
order by RptYearMonth[/CODE]

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-03-31 : 12:06:13

Thank you so much Bustaz!
This is amazing, I have been struggling with this for a couple days and have gotten nowhere. Thanks for your help!

I have been messing around with the query, but am still having a couple issues.

If there is a gap between hired dates, like this for example:

insert into @table (
EmpID,
HiredDate,
TerminationDate
)
values
('10', '2011-01-01', NULL),
('11', '2011-04-01', '2011-05-25'),
('12', '2011-05-01', NULL),
('13', '2011-06-01', NULL)

The results are only returned for the first date, and the next month includes a HiredCount that doesn't exist, like this:
2011-01-01 0 1 0 1
2011-02-01 1 1 0 2

Would it be possible to fill the gaps so it returned like this?:
2011-01-01 0 1 0 1
2011-02-01 1 0 0 1
2011-03-01 1 0 0 1
2011-04-01 1 1 0 2
2011-05-01 2 1 1 1
2011-06-01 1 1 0 2
2011-07-01 2 1 0 3
2011-08-01 3 0 0 3

And if the start and end dates were provided as parameters could the query filter between those dates but include an OpeningHeadCount for employees hired before the startdate. For example, if startdate = '2011-05-01' and enddate = '2011-09-01' the results would be:
2011-05-01 2 1 1 2
2011-06-01 2 1 0 3
2011-07-01 3 0 0 3
2011-08-01 3 0 0 3
2011-09-01 3 0 0 3

Thanks again for your help.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-03-31 : 16:41:35

I thought about gaps when putting this together but didn't want to make things too complicated. The solution I had in mind was to use a Calendar table. The Calendar would insure that every month was represented in the date range of interest. In short, the approach would join the Calendar to your data table. Use an outer join so that if there is no personnel activity, you'll still get the Calendar data. This could be done in the "with MonthDates" section.

You'll need to:
1) Create a Calendar table of months (or a real Calendar and only use the first of the month)
2) OUTER JOIN the data to the Calendar in the MonthDates section
3) GROUP BY Calendar.YearMonth instead of HiredDate in the MonthDatesAggregated section

HTH

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-04-05 : 01:26:15

Almost have this working as it should, but there are a couple small issues still.
The first issue is when the first employee hire is counted. In this example it's on 2011-01-01. It's counted correctly in the first row of the returned results, but then the very next month has a hired count of 1 as well, when it should be 0.

The second issue is that the hired/terminated values are added to the next month. In this example there are 2 new hires on 2011-03-01
but the results show them as hired in 2011-04

The results from this example should be:
Date OpeningBalance Hired Terminated ClosingBalance
2011-01-01 0 1 0 1
2011-02-01 1 0 0 1
2011-03-01 1 2 0 3
2011-04-01 3 1 0 4
2011-05-01 4 1 0 5
2011-06-01 5 0 1 4
2011-07-01 4 0 0 4
2011-08-01 4 0 0 4
2011-09-01 4 3 0 7
2011-10-01 7 0 0 7
2011-11-01 7 0 0 7
2011-12-01 7 0 0 7
2012-01-01 7 0 0 7

Declare @pStartDate date = '2011-01-01'
Declare @pEndDate date = '2012-01-01'

declare @Employee table (
HiredDate date,
TerminationDate date null
)

insert into @Employee (
HiredDate,
TerminationDate
)
values
('2011-01-01', NULL),
('2011-03-01', NULL),
('2011-03-01', NULL),
('2011-04-01', '2011-06-25'),
('2011-05-01', NULL),
('2011-09-01', NULL),
('2011-09-01', NULL),
('2011-09-01', NULL)

declare @Calendar table (
YearMonth date
)

insert into @Calendar (
YearMonth
)
values
('2011-01-01'),
('2011-02-01'),
('2011-03-01'),
('2011-04-01'),
('2011-05-01'),
('2011-06-01'),
('2011-07-01'),
('2011-08-01'),
('2011-09-01'),
('2011-10-01'),
('2011-11-01'),
('2011-12-01')

;WITH MonthDates -- Convert dates to first of month
AS (
SELECT
CAST(DateAdd(month, DateDiff(month, 0, HiredDate), 0) as date) HiredDate,
CAST(DateAdd(month, DateDiff(month, 0, TerminationDate), 0) as date) TerminationDate
FROM @Employee E
),
MonthDatesAggregated -- Aggregate counts (hired/fired) per month
AS (
SELECT
YearMonth AS "Date",
Hired = SUM(CASE WHEN YearMonth = MD1.HiredDate THEN 1 ELSE 0 END),
Terminated = SUM(CASE WHEN YearMonth = MD1.TerminationDate THEN 1 ELSE 0 END)
FROM
@Calendar LEFT JOIN MonthDates AS MD1 ON YearMonth = MD1.HiredDate OR
YearMonth = MD1.TerminationDate
WHERE
YearMonth Between @pStartDate AND @pEndDate
GROUP BY
YearMonth
),
Turnover
AS (
SELECT
@pStartDate AS "Date",
(SELECT COUNT(*) FROM @Employee WHERE TerminationDate IS Null AND HiredDate < @pStartDate) AS OpeningBalance,
Hired,
Terminated,
((SELECT COUNT(*) FROM @Employee WHERE TerminationDate IS Null AND HiredDate < @pStartDate) + Hired) - Terminated AS ClosingBalance
FROM MonthDatesAggregated
WHERE "Date" = @pStartDate

UNION all

SELECT
CAST(DateAdd(month, 1, t.Date) as date),
ClosingBalance,
m.Hired,
m.Terminated,
ClosingBalance + m.Hired - m.Terminated
FROM Turnover t
INNER JOIN
MonthDatesAggregated m
on t.Date = m.Date
)
SELECT *
FROM Turnover
ORDER BY "Date"

Thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-04-05 : 20:33:47

Here's my latest. The Terminated count was correct but was assigned to the wrong month. I finally decided that we needed to group by two different entities. I also corrected the join condition into the recursive cte.[CODE]declare
@StartDate Date

declare @table table (
ID int identity(1, 1),
EmpID varchar(20),
HiredDate date,
TerminationDate date null
)

insert into @table (
EmpID,
HiredDate,
TerminationDate
)
values
('10', '2011-01-01', NULL),
('11', '2011-04-01', '2011-05-25'),
('12', '2011-05-01', NULL),
('13', '2011-06-01', NULL)

declare @Calendar table (
YearMonth date
)

insert into @Calendar (
YearMonth
)
values
('2011-01-01'),
('2011-02-01'),
('2011-03-01'),
('2011-04-01'),
('2011-05-01'),
('2011-06-01'),
('2011-07-01'),
('2011-08-01'),
('2011-09-01'),
('2011-10-01'),
('2011-11-01'),
('2011-12-01')

select @StartDate = min(DateAdd(month, DateDiff(month, 0, HiredDate), 0))
from @table

;with MonthDates -- Convert dates to first of month
as (
select
cast(DateAdd(month, DateDiff(month, 0, HiredDate), 0) as date) HiredDate,
cast(DateAdd(month, DateDiff(month, 0, TerminationDate), 0) as date) TerminationDate
from @table
),
MonthDatesAggregated -- Aggregate counts (hired/fired) per month
as (
select a.RptYearMonth, a.HiredCount, b.TerminatedCount
from
(
select
c.YearMonth RptYearMonth,
count(HiredDate) HiredCount
from
@Calendar c
left outer join
MonthDates md
on c.YearMonth = md.HiredDate
group by
c.YearMonth
) a

inner join

(
select
c.YearMonth RptYearMonth,
count(TerminationDate) TerminatedCount
from
@Calendar c
left outer join
MonthDates md
on c.YearMonth = md.TerminationDate
group by
c.YearMonth
) b
on a.RptYearMonth = b.RptYearMonth
),
Turnover
as (
select
@StartDate RptYearMonth,
0 OpeningHeadCount,
HiredCount,
TerminatedCount,
HiredCount - TerminatedCount ClosingHeadCount
from MonthDatesAggregated
where RptYearMonth = @StartDate

union all

select
cast(DateAdd(month, 1, t.RptYearMonth) as date),
ClosingHeadCount,
m.HiredCount,
m.TerminatedCount,
ClosingHeadCount + m.HiredCount - m.TerminatedCount
from Turnover t
inner join
MonthDatesAggregated m
on cast(DateAdd(month, 1, t.RptYearMonth) as date) = m.RptYearMonth
)
select *
from Turnover
order by RptYearMonth[/CODE]Over and out...

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-04-06 : 11:26:13

Here's my latest. The Terminated count was correct but was assigned to the wrong month. I finally decided that we needed to group by two different entities. I also corrected the join condition in the recursive cte.[CODE]declare
@StartDate Date

declare @table table (
ID int identity(1, 1),
EmpID varchar(20),
HiredDate date,
TerminationDate date null
)

insert into @table (
EmpID,
HiredDate,
TerminationDate
)
values
('10', '2011-01-01', NULL),
('11', '2011-04-01', '2011-05-25'),
('12', '2011-05-01', NULL),
('13', '2011-06-01', NULL)

declare @Calendar table (
YearMonth date
)

insert into @Calendar (
YearMonth
)
values
('2011-01-01'),
('2011-02-01'),
('2011-03-01'),
('2011-04-01'),
('2011-05-01'),
('2011-06-01'),
('2011-07-01'),
('2011-08-01'),
('2011-09-01'),
('2011-10-01'),
('2011-11-01'),
('2011-12-01')

select @StartDate = min(DateAdd(month, DateDiff(month, 0, HiredDate), 0))
from @table

;with MonthDates -- Convert dates to first of month
as (
select
cast(DateAdd(month, DateDiff(month, 0, HiredDate), 0) as date) HiredDate,
cast(DateAdd(month, DateDiff(month, 0, TerminationDate), 0) as date) TerminationDate
from @table
),
MonthDatesAggregated -- Aggregate counts (hired/fired) per month
as (
select a.RptYearMonth, a.HiredCount, b.TerminatedCount
from
(
select
c.YearMonth RptYearMonth,
count(HiredDate) HiredCount
from
@Calendar c
left outer join
MonthDates md
on c.YearMonth = md.HiredDate
group by
c.YearMonth
) a

inner join

(
select
c.YearMonth RptYearMonth,
count(TerminationDate) TerminatedCount
from
@Calendar c
left outer join
MonthDates md
on c.YearMonth = md.TerminationDate
group by
c.YearMonth
) b
on a.RptYearMonth = b.RptYearMonth
),
Turnover
as (
select
@StartDate RptYearMonth,
0 OpeningHeadCount,
HiredCount,
TerminatedCount,
HiredCount - TerminatedCount ClosingHeadCount
from MonthDatesAggregated
where RptYearMonth = @StartDate

union all

select
cast(DateAdd(month, 1, t.RptYearMonth) as date),
ClosingHeadCount,
m.HiredCount,
m.TerminatedCount,
ClosingHeadCount + m.HiredCount - m.TerminatedCount
from Turnover t
inner join
MonthDatesAggregated m
on cast(DateAdd(month, 1, t.RptYearMonth) as date) = m.RptYearMonth
)
select *
from Turnover
order by RptYearMonth[/CODE]Over and out...

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources