select
   Contract.*,
   Contragent.*,
   Doc.*,
   Vehicle.*
from
   Contract
   join Contragent on (Contragent.Id=Contract.ContragentId)
   join Doc on (Doc.Id=Contragent.DocId)
   join Vehicle on (Vehicle.Id=Contract.VehicleId)
where
   (ContractId=SmthValue)
   and (Contract.Date between Contragent.DateFrom and Contragent.DateTo)
   and (Contract.Date between Doc.DateFrom and Doc.DateTo)
   and (Contract.Date between Vehicle.DateFrom and Vehicle.DateTo)

select
   max(C.GenID)
from
   Contragents C
where
   (C.RecordModify<:ContractDate)
   and (C.ContragentID=:ContractID)

select
    Contract.*,
    Contragent.*,
    Doc.*,
    Vehicle.*
from
    Contract
    join Contragent on (Contragent.Id=select max(Id) from Contragent where RecordModify<Contract.Date and Id=Contract.ContragentId)
    join Doc on (Doc.Id=select max(Id) from Doc where RecordModify<Contract.Date and Id=Contragent.DocId)
    join Vehicle on (Vehicle.Id=select max(Id) from Vehicle where RecordModify<Contract.Date and Id=Contract.VehicleId)
where
    (ContractId=SmthValue)

create table ContragentMasterWGenIdDate
(
    Id numeric( 18 , 0 ),
    GenId numeric( 18 , 0 )
)

create table ContragentDetailWGenIdDate
(
    GenId numeric( 18 , 0 ) identity,
    Name varchar( 1024 ),
    RecordModify datetime
)

select
   CM.Id,
   max(CM.GenId) as GenId,
from
   ContragentMasterWGenIdDate CM
group by
   CM.Id
order by
   CM.Id

select
   CM.Id,
   max(CM.GenId) as GenId,
   CD.Name,
   CD.RecordModify
from
   ContragentMasterWGenIdDate CM
   join ContragentDetailWGenIdDate CD on (CD.GenId=CM.GenId) and (CD.RecordModify<='20090329')
group by
   CM.Id
having
   CD.GenId=max(CM.GenId)
order by
   CM.Id