Myujth

My goal is to calculate the number of Full-Time Equivalent personnel (FTE) - Full and Part time - per working period

I have the following code to calculate the number of employees FT / PT groupped by Region and Status (FT=1/PT=2):

Code 1 (Main):

DECLARE @MonthBOP date = '07-01-2017'
DECLARE @MonthEOP date = '09-30-2018'
DECLARE @GLRegionsWHID int = 705
DECLARE @GLProgramsWHID int = -1

SELECT 
 glrn.WHID AS [RegionID]
 ,dol.WHID AS [StatusType]
 ,COUNT (*) AS [CountClients]

FROM [WH].[Dimension].[PaycomEmployee] empl
INNER JOIN Dimension.PaycomDOLStatus dol ON empl.PaycomDOLStatusFK = dol.WHID
INNER JOIN Dimension.PaycomDepartment dept ON empl.PaycomDepartmentFK = dept.WHID
INNER JOIN Dimension.GLProgram glpgm ON dept.GLProgramFK = glpgm.WHID
INNER JOIN Dimension.GLRegion glrn ON glpgm.GLRegionFK = glrn.WHID

WHERE 

((dol.WHID IN (1,2)))
AND ((glrn.WHID IN (@GLRegionsWHID)) OR (-1 IN (@GLRegionsWHID)))
AND ((glpgm.WHID IN (@GLProgramsWHID)) OR (-1 IN (@GLProgramsWHID)))

GROUP BY
 glrn.WHID
 ,dol.WHID

The result is the following (below):

But I also have another code part, which determines - the employee working period:

Code 2:

Min(@MonthEOP, empl.DateTerminated) - Max(@MonthBOP, empl.DateHired) + 1 =
 [Number Days employed in Period]
WHERE empl.DateTerminated > @MonthBOP 

My question is - How to combine correctly the main code (Code 1) and the additional part (Code 2) - so it would be all in one code
or - any sort of reference in Code 1 into Code 2

My goal is to determine the Employee Working Period and to bring it into
Code 1 (Main)

Thank you for help!

You can't use MIN() or MAX() like you do in code2.

Use CASE instead.

CASE
 WHEN @MonthBOP > empl.DateTerminated THEN @MonthBOP
 WHEN @MonthEOP < empl.DateTerminated THEN @MonthEOP
 ELSE empl.DateTerminated
END
-
CASE
 WHEN @MonthEOP < empl.DateHired THEN @MonthEOP
 WHEN @MonthBOP > empl.DateHired THEN @MonthBOP
 ELSE empl.DateHired
END
 AS [Number Days employed in Period]