SQL – Compare 2 tables with dates [closed]

I have 3 tables with are the following:

st with an id, startDate, endDate, customer_id and serivcedeal_Id.

servicedeal with id and name.

price with id, price, startDate and servicedeal_Id.

I have the following data in the tables:

wpwh_veosoft_crm_st

Table st

wpwh_veosoft_crm_servicedeal

Table servicedeal

wpwh_veosoft_crm_price

Table price

If i have the following data:

table st

startDate = 2018-11-01
endDate = 2019-03-05
customer_Id = 355
Servicedeal_Id = 3

table servicedeal

id = 3 
name = lille pakke

i only want to see the row with id 13 from table price because its the closet date before my startDate 2018-11-01

Does anyone have an SQL that can solve the problem?

thanks in advance

Answer

You can join and filter with a correlated subquery:

select s.*, d.*, p.*
from wpwh_veosoft_crm_st s
inner join wpwh_veosoft_crm_servicedeal d on d.id = s.servicedeal_id
inner join wpwh_veosoft_crm_price p on p.servicedeal_id = s.servicedeal_id
where p.startDate = (
    select max(p1.startDate)
    from wpwh_veosoft_crm_price p1
    where p1.servicedeal_id = s.servicedeal_id and p1.startDate < s.startDate
)

Advertisement

Answer