I have a Table which stores all Invoice Header Info which is called arhead. there is 2 columns

i need to query.

duedate and invoiceopen to get the the folowing.

Idealy i would like to have the query to return the folowing with a single query:

Timeframe Open Amount Max Amount Nbr of Invoices Avg Days Past due

i would like to have the result summaries by not due yet, 1 - 30 Days, 31- 60, 61 - 90 Days

and finaly all above 90 Days

right now i a doing th wth single querys like the one below but i dont know how i can get the AVG Days Past Due

SELECT SUM(invoiceopen) AS SumOpen, MAX(invoiceopen) AS MaxOpen,COUNT(*) AS CountOpen

FROM arhead

WHERE (invoiceopen > 0) AND (duedate + 30 <= GETDATE())

SELECT TimeFrame, [Open Amount], [Max Amount], [Nbr of Invoices], [Avg Days Past Due]

FROM (

SELECT CASE WHEN DATEDIFF(DAY, dueDate, GETDATE()) < 1 THEN 'Not due yet'

WHEN DATEDIFF(DAY, dueDate, GETDATE()) BETWEEN 1 AND 30 THEN ' 1 - 30 Days'

WHEN DATEDIFF(DAY, dueDate, GETDATE()) BETWEEN 31 AND 60 THEN '31 - 60 Days'

WHEN DATEDIFF(DAY, dueDate, GETDATE()) BETWEEN 61 AND 90 THEN '61 - 90 Days'

WHEN DATEDIFF(DAY, dueDate, GETDATE()) > 90 THEN 'Over 90 Days'

END AS TimeFrame,

SUM(invoiceOpen) AS [Open Amount],

MAX(invoiceOpen) AS [Max Amount],

SUM(1) AS [Nbr of Invoices],

SUM(CASE WHEN DATEDIFF(DAY, dueDate, GETDATE()) < 1 THEN 0

ELSE DATEDIFF(DAY, dueDate, GETDATE()) END) / SUM(1) AS [Avg Days Past Due]

FROM arHead

GROUP BY CASE WHEN DATEDIFF(DAY, dueDate, GETDATE()) < 1 THEN 'Not due yet'

WHEN DATEDIFF(DAY, dueDate, GETDATE()) BETWEEN 1 AND 30 THEN ' 1 - 30 Days'

WHEN DATEDIFF(DAY, dueDate, GETDATE()) BETWEEN 31 AND 60 THEN '31 - 60 Days'

WHEN DATEDIFF(DAY, dueDate, GETDATE()) BETWEEN 61 AND 90 THEN '61 - 90 Days'

WHEN DATEDIFF(DAY, dueDate, GETDATE()) > 90 THEN 'Over 90 Days' END

) AS derived

ORDER BY CASE TimeFrame WHEN 'Not due Yet' THEN '' ELSE TimeFrame END