I have a derived column in my stored procedure where i process the amount of days in an ordering process
[ORDER PROCESS] = SUM(CASE WHEN (DATEDIFF(dd,convert(datetime,orders.ordershipped,121),getdate()) -
(2 * DATEDIFF(wk,convert(datetime,orders.orderarrived,121),getdate()))) < 2 AND orders.closed <> '' THEN 1 ELSE 0 END)
but i need to find the average amount of days ; when i do ....AVG(SUM(CASE WHEN (DATEDIFF(dd,convert(datetime,orders.ordershipped.... it is not legal syntax as I cannot combine average and sum aggregate functions. Is there another trick for this?
Thanks