create a query

[teachmecr]

I have a table called Invoice. It has fields such as
Invoicenum duedate Amount and status. The status could be open or close meaning
whether the invoice has been paid or not. What i basically want to achieve is to
find which invoice is delinquent by (1-30days,31-60,61-90,91-120 and >120).
What happens is if there are open invoices for instance in the month of December
and January and the customer made a payment in february for just one month. That payment
goes towards February invoice leaving December and jan open whearas its supposed to be applied
towards December invoice but its a bug in the system. However i want to create a report to
calculate true delinquency. Let me clarify by the following example

Invoicenum duedate amount status
100 20071001 500 o
101 20071101 500 c
102 20071201 500 c
103 20080101 500 o
104 20080201 500 o

Now my query or report shud display the above results as follows

InvoiceNum delinquency Amount
100 61-90 500
103 31-60 500
104 1-30 500

The open and closed invoices might appear in different variations meaning there could be
10 open invoices between 2 closes invoices or anything like that.

In other words I just want to be able to list all the open invoices 30 days apart from each otther.
I just somehow can't get the right logic for it to work in all cases. If i need to
do it thru a stored procedure give any suggestions please. I hope I explained it
clearly if not i'll try to elaborate on it more. thanks for your help.

 

[gmmastros]

Try this...

Select InvoiceNum,
       Case When DateDiff(Day, DueDate, GetDate()) < 31 Then '1-30'
            When DateDiff(Day, DueDate, GetDate()) < 61 Then '31-60'
            When DateDiff(Day, DueDate, GetDate()) < 91 Then '61-90'
            When DateDiff(Day, DueDate, GetDate()) < 121 Then '91-120'
            Else '> 120'
            End,
       Amount
From   Invoice
Where  Status = 'o'
       And DueDate < GetDate()

If this works for you, and you would like me to explain it, just let me know.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

DECLARE @Test TABLE (Id int IDENTITY(1,1), Invoice int, DueDate datetime, Amount Numeric (10,2))

INSERT INTO @Test (Invoice, DueDate, Amount)
SELECT Invoice, DueDate, Amount
       FROM YourTable
WHERE Status = 'O'
ORDER BY Invoice

SELECT Tbl1.Invoice,
       CASE WHEN DATEDIFF(dd,Tbl1.DueDate, Tbl2.DueDate) > 120
            THEN '>120'
       CASE WHEN DATEDIFF(dd,Tbl1.DueDate, Tbl2.DueDate) > 90
            THEN '91-120'
       CASE WHEN DATEDIFF(dd,Tbl1.DueDate, Tbl2.DueDate) > 60
            THEN '61-90'
       CASE WHEN DATEDIFF(dd,Tbl1.DueDate, Tbl2.DueDate) > 30
            THEN '31-60'
       ELSE '0-30' END AS Delinquency,
       Tbl1.Amount
FROM @Test Tbl1
LEFT JOIN @Test Tbl2 ON (Tbl1.Id+1) = Tbl2.Id

NOT TESTED AT ALL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[teachmecr]

No this is not what i'm looking for...my question is complicated than just counting the difference between two dates..thanks

 

[bborissov]

Then what?
Explain it slowly because I read slowly

What is wrong with George's or mine query?

How you define that Invoice No 100 is in 61-90 interval?


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

Really???

If you want more help, then I would encourage you to explain your problem in a little more detail.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[teachmecr]

Every first closed invoice gets applied towards the first open invoice thus temporarily becoming an open invoice itself and so on. so at the end you get all the open invoices in sequence 30 days apart from each other...i hope this makes it clear. thanks

InvoiceNum delinquency Amount due date
100 61-90 500 20071201
103 31-60 500 20080101
104 1-30 500 20080201