Getting Average number based on UserID-CodePudding

This is a follow up to a previous post. I'm trying to get the average days each person took to close all of their transactions in a month. Each person has 200 transactions each month and we're only going by the open date to Invoice date, so we don't have to worry about a transaction that took place the following month.

I was able to use a CASE statement change anything with a negative number to 0. The result is coming out with every transaction, so now I'm just trying to take each person's numbers, average them and then display them by user name.

Here is what I've got so far

SELECT [Transaction], [UserID], 
       CASE WHEN DATEDIFF (day, [ShippingDt], [InvoiceDate]) < 0 
         THEN 0 
         ELSE DATEDIFF (day, [ShippingDt], [InvoiceDate])
       END AS DTC
FROM Transaction.table
WHERE [ShippingDt] >= '20211101' AND [ShippingDt] < '20211201'
  AND [InvoiceDate] IS NOT NULL

CodePudding user response：

Well, you say you want an average, so AVG seems to be appropriate, and group by UserID:

SELECT [UserID], 
    AVG(CASE WHEN DATEDIFF(day, [ShippingDt], [InvoiceDate]) < 0 
         THEN 0 
         ELSE DATEDIFF(day, [ShippingDt], [InvoiceDate])
       END) AS DTC
FROM Transaction.table
WHERE [ShippingDt] >= '20211101' AND [ShippingDt] < '20211201'
  AND [InvoiceDate] IS NOT NULL 
GROUP BY [UserID];