SQL Server 2019

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

CREATE TABLE [dbo].[Sale](
    [ID] [int] NOT NULL,
    [SaleDate] [date] NOT NULL,
    [CustomerRef] [varchar](20) NOT NULL,
    INDEX IX_1 CLUSTERED (CustomerRef, SaleDate)
)

CREATE TABLE [dbo].[Verification](
    [CustomerRef] [varchar](20) NOT NULL,
    [VerificationDate] [date] NOT NULL,
    INDEX IX_2 CLUSTERED (CustomerRef, VerificationDate)
)

INSERT [dbo].[Sale] ([ID], [SaleDate], [CustomerRef]) VALUES
(1, CAST(N'2022-02-01' AS Date), N'1'),
(2, CAST(N'2022-02-02' AS Date), N'1'),
(3, CAST(N'2022-02-03' AS Date), N'1'),
(4, CAST(N'2022-02-13' AS Date), N'1'),
(5, CAST(N'2022-02-14' AS Date), N'1'),
(6, CAST(N'2022-02-15' AS Date), N'1'),
(7, CAST(N'2022-02-16' AS Date), N'1'),
(8, CAST(N'2022-03-08' AS Date), N'1'),
(9, CAST(N'2022-03-08' AS Date), N'1'),
(10, CAST(N'2022-03-10' AS Date), N'1'),
(11, CAST(N'2022-03-11' AS Date), N'1'),
(12, CAST(N'2022-03-12' AS Date), N'1'),
(13, CAST(N'2022-03-13' AS Date), N'1'),
(14, CAST(N'2022-02-20' AS Date), N'2'),
(15, CAST(N'2022-03-14' AS Date), N'1'),
(16, CAST(N'2022-02-10' AS Date), N'2'),
(17, CAST(N'2022-02-11' AS Date), N'2'),
(18, CAST(N'2022-02-12' AS Date), N'2'),
(19, CAST(N'2022-03-18' AS Date), N'2'),
(20, CAST(N'2022-03-19' AS Date), N'2'),
(21, CAST(N'2022-03-20' AS Date), N'2'),
(22, CAST(N'2022-02-15' AS Date), N'3'),
(23, CAST(N'2022-02-16' AS Date), N'3'),
(24, CAST(N'2022-02-20' AS Date), N'4'),
(25, CAST(N'2022-02-21' AS Date), N'4')
INSERT [dbo].[Verification] ([CustomerRef], [VerificationDate]) VALUES
(N'1', CAST(N'2022-02-01' AS Date)),
(N'2', CAST(N'2022-02-10' AS Date)),
(N'3', CAST(N'2022-02-15' AS Date)),
(N'4', CAST(N'2022-02-20' AS Date)),
(N'1', CAST(N'2022-03-10' AS Date)),
(N'2', CAST(N'2022-03-20' AS Date))

31 rows affected

