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