Solved

Invoice Past due Query

Posted on 2004-08-02
13
818 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Scott Pletcher
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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:Scott Pletcher
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
 
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:Scott Pletcher
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:Scott Pletcher
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:
Scott Pletcher 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:Scott Pletcher
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 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