Jonathan JC Jonathan JC - 5 months ago 13
SQL Question

SQL Query to sum buckets

I have a table with inovices and a field named

Arrers
days and I need to count how many items are into 0-30 days also 30-90 and 90-120

Basically what I need is to calcuate the aging of my client portfolio.

so far I have this:

SELECT SUM(CASE WHEN Receivable.Arrers>'0'<'30' THEN 1 ELSE 0) AS 0-30,
SUM(CASE WHEN Receivable.Arrers<'30'>'60' THEN 1 ELSE 0) AS 30-60,
SUM(CASE WHEN Receivable.Arrers<'90'>'120' THEN 1 ELSE 0) AS 90-120
From Receivable


Table Name Receivable

Invoice Arrers
89859 10
89856 3
89853 11

Answer

Access SQL does not support CASE WHEN. You can use an IIf expression instead.

SELECT
    SUM(IIf(r.Arrers BETWEEN  0 AND  30, 1, 0)) AS [0-30],
    SUM(IIf(r.Arrers BETWEEN 31 AND  60, 1, 0)) AS [31-60], 
    SUM(IIf(r.Arrers BETWEEN 90 AND 120, 1, 0)) AS [90-120] 
FROM Receivable AS r;

The example in your question ignores Arrers from 61 to 89, so I did, too. But you can add in another column expression if needed.

If Arrers is text instead of numeric datatype, you can use Val() to cast the text values to numbers ...

SUM(IIf(Val(r.Arrers) BETWEEN  0 AND  30, 1, 0)) AS [0-30]