CODEWITHSUNDEEP
sqlms-access
Arte
Arte

Reputation: 39

Reduce outcomes base on sub query

I'm trying to run that query but I would like to reduce the result only if the previous Mois (months in english) has a minimum of 3 months.

For example, I have the table OtifDataToExport in MS Access with these fields:

╔════════════╦═══════════════════════╦═══════════════════════════════════════════════════════════╗
║  t1.Mois   ║ t1.[Code fournisseur] ║                      t2.ratioOnTime                       ║
╠════════════╬═══════════════════════╬═══════════════════════════════════════════════════════════╣
║ 12/01/2016 ║                     1 ║ 0,5 = Outcomes AVG ratioOnTime 0 ( no 3 previous months)  ║
║ 01/01/2017 ║                     1 ║ 0,5 = Outcomes AVG ratioOnTime 0 ( no 3 previous months)  ║
║ 02/01/2017 ║                     1 ║ 0,5 = Outcomes AVG ratioOnTime 0 ( no 3 previous months)  ║
║ 03/01/2017 ║                     1 ║ 0,5 = Outcomes AVG ratioOnTime 0,5 (  3 previous months)  ║
║ 12/01/2016 ║                     2 ║ 1 = Outcomes AVG ratioOnTime 0 ( no 3 previous months)    ║
║ 01/01/2017 ║                     2 ║ 0,5 = Outcomes AVG ratioOnTime 0 ( no 3 previous months)  ║
║ 02/01/2017 ║                     2 ║ 0,5 = Outcomes AVG ratioOnTime 0 ( no  3 previous months) ║
║ 03/01/2017 ║                     2 ║ 0,5 = Outcomes AVG ratioOnTime 0,66 ( 3 previous months)  ║
║ 03/12/2016 ║                     3 ║ 0,5 = Outcomes AVG ratioOnTime 0 ( no 3 previous months)  ║
╚════════════╩═══════════════════════╩═══════════════════════════════════════════════════════════╝

SELECT  t1.Mois, t1.[Code fournisseur], avg(t2.ratioOnTime) as "Moyenne 3 dernier mois"
FROM
OtifDataToExport AS t1 
left JOIN 
    (
    SELECT t3.mois,t3.[code fournisseur], t3.ratioOnTime
    FROM [OtifDataToExport] as t3
        ) as t2 
ON t1.Mois > t2.Mois and  t1.[Code fournisseur] = t2.[code fournisseur]

group by t1.Mois, t1.[Code fournisseur]

I think I'm closed to, but I can't figure out the solution in MS Access.

Any help is very appreciated.

Upvotes: 0

Views: 78

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

Unless I've misunderstood your requirements, I might suggest either of the following approaches, depending upon whether or not you wish to display those records with fewer than 3 previous months:

select
    t1.mois,
    t1.[code fournisseur],
    avg(t2.ratioontime) as avgratio
from
    otifdatatoexport t1 left join otifdatatoexport t2 on
    t1.mois >= t2.mois and t1.[code fournisseur] = t2.[code fournisseur]
group by
    t1.mois,
    t1.[code fournisseur]
having
    count(*) > 3
order by
    t1.[code fournisseur],
    t1.mois

select
    t1.mois,
    t1.[code fournisseur],
    iif(count(*)>3,avg(t2.ratioontime),0) as avgratio
from
    otifdatatoexport t1 left join otifdatatoexport t2 on 
    t1.mois >= t2.mois and t1.[code fournisseur] = t2.[code fournisseur]
group by
    t1.mois,
    t1.[code fournisseur]
order by
    t1.[code fournisseur],
    t1.mois

To calculate an average using only the last 3 months' worth of data, you could change the join criteria to only include those records within 3 months of the current record, e.g.:

select
    t1.mois,
    t1.[code fournisseur],
    iif(count(*)>3,avg(t2.ratioontime),0) as avgratio
from
    otifdatatoexport t1 left join otifdatatoexport t2 on 
    (t2.mois between dateadd("m",-3,t1.mois) and t1.mois) and t1.[code fournisseur] = t2.[code fournisseur]
group by
    t1.mois,
    t1.[code fournisseur]
order by
    t1.[code fournisseur],
    t1.mois

Or, to use exactly three previous months, you could use something like this:

select
    t1.mois,
    t1.[code fournisseur],
    iif(count(*)=4,avg(t2.ratioontime),0) as avgratio
from
    otifdatatoexport t1 left join otifdatatoexport t2 on 
    t1.mois >= t2.mois and t1.[code fournisseur] = t2.[code fournisseur]
group by
    t1.mois,
    t1.[code fournisseur]
order by
    t1.[code fournisseur],
    t1.mois

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you just want a having clause:

having count(*) >= 3

Upvotes: 0

Related Questions