| scanCode |
dates |
flag |
rn1 |
rn2 |
diff |
| 182086 |
2020-07-03 00:00:00.000 |
A |
3 |
1 |
2 |
| 182086 |
2020-07-04 00:00:00.000 |
A |
4 |
2 |
2 |
| 182086 |
2020-07-10 00:00:00.000 |
A |
9 |
3 |
6 |
| 182086 |
2020-07-11 00:00:00.000 |
A |
10 |
4 |
6 |
| 182086 |
2020-07-13 00:00:00.000 |
A |
11 |
5 |
6 |
| 182086 |
2020-07-14 00:00:00.000 |
A |
12 |
6 |
6 |
| 182086 |
2020-07-01 00:00:00.000 |
P |
1 |
7 |
-6 |
| 182086 |
2020-07-02 00:00:00.000 |
P |
2 |
8 |
-6 |
| 182086 |
2020-07-06 00:00:00.000 |
P |
5 |
9 |
-4 |
| 182086 |
2020-07-07 00:00:00.000 |
P |
6 |
10 |
-4 |
| 182086 |
2020-07-08 00:00:00.000 |
P |
7 |
11 |
-4 |
| 182086 |
2020-07-09 00:00:00.000 |
P |
8 |
12 |
-4 |
| scanCode |
dates |
flag |
prn |
| 182086 |
2020-07-01 00:00:00.000 |
P |
1 |
| 182086 |
2020-07-02 00:00:00.000 |
P |
2 |
| 182086 |
2020-07-06 00:00:00.000 |
P |
1 |
| 182086 |
2020-07-07 00:00:00.000 |
P |
2 |
| 182086 |
2020-07-08 00:00:00.000 |
P |
3 |
| 182086 |
2020-07-09 00:00:00.000 |
P |
4 |
| 182086 |
2020-07-03 00:00:00.000 |
A |
1 |
| 182086 |
2020-07-04 00:00:00.000 |
A |
2 |
| 182086 |
2020-07-10 00:00:00.000 |
A |
1 |
| 182086 |
2020-07-11 00:00:00.000 |
A |
2 |
| 182086 |
2020-07-13 00:00:00.000 |
A |
3 |
| 182086 |
2020-07-14 00:00:00.000 |
A |
4 |
| 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="WITH
 N AS 
 (
 SELECT 
 *, 
 rn1 = ROW_NUMBER() OVER (
 PARTITION BY T.scanCode 
 ORDER BY T.dates),
 rn2 = ROW_NUMBER() OVER (
 PARTITION BY T.scanCode 
 ORDER BY T.flag, T.dates)
 FROM @t AS T
 )
SELECT
 N.scanCode,
 N.dates,
 N.flag,
 prn = ROW_NUMBER() OVER (
 PARTITION BY N.scanCode, (N.rn1 - N.rn2)
 ORDER BY N.dates)
FROM N" StatementId="1" StatementCompId="5" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.0261568" StatementEstRows="12" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x365310BB46AAC186" QueryPlanHash="0x55C4B8E2ADAD28C1" 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" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="224"><MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="704" RequiredMemory="640" DesiredMemory="704" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="606128" GrantedMemory="1024" MaxUsedMemory="24"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1043496"></OptimizerHardwareDependentProperties><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="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="12" EstimateIO="0" EstimateCPU="9.6e-07" AvgRowSize="32" EstimatedTotalSubtreeCost="0.0261568" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="12" EstimateIO="0" EstimateCPU="2.4e-07" AvgRowSize="32" EstimatedTotalSubtreeCost="0.0261558" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Segment1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1007"></ColumnReference></SegmentColumn><RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="12" EstimateIO="0.0112613" EstimateCPU="0.000167142" AvgRowSize="32" EstimatedTotalSubtreeCost="0.0261556" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><MemoryFractions Input="0.5" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="704" OutputMemoryGrant="512" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Expr1003"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="12" EstimateIO="0" EstimateCPU="1.2e-06" AvgRowSize="32" EstimatedTotalSubtreeCost="0.0147272" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="[Expr1001]-[Expr1002]"><Arithmetic Operation="SUB"><ScalarOperator><Identifier><ColumnReference Column="Expr1001"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="12" EstimateIO="0" EstimateCPU="9.6e-07" AvgRowSize="40" EstimatedTotalSubtreeCost="0.014726" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference><ColumnReference Column="Expr1001"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="12" EstimateIO="0" EstimateCPU="2.4e-07" AvgRowSize="40" EstimatedTotalSubtreeCost="0.014725" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference><ColumnReference Column="Expr1001"></ColumnReference><ColumnReference Column="Segment1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1006"></ColumnReference></SegmentColumn><RelOp NodeId="6" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="12" EstimateIO="0.0112613" EstimateCPU="0.000167142" AvgRowSize="32" EstimatedTotalSubtreeCost="0.0147248" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference><ColumnReference Column="Expr1001"></ColumnReference></OutputList><MemoryFractions Input="1" Output="0.5"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="896" OutputMemoryGrant="320" UsedMemoryGrant="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="7" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="12" EstimateIO="0" EstimateCPU="9.6e-07" AvgRowSize="32" EstimatedTotalSubtreeCost="0.0032964" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference><ColumnReference Column="Expr1001"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1001"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="12" EstimateIO="0" EstimateCPU="2.4e-07" AvgRowSize="32" EstimatedTotalSubtreeCost="0.00329544" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference><ColumnReference Column="Segment1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1005"></ColumnReference></SegmentColumn><RelOp NodeId="9" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="12" EstimatedRowsRead="12" EstimateIO="0.003125" EstimateCPU="0.0001702" AvgRowSize="24" EstimatedTotalSubtreeCost="0.0032952" TableCardinality="12" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="12" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="@t" Alias="[T]" Column="scanCode"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="@t" Alias="[T]" Column="dates"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="@t" Alias="[T]" Column="flag"></ColumnReference></DefinedValue></DefinedValues><Object Table="[@t]" Index="[PK__#B183311__A19560453D6B0315]" Alias="[T]" Storage="RowStore"></Object></IndexScan></RelOp></Segment></RelOp></SequenceProject></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |