Site icon Dems Extreme Makeover

Get a count and average from a couple rows in the same table?

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.

Exit mobile version