CustomerRef	VerificationDate	NextDate	count
1	2022-02-01	2022-03-10	4
1	2022-03-10	2099-01-01	4
2	2022-02-10	2022-03-20	3
2	2022-03-20	2099-01-01	3
3	2022-02-15	2099-01-01	1
4	2022-02-20	2099-01-01	1

Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT v., s. FROM ( SELECT , NextDate = LEAD(v.VerificationDate, 1, '20990101') OVER (PARTITION BY v.CustomerRef ORDER BY v.VerificationDate) FROM Verification v ) v OUTER APPLY ( SELECT TOP (1) sFirst. FROM Sale sFirst WHERE sFirst.CustomerRef = v.CustomerRef AND sFirst.SaleDate < v.NextDate ORDER BY sFirst.SaleDate ) sFirst CROSS APPLY ( SELECT count = COUNT() FROM Sale s WHERE s.CustomerRef = v.CustomerRef AND s.SaleDate > sFirst.SaleDate AND s.SaleDate < DATEADD(day, 14, sFirst.SaleDate) AND s.SaleDate < v.NextDate ) s" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0115361" StatementEstRows="6" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xAE2858342A22555E" QueryPlanHash="0x068BB2B057CF2182" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="64" CompileTime="0" CompileCPU="0" CompileMemory="496"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1328072"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-05-31T09:21:17.37" ModificationCount="0" SamplingPercent="100" Statistics="[IX_1]" Table="[Sale]" Schema="[dbo]" Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-05-31T09:21:17.37" ModificationCount="0" SamplingPercent="100" Statistics="[IX_2]" Table="[Verification]" Schema="[dbo]" Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="6" EstimateIO="0" EstimateCPU="2.508e-05" AvgRowSize="22" EstimatedTotalSubtreeCost="0.0115361" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="6" EstimateIO="0" EstimateCPU="2.508e-05" AvgRowSize="21" EstimatedTotalSubtreeCost="0.00740912" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6" EstimateIO="0" EstimateCPU="6e-07" AvgRowSize="18" EstimatedTotalSubtreeCost="0.00330924" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Count1010]=(0) THEN '2099-01-01' ELSE [LastValue1009] END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Count1010"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="'2099-01-01'"></Const></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Column="LastValue1009"></ColumnReference></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="6" EstimateIO="0" EstimateCPU="3.768e-06" AvgRowSize="56" EstimatedTotalSubtreeCost="0.00330864" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="LastValue1009"></ColumnReference><ColumnReference Column="Count1010"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Count1010"></ColumnReference><ScalarOperator ScalarString="Count()"><Aggregate AggType="countstar" Distinct="0"></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="LastValue1009"></ColumnReference><ScalarOperator ScalarString="LAST_VALUE([fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Verification].[VerificationDate] as [v].[VerificationDate])"><Aggregate Distinct="0" AggType="LAST_VALUE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ScalarOperator ScalarString="ANY([fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Verification].[VerificationDate] as [v].[VerificationDate])"><Aggregate Distinct="0" AggType="ANY"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Column="WindowCount1018"></ColumnReference></GroupBy><RelOp NodeId="4" PhysicalOp="Window Spool" LogicalOp="Window Spool" EstimateRows="12" EstimateIO="0" EstimateCPU="1.3188e-05" AvgRowSize="56" EstimatedTotalSubtreeCost="0.00330487" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="RowNumber1013"></ColumnReference><ColumnReference Column="WindowCount1018"></ColumnReference><ColumnReference Column="Segment1017"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><WindowSpool><RelOp NodeId="5" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="6" EstimateIO="0" EstimateCPU="3.768e-07" AvgRowSize="56" EstimatedTotalSubtreeCost="0.00329018" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="RowNumber1013"></ColumnReference><ColumnReference Column="TopRowNumber1014"></ColumnReference><ColumnReference Column="BottomRowNumber1015"></ColumnReference><ColumnReference Column="Segment1017"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1017"></ColumnReference></SegmentColumn><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6" EstimateIO="0" EstimateCPU="6e-07" AvgRowSize="48" EstimatedTotalSubtreeCost="0.0032898" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="RowNumber1013"></ColumnReference><ColumnReference Column="TopRowNumber1014"></ColumnReference><ColumnReference Column="BottomRowNumber1015"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="TopRowNumber1014"></ColumnReference><ScalarOperator ScalarString="[RowNumber1013]+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="RowNumber1013"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="BottomRowNumber1015"></ColumnReference><ScalarOperator ScalarString="[RowNumber1013]+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="RowNumber1013"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="6" EstimateIO="0" EstimateCPU="4.8e-07" AvgRowSize="32" EstimatedTotalSubtreeCost="0.0032892" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="RowNumber1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="RowNumber1013"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="6" EstimateIO="0" EstimateCPU="1.2e-07" AvgRowSize="32" EstimatedTotalSubtreeCost="0.00328872" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="Segment1016"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1016"></ColumnReference></SegmentColumn><RelOp NodeId="9" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="6" EstimatedRowsRead="6" EstimateIO="0.003125" EstimateCPU="0.0001636" AvgRowSize="24" EstimatedTotalSubtreeCost="0.0032886" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Index="[IX_2]" Alias="[v]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp></Segment></RelOp></WindowSpool></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp><RelOp NodeId="10" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="10" EstimatedTotalSubtreeCost="0.0040748" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="6"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="11" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateRowsWithoutRowGoal="3.42327" EstimateIO="0" EstimateCPU="3.42327e-07" AvgRowSize="13" EstimatedTotalSubtreeCost="0.0040742" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="SaleDate"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Sale].[SaleDate] as [sFirst].[SaleDate]"><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="SaleDate"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="12" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateRowsWithoutRowGoal="3.42327" EstimatedRowsRead="3.42327" EstimateIO="0.003125" EstimateCPU="0.000160766" AvgRowSize="10" EstimatedTotalSubtreeCost="0.0040736" TableCardinality="25" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="SaleDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="6" ActualLogicalReads="12" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="0" ActualExecutions="6"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="SaleDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Index="[IX_1]" Alias="[sFirst]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="CustomerRef"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Verification].[CustomerRef] as [v].[CustomerRef]"><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix><EndRange ScanType="LT"><RangeColumns><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="SaleDate"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Expr1002]"><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp></Top></RelOp></NestedLoops></RelOp><RelOp NodeId="13" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00410186" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1008"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1019],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1019"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="14" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.80771e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00410186" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1019"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="6"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1019"></ColumnReference><ScalarOperator ScalarString="Count(*)"><Aggregate AggType="countstar" Distinct="0"></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="15" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="2.17952" EstimatedRowsRead="2.5335" EstimateIO="0.003125" EstimateCPU="0.000159787" AvgRowSize="10" EstimatedTotalSubtreeCost="0.00408372" TableCardinality="25" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="16" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="6" ActualLogicalReads="12" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="16" ActualEndOfScans="6" ActualExecutions="6"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Index="[IX_1]" Alias="[s]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[s]" Column="CustomerRef"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Verification].[CustomerRef] as [v].[CustomerRef]"><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix><StartRange ScanType="GT"><RangeColumns><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[s]" Column="SaleDate"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Expr1005]"><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LT"><RangeColumns><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[s]" Column="SaleDate"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="dateadd(day,(14),[Expr1005])"><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(14)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Sale].[SaleDate] as [s].[SaleDate]<[Expr1002]"><Compare CompareOp="LT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[s]" Column="SaleDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT v.*, s.*
FROM (
 SELECT *,
 NextDate = LEAD(v.VerificationDate, 1, '20990101') OVER (PARTITION BY v.CustomerRef ORDER BY v.VerificationDate)
 FROM Verification v
) v
OUTER APPLY (
 SELECT TOP (1) sFirst.*
 FROM Sale sFirst
 WHERE sFirst.CustomerRef = v.CustomerRef
 AND sFirst.SaleDate < v.NextDate
 ORDER BY sFirst.SaleDate
) sFirst
CROSS APPLY (
 SELECT count = COUNT(*)
 FROM Sale s
 WHERE s.CustomerRef = v.CustomerRef
 AND s.SaleDate > sFirst.SaleDate
 AND s.SaleDate < DATEADD(day, 14, sFirst.SaleDate)
 AND s.SaleDate < v.NextDate
) s" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0115361" StatementEstRows="6" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xAE2858342A22555E" QueryPlanHash="0x068BB2B057CF2182" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="64" CompileTime="0" CompileCPU="0" CompileMemory="496"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1328072"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-05-31T09:21:17.37" ModificationCount="0" SamplingPercent="100" Statistics="[IX_1]" Table="[Sale]" Schema="[dbo]" Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-05-31T09:21:17.37" ModificationCount="0" SamplingPercent="100" Statistics="[IX_2]" Table="[Verification]" Schema="[dbo]" Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="6" EstimateIO="0" EstimateCPU="2.508e-05" AvgRowSize="22" EstimatedTotalSubtreeCost="0.0115361" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="6" EstimateIO="0" EstimateCPU="2.508e-05" AvgRowSize="21" EstimatedTotalSubtreeCost="0.00740912" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6" EstimateIO="0" EstimateCPU="6e-07" AvgRowSize="18" EstimatedTotalSubtreeCost="0.00330924" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Count1010]=(0) THEN '2099-01-01' ELSE [LastValue1009] END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Count1010"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="'2099-01-01'"></Const></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Column="LastValue1009"></ColumnReference></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="6" EstimateIO="0" EstimateCPU="3.768e-06" AvgRowSize="56" EstimatedTotalSubtreeCost="0.00330864" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="LastValue1009"></ColumnReference><ColumnReference Column="Count1010"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Count1010"></ColumnReference><ScalarOperator ScalarString="Count(*)"><Aggregate AggType="countstar" Distinct="0"></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="LastValue1009"></ColumnReference><ScalarOperator ScalarString="LAST_VALUE([fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Verification].[VerificationDate] as [v].[VerificationDate])"><Aggregate Distinct="0" AggType="LAST_VALUE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ScalarOperator ScalarString="ANY([fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Verification].[VerificationDate] as [v].[VerificationDate])"><Aggregate Distinct="0" AggType="ANY"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Column="WindowCount1018"></ColumnReference></GroupBy><RelOp NodeId="4" PhysicalOp="Window Spool" LogicalOp="Window Spool" EstimateRows="12" EstimateIO="0" EstimateCPU="1.3188e-05" AvgRowSize="56" EstimatedTotalSubtreeCost="0.00330487" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="RowNumber1013"></ColumnReference><ColumnReference Column="WindowCount1018"></ColumnReference><ColumnReference Column="Segment1017"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="8" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><WindowSpool><RelOp NodeId="5" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="6" EstimateIO="0" EstimateCPU="3.768e-07" AvgRowSize="56" EstimatedTotalSubtreeCost="0.00329018" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="RowNumber1013"></ColumnReference><ColumnReference Column="TopRowNumber1014"></ColumnReference><ColumnReference Column="BottomRowNumber1015"></ColumnReference><ColumnReference Column="Segment1017"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1017"></ColumnReference></SegmentColumn><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6" EstimateIO="0" EstimateCPU="6e-07" AvgRowSize="48" EstimatedTotalSubtreeCost="0.0032898" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="RowNumber1013"></ColumnReference><ColumnReference Column="TopRowNumber1014"></ColumnReference><ColumnReference Column="BottomRowNumber1015"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="TopRowNumber1014"></ColumnReference><ScalarOperator ScalarString="[RowNumber1013]+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="RowNumber1013"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="BottomRowNumber1015"></ColumnReference><ScalarOperator ScalarString="[RowNumber1013]+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="RowNumber1013"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="6" EstimateIO="0" EstimateCPU="4.8e-07" AvgRowSize="32" EstimatedTotalSubtreeCost="0.0032892" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="RowNumber1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="RowNumber1013"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="6" EstimateIO="0" EstimateCPU="1.2e-07" AvgRowSize="32" EstimatedTotalSubtreeCost="0.00328872" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference><ColumnReference Column="Segment1016"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1016"></ColumnReference></SegmentColumn><RelOp NodeId="9" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="6" EstimatedRowsRead="6" EstimateIO="0.003125" EstimateCPU="0.0001636" AvgRowSize="24" EstimatedTotalSubtreeCost="0.0032886" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="VerificationDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Index="[IX_2]" Alias="[v]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp></Segment></RelOp></WindowSpool></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp><RelOp NodeId="10" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="10" EstimatedTotalSubtreeCost="0.0040748" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="6"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="11" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateRowsWithoutRowGoal="3.42327" EstimateIO="0" EstimateCPU="3.42327e-07" AvgRowSize="13" EstimatedTotalSubtreeCost="0.0040742" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="SaleDate"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Sale].[SaleDate] as [sFirst].[SaleDate]"><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="SaleDate"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="12" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateRowsWithoutRowGoal="3.42327" EstimatedRowsRead="3.42327" EstimateIO="0.003125" EstimateCPU="0.000160766" AvgRowSize="10" EstimatedTotalSubtreeCost="0.0040736" TableCardinality="25" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="SaleDate"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="6" ActualLogicalReads="12" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="0" ActualExecutions="6"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="SaleDate"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Index="[IX_1]" Alias="[sFirst]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="CustomerRef"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Verification].[CustomerRef] as [v].[CustomerRef]"><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix><EndRange ScanType="LT"><RangeColumns><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[sFirst]" Column="SaleDate"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Expr1002]"><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp></Top></RelOp></NestedLoops></RelOp><RelOp NodeId="13" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00410186" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1008"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1019],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1019"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="14" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.80771e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00410186" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1019"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="6"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1019"></ColumnReference><ScalarOperator ScalarString="Count(*)"><Aggregate AggType="countstar" Distinct="0"></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="15" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="2.17952" EstimatedRowsRead="2.5335" EstimateIO="0.003125" EstimateCPU="0.000159787" AvgRowSize="10" EstimatedTotalSubtreeCost="0.00408372" TableCardinality="25" Parallel="0" EstimateRebinds="5" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="16" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="6" ActualLogicalReads="12" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="16" ActualEndOfScans="6" ActualExecutions="6"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Index="[IX_1]" Alias="[s]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[s]" Column="CustomerRef"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Verification].[CustomerRef] as [v].[CustomerRef]"><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Verification]" Alias="[v]" Column="CustomerRef"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix><StartRange ScanType="GT"><RangeColumns><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[s]" Column="SaleDate"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Expr1005]"><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LT"><RangeColumns><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[s]" Column="SaleDate"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="dateadd(day,(14),[Expr1005])"><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(14)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e].[dbo].[Sale].[SaleDate] as [s].[SaleDate]<[Expr1002]"><Compare CompareOp="LT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4c3afa266fbe4ce99a10f00c33b3873e]" Schema="[dbo]" Table="[Sale]" Alias="[s]" Column="SaleDate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>