Trying to get total number of active employees-CodePudding

I am looking to get a count of total number of active employees for 2022 and 2023. Our tables have records on a weekly basis, so when I am pulling the records, its counting an employee 52 times . I am looking to have the query count based on if the person has an active record within the year, to have it only count once. I have includes distinct in the select statement but still getting multiple counts and its not really duplicate so distinct is probable not the solution here.

select distinct count(*)
from TimeSheetsView TSV
inner join Person_Identification PI on PI.PersonId=TSV.Personid
inner join Order_Person_Detail_Record OPDR on OPDR.PersonId=Pi.PersonId and OPDR.DetailRecId=TSV.DetailRecId and OPDR.OrderId=TSV.orderid
where PI.PersonType='AS' and tsv.recordtype='A' and left(yearweek,4) IN ( '2022')
group by PI.PersonId

CodePudding user response：

You shouldn't take "PI" as an alias, since PI is a reserved word. Simply take DISTINCT within the brackets of the COUNT aggregation, you do not need to group:

SELECT 
    COUNT(DISTINCT PI.PersonId) AS "Total Count"
FROM TimeSheetsView TSV
INNER JOIN Person_Identification P ON P.PersonId=TSV.Personid
INNER JOIN Order_Person_Detail_Record OPDR 
    ON OPDR.PersonId=P.PersonId 
    AND OPDR.DetailRecId=TSV.DetailRecId 
    AND OPDR.OrderId=TSV.orderid
WHERE P.PersonType='AS' 
  AND tsv.recordtype='A' 
  AND left(yearweek,4) IN ( '2022');

If you want to see the Totals per Year, you can use:

SELECT 
    LEFT(yearweek,4) AS "Year", 
    COUNT(DISTINCT PI.PersonId) AS "Total Count"
FROM TimeSheetsView TSV
INNER JOIN Person_Identification P ON P.PersonId=TSV.Personid
INNER JOIN Order_Person_Detail_Record OPDR 
    ON OPDR.PersonId=P.PersonId 
    AND OPDR.DetailRecId=TSV.DetailRecId 
    AND OPDR.OrderId=TSV.orderid
WHERE P.PersonType='AS' AND tsv.recordtype='A'
GROUP BY LEFT(yearweek,4);

CodePudding user response：

Do you have any columns that specify that the employee has an active record?

If so you can say:

WHERE 
     ActiveStatus = True AND
     YEAR(DateColumn) <= Year(GetDate()) AND
     YEAR(DateColumn) >= '2022'