SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [Routes](
    [RouteID] [int] NOT NULL,
    [Type] [varchar](50) NOT NULL,
    [StartDate] [datetime] NOT NULL,
);

CREATE INDEX RouteDateIDX
ON Routes (RouteID, StartDate);

insert into Routes(RouteID, Type, StartDate) values(301, 'Standard', '20150101');
insert into Routes(RouteID, Type, StartDate) values(301, 'Discontinued', '20160601');
insert into Routes(RouteID, Type, StartDate) values(302, 'Standard', '20150101');
insert into Routes(RouteID, Type, StartDate) values(302, 'ParaTrans', '20170101');

CREATE TABLE Transactions(
    TRANSIT_DAY date NOT NULL,
    ROUTE_ID int NOT NULL,
);

INSERT INTO Transactions(TRANSIT_DAY, ROUTE_ID) VALUES('20150420', 301);
INSERT INTO Transactions(TRANSIT_DAY, ROUTE_ID) VALUES('20180120', 301);
INSERT INTO Transactions(TRANSIT_DAY, ROUTE_ID) VALUES('20130420', 301);
INSERT INTO Transactions(TRANSIT_DAY, ROUTE_ID) VALUES('20150420', 302);
INSERT INTO Transactions(TRANSIT_DAY, ROUTE_ID) VALUES('20180120', 302);
INSERT INTO Transactions(TRANSIT_DAY, ROUTE_ID) VALUES('20130420', 302);

10 rows affected

TRANSIT_DAY	ROUTE_ID	NCTD_MODE
20/04/2015 00:00:00	301	Standard
20/01/2018 00:00:00	301	Discontinued
20/04/2013 00:00:00	301	null
20/04/2015 00:00:00	302	Standard
20/01/2018 00:00:00	302	ParaTrans
20/04/2013 00:00:00	302	null

Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.3.1" Build="12.0.5000.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT TRANSIT_DAY, ROUTE_ID, (SELECT TOP (1) Type FROM Routes WHERE (RouteID = Transactions.ROUTE_ID) AND (StartDate <= Transactions.TRANSIT_DAY) ORDER BY StartDate DESC) AS NCTD_MODE FROM Transactions" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0114872" StatementEstRows="6" StatementOptmLevel="FULL" QueryHash="0x32D6E1F54F3BAC15" QueryPlanHash="0x7966BE4B2E07AF82" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="140" CompileCPU="140" CompileMemory="240"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209662" EstimatedPagesCached="13103" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="424432"/><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6" EstimateIO="0" EstimateCPU="6e-007" AvgRowSize="43" EstimatedTotalSubtreeCost="0.0114872" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="TRANSIT_DAY"/><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="ROUTE_ID"/><ColumnReference Column="Expr1007"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"/><ScalarOperator ScalarString="[fiddle_421c4459871248ae89d661dc39cac4b8].[dbo].[Routes].[Type]"><Identifier><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="Type"/></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="6" EstimateIO="0" EstimateCPU="2.508e-005" AvgRowSize="43" EstimatedTotalSubtreeCost="0.0114866" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="TRANSIT_DAY"/><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="ROUTE_ID"/><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="Type"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="TRANSIT_DAY"/><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="ROUTE_ID"/></OuterReferences><RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="6" EstimateIO="0.003125" EstimateCPU="0.0001636" AvgRowSize="14" EstimatedTotalSubtreeCost="0.0032886" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="TRANSIT_DAY"/><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="ROUTE_ID"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="TRANSIT_DAY"/></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="ROUTE_ID"/></DefinedValue></DefinedValues><Object Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" IndexKind="Heap" Storage="RowStore"/></TableScan></RelOp><RelOp NodeId="3" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="36" EstimatedTotalSubtreeCost="0.00817288" Parallel="0" EstimateRebinds="4.73496" EstimateRewinds="0.265043" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="Type"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="2" ActualExecutions="6"/></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"/></ScalarOperator></TopExpression><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="44" EstimatedTotalSubtreeCost="0.00817228" Parallel="0" EstimateRebinds="4.73496" EstimateRewinds="0.265043" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="Type"/><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="StartDate"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="2" ActualExecutions="6"/></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Bmk1003"/></OuterReferences><RelOp NodeId="5" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0040736" TableCardinality="4" Parallel="0" EstimateRebinds="4.73496" EstimateRewinds="0.265043" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1003"/><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="StartDate"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="2" ActualExecutions="6"/></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Expr1009"/><ColumnReference Column="Expr1010"/><ColumnReference Column="Expr1008"/></OuterReferences><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="27" EstimatedTotalSubtreeCost="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1009"/><ColumnReference Column="Expr1010"/><ColumnReference Column="Expr1008"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="2" ActualExecutions="6"/></RunTimeInformation><ComputeScalar><DefinedValues><DefinedValue><ValueVector><ColumnReference Column="Expr1009"/><ColumnReference Column="Expr1010"/><ColumnReference Column="Expr1008"/></ValueVector><ScalarOperator ScalarString="GetRangeWithMismatchedTypes(NULL,[fiddle_421c4459871248ae89d661dc39cac4b8].[dbo].[Transactions].[TRANSIT_DAY],(42))"><Intrinsic FunctionName="GetRangeWithMismatchedTypes"><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="TRANSIT_DAY"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(42)"/></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="0" EstimatedTotalSubtreeCost="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="2" ActualExecutions="6"/></RunTimeInformation><ConstantScan/></RelOp></ComputeScalar></RelOp><RelOp NodeId="11" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0040736" TableCardinality="4" Parallel="0" EstimateRebinds="4.73496" EstimateRewinds="0.265043" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1003"/><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="StartDate"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" ActualElapsedms="0" ActualCPUms="0" ActualScans="6" ActualLogicalReads="12" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="2" ActualExecutions="6"/></RunTimeInformation><IndexScan Ordered="1" ScanDirection="BACKWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Column="Bmk1003"/></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="StartDate"/></DefinedValue></DefinedValues><Object Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Index="[RouteDateIDX]" IndexKind="NonClustered" Storage="RowStore"/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="RouteID"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_421c4459871248ae89d661dc39cac4b8].[dbo].[Transactions].[ROUTE_ID]"><Identifier><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Transactions]" Column="ROUTE_ID"/></Identifier></ScalarOperator></RangeExpressions></Prefix><StartRange ScanType="GT"><RangeColumns><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="StartDate"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Expr1009]"><Identifier><ColumnReference Column="Expr1009"/></Identifier></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LT"><RangeColumns><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="StartDate"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Expr1010]"><Identifier><ColumnReference Column="Expr1010"/></Identifier></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="13" PhysicalOp="RID Lookup" LogicalOp="RID Lookup" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="36" EstimatedTotalSubtreeCost="0.0040736" TableCardinality="4" Parallel="0" EstimateRebinds="4.73496" EstimateRewinds="0.265043" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="Type"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="0" ActualExecutions="4"/></RunTimeInformation><IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" Column="Type"/></DefinedValue></DefinedValues><Object Database="[fiddle_421c4459871248ae89d661dc39cac4b8]" Schema="[dbo]" Table="[Routes]" TableReferenceId="-1" IndexKind="Heap" Storage="RowStore"/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Column="Bmk1003"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Bmk1003]"><Identifier><ColumnReference Column="Bmk1003"/></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Top></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Microsoft SQL Server 2005 XML Showplan