Solved

Invoice Past due Query

Posted on 2004-08-02
13
772 Views
Last Modified: 2008-02-01
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())
0
Comment
Question by:AlexPonnath
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 9

Expert Comment

by:crescendo
ID: 11698232



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
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 11698252
If you could post some sample data, that would help a lot :-).
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 11698276
try this..

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

Author Comment

by:AlexPonnath
ID: 11698341
i get the part on how to get the AVG days past due but this still forces me to actually run 4 Querys to get the info for
the 4 groups i am looking for

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

Expert Comment

by:ScottPletcher
ID: 11698380
I think the query below is close, but haven't tested it yet:


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
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 11698404
nice code @ScottPletcher
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 19

Expert Comment

by:grant300
ID: 11698415
You don't say what the GROUP BY clause is in the sample query you sent.  Is that the entire thing?

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
0
 
LVL 19

Expert Comment

by:grant300
ID: 11698478
Scott and I were typing almost the same thing at the same time.

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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 11698514
This is closer, although it may still need a little tweaking:


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
0
 
LVL 19

Expert Comment

by:grant300
ID: 11698525
You will have to pick your TimeFrame strings in such a way that the coellating sequence orders them the correct way.  Depending on what you want the strings to be, you may find the coellating sequence the server was installed with affects the outcome.

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

Bill
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 11698553
CORRECTION (D'OH; it's very near the end of the work day here):

Replace this:
DAY, GETDATE(), dueDate  --INCORRECT!!

With this:
DAY, dueDate, GETDATE()
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 11698658
Personally, I'd probably use a derived query to make sorting easier to customize.  For example:


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
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 11698699
>> Not sure why he used SUM(1) instead of COUNT(*) <<
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]
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now