Group by two columns with case query in SQL Server

63 Views Asked by At

I'm trying to retrieve drivers data with Total Accepted and Total Ignored ride requests for the current date.

Based on the Drivers and DriverReceivedRequests tables, I get the total count but the twists is I have duplicate rows that reside on the DriverReceivedRequest table against the driverId and the rideId. So there has to be group by clause on both the driverId and RideId, having the driver getting multiple requests for the current date, but also receiving twice or thrice request for the same ride as well.

This is the table structure for DriverReceivedRequests:

Id          DriverId       RideId         ReceivedStatusId     DateTime
------------------------------------------------------------------------
0014d26b    93665f55      fef6fb96            NULL              04:55.6
00175c65    6e62a94e      cb214a84            NULL              09:32.1
0017c22b    ec9e1297      4b47dc8a          4211357D            10:28:5
0014d26b    6e62a94e      fef6fb96            NULL              04:56.8

This is the query I have tried:

select 
    d.Id, d.FirstName, d.LastName,
    Sum(case when drrs.Number = 1 then 1 else 0 end) as TotalAccepted,
    Sum(case when drrs.Number = 2 or drr.ReceivedStatusId  is null then 1 else 0 end) as TotalRejected
from 
    dbo.[DriverReceivedRequests] drr
inner join 
    dbo.[Drivers] d on drr.DriverId = d.Id
left join 
    dbo.[DriverReceivedRequestsStatus] drrs on drr.ReceivedStatusId = drrs.Id
where 
    Day(drr.Datetime) = Day(getdate())
    and month(drr.DateTime) = Month(getdate())
group by 
    d.FirstName, d.LastName, d.Id

In the above query if I group by with RideId as well, it generates duplicate names of drivers as well with incorrect data. I've also applied partition by clause with DriverId but not the correct result

This also generates the same result

WITH cte AS 
(
    SELECT 
        d.Id, 
        d.FirstName, d.LastName, 
        TotalAccepted = SUM (CASE WHEN drrs.Number = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY drr.DriverId), 
        TotalRejected = SUM (CASE WHEN drrs.Number = 2 OR drr.ReceivedStatusId IS NULL THEN 1 ELSE 0 END) 
                OVER (PARTITION BY drr.DriverId),
        rn = ROW_NUMBER() OVER(PARTITION BY drr.DriverId 
                               ORDER BY drr.DateTime DESC) 
    FROM   
        DriverReceivedRequests drr
    INNER JOIN 
        dbo.[Drivers] d ON drr.DriverId = d.Id
    LEFT JOIN 
        dbo.[DriverReceivedRequestsStatus] drrs ON drr.ReceivedStatusId = drrs.Id
    WHERE  
        DAY (drr.Datetime) = DAY (GETDATE())
        AND MONTH (drr.DateTime) = MONTH (GETDATE())
) 
SELECT 
    Id, 
    FirstName, LastName, 
    TotalAccepted, TotalRejected
FROM
    cte 
WHERE  
    rn = 1 

My question is how can I group by individual driver data in terms of incoming ride requests?

Note: the driver receives same ride request multiple times

0

There are 0 best solutions below