Finding dates 30-60-90 days in past 1

[NFLDUser]

My same table filled with invoices, has a 'date' field. I'd like to figure out how many invoices I have that are between 30-44, 45-59, 60-89 and 90+ days in the past, but only where 'balance' does not equal 0.

Anyone?

 

[PHV]

Something like this ?
SELECT method, Count(*) AS newvar
, Sum(IIf((Date()-[date]) Between 30 And 44,1,0)) AS [30-44]
, Sum(IIf((Date()-[date]) Between 45 And 59,1,0)) AS [45-59]
, Sum(IIf((Date()-[date]) Between 60 And 89,1,0)) AS [60-89]
, Sum(IIf((Date()-[date])>90,1,0)) AS [90+]
FROM Billing
WHERE balance <> 0
GROUP BY method

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NFLDUser]

Perfect, THANKS...AGAIN!

I did make one minor change to just give me totals ... as if I have receivables, I don't care about their original payment method. Here's the end result.

SELECT Count(*) AS newvar
, Sum(IIf((Date()-[date]) Between 30 And 44,1,0)) AS [30-44]
, Sum(IIf((Date()-[date]) Between 45 And 59,1,0)) AS [45-59]
, Sum(IIf((Date()-[date]) Between 60 And 89,1,0)) AS [60-89]
, Sum(IIf((Date()-[date])>90,1,0)) AS [90+]
FROM Billing
WHERE balance <> 0

 

[NFLDUser]

One followup question to that?

How could I ammend to that query to also include the total amount owing for 30-44, 45-59, 60-89, 90+ by adding up the 'balance' field for each one?

 

[PHV]

SELECT Count(*) AS newvar, Sum(balance) As OverallTotal
, Sum(IIf((Date()-[date]) Between 30 And 44,1,0)) AS [30-44]
, Sum(IIf((Date()-[date]) Between 30 And 44,balance,0)) AS Total1
, Sum(IIf((Date()-[date]) Between 45 And 59,1,0)) AS [45-59]
, Sum(IIf((Date()-[date]) Between 45 And 59,balance,0)) AS Total2
, Sum(IIf((Date()-[date]) Between 60 And 89,1,0)) AS [60-89]
, Sum(IIf((Date()-[date]) Between 60 And 89,balance,0)) AS Total3
, Sum(IIf((Date()-[date])>90,1,0)) AS [90+]
, Sum(IIf((Date()-[date])>90,balance,0)) AS Total4
FROM Billing
WHERE balance <> 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NFLDUser]

Perf. thanks!
Worked