Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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())

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 SUM(invoiceopen) AS SumOpen, MAX(invoiceopen) AS MaxOpen, COUNT(*) AS CountOpen, DATEDIFF(d, duedate,GETDATE()) AS DaysLate, AVG(invoiceopen) AS AvgAmount

FROM arhead

WHERE DaysLate <= 30

ORDER BY SumOpen, MaxOpen, CountOpen, DaysLate

SELECT SUM(invoiceopen) AS SumOpen, MAX(invoiceopen) AS MaxOpen,COUNT(*) AS CountOpen,AVG (DATEDIFF(day, duedate, getdate())) as AvgDaysPast

FROM arhead

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

the 4 groups i am looking for

open Invoices, 1 - 30 Days Past, 31- 60 Past, 61 to 90 past and 90 + days...

SELECT CASE WHEN DATEDIFF(DAY, GETDATE(), dueDate) <= 30 THEN ' 1 - 30 Days'

WHEN DATEDIFF(DAY, GETDATE(), dueDate) <= 60 THEN '31 - 60 Days'

WHEN DATEDIFF(DAY, GETDATE(), dueDate) <= 90 THEN '61 - 90 Days'

WHEN DATEDIFF(DAY, GETDATE(), dueDate) > 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(DATEDIFF(DAY, GETDATE(), dueDate)) / SUM(1) AS [Avg Days Past Due]

FROM arHead

GROUP BY TimeFrame

ORDER BY TimeFrame

You can use the CASE statement to compute the psuedo column for the timeframe with the domain {NOT_DUE, 1-30, 31-60, 61-90, >90}

You also have to use the datediff function to reduce things to a simple numerical range comparison.

You can also use the AVG function, along with the appropriate GROUP BY clause to get what you want. Let me see if I can come close:

SELECT (case when duedate >= GETDATE() then 'NOT DUE"

when datediff(day, GETDATE(), duedate) <31 then '1-30'

when datediff(day, GETDATE(), duedate) <61 then '31-60'

when datediff(day, GETDATE(), duedate) <91 then '61-90'

else '>90'

end) AS Timeframe,

SUM(invoiceopen) AS SumOpen,

MAX(invoiceopen) AS MaxOpen,

COUNT(*) AS CountOpen,

AVG((case when duedate > GETDATE() then 0 else datediff(day, GETDATE(), duedate) end))

FROM arhead

WHERE invoiceopen > 0

AND duedate <= dateadd(day, -30, GETDATE())

GROUP BY (case when duedate >= GETDATE() then 'NOT DUE"

when datediff(day, GETDATE(), duedate) <31 then '1-30'

when datediff(day, GETDATE(), duedate) <61 then '31-60'

when datediff(day, GETDATE(), duedate) <91 then '61-90'

else '>90'

end)

Give that a try and see how it works.

Bill

Not sure why he used SUM(1) instead of COUNT(*)

Also, his computed Averge Days Overdue returns a bogus number for the invoices that are "NOT DUE" yet.

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, GETDATE(), dueDate) < 1 THEN 0

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

FROM arHead

--WHERE dueDate BETWEEN '?start' AND '?end'

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

ORDER 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

Scott remembered the ORDER_BY clause that I forgot. Mia culpa.

Bill

Replace this:

DAY, GETDATE(), dueDate --INCORRECT!!

With this:

DAY, dueDate, GETDATE()

Just habit. It seems to be somewhat more efficient in grouping queries.

>> Also, his computed Averge Days Overdue returns a bogus number for the invoices that are "NOT DUE" yet. <<

Indeed, your method of computing AVG days is much cleaner and more logical for someone to read. So replace this:

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

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

with this:

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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