take sum of a field and automatically divide it in groups by other numeric field like Age. Trying to find an easier way to write below query

Question

Here is a SQL query it aggregates the sum of a field 'called current principal balance' by Age buckets. I am just curious if there is another way to do it.

here is the query

SELECT
[Age Bucket]
,SUM(current_note_amount) As 'Currnet_note_amount'
,COUNT(acctrefno) As 'total number of accts'

FROM 

(
SELECT 
 acctrefno
, L.portfolio_code_id
  ,BOR.dob 
, CASE WHEN datediff(year, BOR.dob, getdate()) >= 10 AND datediff(year, BOR.dob, getdate()) < 20 THEN '10-20'
WHEN datediff(year, dob, getdate()) >= 20  AND datediff(year, dob, getdate()) < 30 THEN '20-30'
WHEN datediff(year, dob, getdate()) >= 30 AND datediff(year, dob, getdate()) < 40 THEN '30-40'
WHEN datediff(year, dob, getdate()) >= 40 AND (datediff(year, dob, getdate())) < 50 THEN '40-50'
WHEN datediff(year, dob, getdate()) >= 50 AND (datediff(year, dob, getdate())) < 60 THEN '50-60'
WHEN datediff(year, dob, getdate()) >= 60 AND (datediff(year, dob, getdate())) < 70 THEN '60-70'
WHEN datediff(year, dob, getdate()) >= 70 AND (datediff(year, dob, getdate())) < 80 THEN '70-80'
WHEN datediff(year, dob, getdate()) >= 80 AND (datediff(year, dob, getdate())) < 90 THEN '80-90'
WHEN datediff(year, dob, getdate()) >= 90 THEN '90+'
END AS 'Age Bucket'
, L.current_note_amount
FROM 

loanacct L 
INNER JOIN cif BOR ON BOR.cifno = L.cifno

WHERE L.portfolio_code_id = 7
) X

GROUP BY 
 [Age Bucket]

FYI, `DATEDIFF(YEAR, dob, GETDATE())` won't give you someone age. For example `DATEDIFF(YEAR, '20201022',GETDATE())` returns 20, however, the person would (today) only be 19 years old. — Thom A, Oct 19 '20 at 18:15
SELECT DATEDIFF(YEAR, '20201022', GETDATE()) will return '0'? what are you trying to say can you please elaborate? — Shreyansh Gokhru, Oct 19 '20 at 20:21
I meant `20001022`, apologies. Force of habit of typing 2020 all the time. The intention is clear, however. — Thom A, Oct 19 '20 at 20:45

score 0 · Accepted Answer · answered Oct 19 '20 at 22:01

The following approach uses the age calculation from this link and refactors the code. Something like this.

with age_cte(acctrefno, portfolio_code_id, dob, current_note_amount, age_bucket) as (
    select acctrefno, l.portfolio_code_id, bor.dob, l.current_note_amount,
           case when age.age between 10 and 19 then '10-19'
                when age.age between 20 and 29 then '20-29'
                when age.age between 30 and 39 then '30-39'
                when age.age between 40 and 49 then '40-49'
                when age.age between 50 and 59 then '50-59'
                when age.age between 60 and 69 then '60-69'
                when age.age between 70 and 79 then '70-79'
                when age.age between 80 and 89 then '80-89'
                when age.age >= 90 then '90+' end as age_bucket
    from loanacct l
         join cif bor on bor.cifno=l.cifno
         cross apply (select (0 + convert(char(8), getdate(),112) - 
                                  convert(char(8), bor.dob,112)) / 10000 as age) age
    where l.portfolio_code_id=7)
select age_bucket, sum(current_note_amount) as [curr_note_amount]
                 , count(acctrefno) as [tot_num_accts]
from age_cte
group by age_bucket;

take sum of a field and automatically divide it in groups by other numeric field like Age. Trying to find an easier way to write below query

1 Answers1