Solved

Invoice Past due Query

Posted on 2004-08-02
13
793 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: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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

813 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

13 Experts available now in Live!

Get 1:1 Help Now