Search code examples
sql-servert-sqlssmsiif

How to set "0.5" value for one field - SSMS t-sql

I have the following code:

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

SELECT 
    glrn.[WHID] as [RegionWHID],
    glrn.Region,
    dol.WHID [DOL Status],
    dol.DOLStatusCode,
    COUNT(DISTINCT tm.PaycomEmployeeFK) AS [CountClients]
FROM 
    [WH].[Fact].[PaycomTimeSheet] tm
INNER JOIN 
    [Dimension].PaycomEmployee empl ON tm.PaycomEmployeeFK = empl.WHID
INNER JOIN 
    [Dimension].PaycomDOLStatus dol ON empl.PaycomDOLStatusFK = dol.WHID
INNER JOIN 
    [Dimension].GLProgram glpgm ON tm.GLProgramFK = glpgm.WHID
INNER JOIN 
    [Dimension].GLRegion glrn ON glpgm.GLRegionFK = glrn.WHID
WHERE 
    (tm.WorkDate >= @MonthBOP) AND (tm.WorkDate <= @MonthEOP)
    AND ((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], glrn.Region, dol.DOLStatusCode, dol.WHID
ORDER BY
    glrn.Region

The result is the following:

enter image description here

It calculates the unique amount of Employees, worked Full / Part time

My goal is to count each "Full Time employee" as 1 (where dol.WHID = 1), which is correct.

But each "Part Time employee" I need to count as "0.5" (where dol.WHID = 2), which is incorrect, as it still counts for "DOLStatus 2" - as 1

Any ideas how will I accomplish this?

Thank you, as always!

Solution

  • you can use SUM with CASE 

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

SELECT 
    glrn.[WHID] as [RegionWHID],
    glrn.Region,
    dol.WHID [DOL Status],
    dol.DOLStatusCode,
    SUM(CASE WHEN dol.WHID = 1 THEN 1.00 WHEN dol.WHID = 2 THEN 0.5 END ) AS [CountClients]
FROM 
    [WH].[Fact].[PaycomTimeSheet] tm
INNER JOIN 
    [Dimension].PaycomEmployee empl ON tm.PaycomEmployeeFK = empl.WHID
INNER JOIN 
    [Dimension].PaycomDOLStatus dol ON empl.PaycomDOLStatusFK = dol.WHID
INNER JOIN 
    [Dimension].GLProgram glpgm ON tm.GLProgramFK = glpgm.WHID
INNER JOIN 
    [Dimension].GLRegion glrn ON glpgm.GLRegionFK = glrn.WHID
WHERE 
    (tm.WorkDate >= @MonthBOP) AND (tm.WorkDate <= @MonthEOP)
    AND ((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], glrn.Region, dol.DOLStatusCode, dol.WHID
ORDER BY
    glrn.Region