SQL Query - query on the current date, but a condition from the past

Question

I am looking for help in the following scenario:

I have a SQL Server database with a table that stores historical data. For example, you can use the following as a set of samples.

CAR, SERVICE DATE, FINDINGS
1234, 21/01/2001, Fuel Filter
1234, 23/09/2009, Oil Change
1234, 30/09/2015, Tyres
3456, 30/09/2015, Clutch

I would like from the following example to return a result that shows the service of any car that was entered on a dating date, for example. 09/30/2015, but only in the case of an oil change in the past.

The request will only return:

1234, 30/09/2015, Tyres

since this is the only car on this date to be services that previously had an oil change.

Any help would be greatly appreciated.

Answer 1

Use EXISTS :

SELECT cur.car,
    cur.[service date],
    cur.findings
FROM tablename cur
WHERE cur.[service date] = @mydate
    AND EXISTS (
        SELECT 1
        FROM tablename past
        WHERE past.car = cur.car
           AND past.[service date] < cur.[service date]
           AND past.findings = 'oil change'
    )

Answer 2

cte.

, , , .

DECLARE @date datetime;

SET @date = '20150930'

with oil_Cte as (
    select distinct car from
    tableName
    where findings = 'oil change'
    and date < @date
)

select *
from tableName
inner join oil_cte on tableName.car = oil_cte.car
where date = @date

Answer 3

• .

• " ". " ", , 1. , OIL Chagne , .

• , , .

.. SQLFiddle

select * from #t where car in (
select car from (
select car, [service date], findings, ROW_NUMBER() over (Partition by car order by [Service Date] desc) as [row]
from (select * from #t where car in (select car from #t where [service date] = '2015-09-30')) A) T
where T.row =2 and Findings = 'Oil Change'
) and [service date] = '2015-09-30'