Count rows in SQL Server table using GROUP BY, First

I think my question is similar to here How to use last_value with group by with count in SQL Server?, however, I can't seem to transcribe the small change to answer my question.

I have table of colleague contracts

ContractId int PK 
ColleagueId int [not null]
ContractStart datetime2 [not null]
ContractEnd datetime2 [null]
BranchId int [not null]
SaturdayOnly bit
isActive bit

What I need to do is get a count, per BranchId of the number of active contracts that are SaturdayOnly i.e. bit 1, and the number of active contracts not SaturdayOnly i.e. bit 0. A colleague can have multiple contracts in the same branch but only one will be active. The final condition is that for the contract to be considered it must start before 2022-12-01 and if there is an end date it must be after 2022-12-01.

I attempted it with this but the 2 cte counts give the same result and the count is incorrect for the branch anyway.

WITH cte AS 
(
    SELECT 
        co.BranchId, co.ContractId, co.ColleagueId,
        ROW_NUMBER() OVER (PARTITION BY co.ColleagueId ORDER BY co.ContractStart DESC) AS row_number
    FROM 
        hr.Contract co    
    WHERE 
        co.SaturdayOnly = 0 
        AND (co.ContractEnd IS NULL OR co.ContractEnd > '2022-12-01')
),
cte_sat AS 
(
    SELECT 
        co.BranchId, co.ContractId, co.ColleagueId,
        ROW_NUMBER() OVER (PARTITION BY co.ColleagueId ORDER BY co.ContractStart DESC) AS row_number
    FROM 
        hr.Contract co    
    WHERE 
        co.SaturdayOnly = 1 
        AND (co.ContractEnd IS NULL OR co.ContractEnd > '2022-12-01')
)
SELECT 
    b.BranchName, 
    COUNT(cte.ContractId), COUNT(cte_sat.ContractId)
FROM 
    hr.Branch b
JOIN 
    cte ON b.ContractorCode = cte.BranchId
JOIN 
    cte_sat ON b.ContractorCode = cte_sat.BranchId
WHERE 
    cte.row_number = 1
GROUP BY 
    b.BranchNumber, b.BranchName
ORDER BY 
    b.BranchNumber

CodePudding user response：

No need for CTE - try

SELECT BranchId, SaturdayOnly, COUNT(*) FROM hr.Contract
WHERE IsActive = 1 AND ContractStart > ... AND ContractEnd < ...
GROUP BY BranchId, SaturdayOnly