Query for parting columns by theys dates

Question

I already asked my question but I will try to give more detail and explication.

So I need to do somme report on a SAP Web Intelligence systems. I can import my data from a weird resource or by SQL directly on db. But if I use the SQL I can't filter or manipulate my data, so I need the SQL extract be perfect.

I'm sorry but I don't know the system of the db, I think's it's an Oracle db.

I give a plan of a part of db for help you:

Data pulled from image

Client
Cocli  Nom
007    Pablo
038    Dora
045    Pedro
069    Petter

Enfact
Numfact  Cocli  Montmonttc (eu)
1        045     13081
2        007    375492
3        038      2347
4        069     34253

Echeanc
Numfact   DateEcheance
1         12/06/2022
2         24/08/2022
3         30/04/2022
4         12/02/2022

I want to do a report with the price in different columns depending on date of due date compared to the date of now.

Currently I have this:

Select ENFACT.NUMFACT, CLIENT.NOM ,ENFACT.MONTMONTTC ,ECHEANC.DATEECHÉANCE AS DATE1, ECHEANC.DATEECHÉANCE AS DATE2 
From CLIENT inner join ENFACT 
on ENFACT.COCLI=CLIENT.COCLI 
inner join  ECHEANC
on ENFACT.NUMFACT=ECHEANC.NUMFACT 
where ENFACT.TYPEFAC='F'

But DATE1 and DATE2 are same, if you have some ideas I'm open.

I give you an example of what I want:

Looks like accounting and wanting ages due of account receivables

                   ------------   DAYS OLD --------------      Total  
Client   Not Due     30      60     90     180   over 180        Due
Pablo    12345                                                 12345
Dora               5645                            543234     548879
Petter              867     987           2345                  4199

Answer 1

It looks like you are doing accounting and trying to get outstanding balances for collections broken out into groups of 30 days. Less than 30, then over 30, 60, 90, 180 and over 180. Not all people may have a balance, but could.

To get the amounts due broken into the different "days old" ranges, use datediff() and compare with curdate() which represents whatever the current date is. This will give you the number of days to fall into the groups.

select
        ByDays.Cocli,
        ByDays.nom,
        sum( case when ByDays.DaysOld < 30 
                then ByDays.Montmonttc else 0 end ) LessThan30Days,
        sum( case when ByDays.DaysOld >= 30 AND ByDays.DaysOld < 60
                then ByDays.Montmonttc else 0 end ) From30To59Days,
        sum( case when ByDays.DaysOld >= 60 AND ByDays.DaysOld < 90 
                then ByDays.Montmonttc else 0 end ) From60To89Days,
        sum( case when ByDays.DaysOld >= 90 AND ByDays.DaysOld < 180 
                then ByDays.Montmonttc else 0 end ) From90To179Days,
        sum( case when ByDays.DaysOld >= 180
                then ByDays.Montmonttc else 0 end ) Over180Days,
        sum( ByDays.Montmonttc ) TotalDue
    from
        (select
                c.cocli,
                c.nom,
                datediff( curdate(), ec.DateEcheance ) DaysOld,
                en.Montmonttc
            from
                client c
                    join enfact en
                        on c.cocli = en.cocli
                        join echeanc ec
                            on en.numfact = ec.numfact ) ByDays
    group by
        ByDays.cocli,
        ByDays.Nom
    order by
        ByDays.Nom

The inner subquery first gathers the data for all records with a pre-computed "how old" the purchase was to make the outer query simpler by just comparing the DaysOld result to the different groups you want.

By grouping by the account ID and name, this will make sure that if you have 5 people named "Pedro", it keeps their respective purchases under their unique ID, not just grouped because they have a common name. So keeping the extra client ID number in the result set can be visually left-out of a final report, but needed to keep distinct context per actual client.