Using Microsoft SQL Server, I’m trying to get the average days it took someone to complete a transaction in a given month.
Each user has hundreds of transactions so I’m looking for a way to get the count on how many transactions for each person and then their average for the month. I also need to make sure that I remove any NULL returns and convert any negatives to a Zero but keep it accounted for.
Example would look like (Max | 300 | 12.5) for (Person | Transactions | Average).
I’ve been able to get as far as:
SELECT
[Transaction],
[NAME],
DATEDIFF (d, [Startdate], [Closedate]) AS Days
FROM
[Table]
WHERE
YEAR ([Startdate]) = 2021
AND MONTH ([Closedate]) = 11
AND Closedate IS NOT NULL
I’ve tried to figure out how to incorporate a CASE statement but it’s not working when I tried to do it before the DATEDIFF
.