>I am trying to calculate the cost of aged receivables in different time ranges. I want the total costs of receivables in the last 30 days, 31-60 days, 61-90 days and 91 and over depending on the date specified which will be most of the time today's date. I don't know how to create a SQL statement that would do this as a new year creates problems in determing the number of days ago it happened. I hope that makes sense. I'm having troubles getting my head around it and explaining it.
>
>TIA,
>Tyler

The new year shouldn't create a problem determining date difference.
Subtracting one date from another will result in the number of days between them.
e.g.

*-- this gets total costs
*     Table: receivables
*               (dDue d, nAmount n(15,2), lReceived)

lnZero = 000000000000.00
ldDate = {^2001-03-01}

If !File("receivables.dbf")
     Create Table receivables (;
          dDue d, nAmount n(15,2), lReceived l)
     For ji = 1 to 1000
          m.dDue = ldDate - Int(Rand()*150)
          m.nAmount = Rand()*9999.99
          m.lReceived = (ji%5=0) && every 5th record
          Insert Into receivables From Memvar
     EndFor
EndIf

Select Sum(IIf(Between(ldDate-ddue,0,30),namount,lnZero)) As n30days, ;
     Sum(IIf(Between(ldDate-ddue,31,60),namount,lnZero)) As n60days, ;
     Sum(IIf(Between(ldDate-ddue,61,90),namount,lnZero)) As n90days, ;
     Sum(IIf(ldDate-ddue>90,namount,lnZero)) As nOver90days ;
     From receivables ;
     Where lreceived=.F. ;
     Into Cursor cuDueAmounts

Browse Last NoWait