i'm having a hard time writing a SQL command to return the minimum date from a table.
Table 1 (planning) contains the conditions when "HP" is met for each term.
Table 2 (realisation) contains whether or not an address is HP or not.
So the endresult should be the minimum date from the realisation table when the sum of ishP reaches the terms corresponding to that contractor.

Planning

Realisation

I really tried multiple SQL commands, but I can't seem to get it done. Can anyone tell me how i should do this?

i tried variations like this one, but it didn't give the expected result:

SELECT
p.*
, (select min(realisation_date) from realisation r where r.contractor = p.contractor having sum(isHP) >= p.term)
FROM planning p

expected result

CodePudding user response：

I did not fully understand your question, the context is not clear. Although the below query can help you I believe.

SELECT planning.* 
    FROM 
    planning,
    (SELECT contractor, sum(isHp), min(realisation_date) FROM realisation
     GROUP BY
        isHp,
        contractor,
        realisation_date
    ) AS filter_realisatioN
    WHERE
      planning.contractor = filter_realisation.contractor
      AND filter_realisation.isHp >= planning.term

CodePudding user response：

Join the tables and compute the running total per contractor per term. Then aggregate to find the date where threshold is met.

with data as (
    select p.term, p.min_HP, r.contractor, r.realisation_date,
        sum(r.isHP) over (
            partition by p.term, r.contractor order by r.realisation_date) as HP
    from realisation r inner join planning p on p.contractor = r.contractor
)
select term, min(min_HP) as min_HP, contractor,
    min(case when HP = min_HP then realisation_date end) as "date"
from data
group by term, contractor

This will return a row for each term and contractors regardless of whether "min_HP" is ever matched.

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4066c526d9aa68057ead38628ef8f8c5