Count only one record pr day-CodePudding

SELECT COUNT(*)  
FROM dbo.dutyrostershift 
WHERE employeeid = 1931 
  AND DATO BETWEEN '20210901' AND '20220831' 
  AND shifttype IN (0,1,4,5,6,15,16,20)

In short it asks about how many jobs one employee has got of some types of work in a given period.

It's used for counting days with work.

Which means, that if there is two or more records with the same date, it should only count 1 for that day.

CodePudding user response：

Instead of counting the rows, count the distinct dates in that period. ie:

SELECT COUNT(distinct cast(dato as date))  
FROM dbo.dutyrostershift 
WHERE employeeid = 1931 AND 
     DATO BETWEEN '20210901' AND '20220831' AND 
     shifttype IN (0,1,4,5,6,15,16,20);

CodePudding user response：

Why did i not think of that? Easy and working - Thanks!