| hProp |
iDateTarget1 |
dblPercentTarget1 |
iDateTarget2 |
dblPercentTarget1 |
iDateTarget3 |
dblPercentTarget3 |
iDateTarget4 |
dblPercentTarget4 |
| 10 |
5 |
0.1400 |
10 |
0.0500 |
15 |
0.0100 |
20 |
0.0100 |
Warning: Null value is eliminated by an aggregate or other SET operation.
| Microsoft SQL Server 2005 XML Showplan |
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3451.2"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH Ranked AS
(
 SELECT
 T.*,
 rn = ROW_NUMBER() OVER (
 PARTITION BY T.hProp 
 ORDER BY T.iDayOfMonth)
 FROM @T AS T
)
SELECT
 R.hProp,
 iDateTarget1 = MAX(CASE WHEN R.rn = 1 THEN R.iDayOfMonth END),
 dblPercentTarget1 = MAX(CASE WHEN R.rn = 1 THEN R.dblTargetPercent END),
 iDateTarget2 = MAX(CASE WHEN R.rn = 2 THEN R.iDayOfMonth END),
 dblPercentTarget1 = MAX(CASE WHEN R.rn = 2 THEN R.dblTargetPercent END),
 iDateTarget3 = MAX(CASE WHEN R.rn = 3 THEN R.iDayOfMonth END),
 dblPercentTarget3 = MAX(CASE WHEN R.rn = 3 THEN R.dblTargetPercent END),
 iDateTarget4 = MAX(CASE WHEN R.rn = 4 THEN R.iDayOfMonth END),
 dblPercentTarget4 = MAX(CASE WHEN R.rn = 4 THEN R.dblTargetPercent END)
FROM Ranked AS R
GROUP BY
 R.hProp" StatementId="1" StatementCompId="4" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0032843" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x2912D3E3B2314A1F" QueryPlanHash="0x77FAE95D471D4C5E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><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="40" CompileTime="0" CompileCPU="0" CompileMemory="424"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="699104"></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="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.1e-006" AvgRowSize="48" EstimatedTotalSubtreeCost="0.0032843" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="hProp"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="MAX(CASE WHEN [Expr1001]=(1) THEN @T.[iDayOfMonth] as [T].[iDayOfMonth] ELSE NULL END)"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1001"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="iDayOfMonth"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="MAX(CASE WHEN [Expr1001]=(1) THEN @T.[dblTargetPercent] as [T].[dblTargetPercent] ELSE NULL END)"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1001"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="dblTargetPercent"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="MAX(CASE WHEN [Expr1001]=(2) THEN @T.[iDayOfMonth] as [T].[iDayOfMonth] ELSE NULL END)"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1001"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="iDayOfMonth"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="MAX(CASE WHEN [Expr1001]=(2) THEN @T.[dblTargetPercent] as [T].[dblTargetPercent] ELSE NULL END)"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1001"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="dblTargetPercent"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="MAX(CASE WHEN [Expr1001]=(3) THEN @T.[iDayOfMonth] as [T].[iDayOfMonth] ELSE NULL END)"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1001"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(3)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="iDayOfMonth"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="MAX(CASE WHEN [Expr1001]=(3) THEN @T.[dblTargetPercent] as [T].[dblTargetPercent] ELSE NULL END)"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1001"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(3)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="dblTargetPercent"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="MAX(CASE WHEN [Expr1001]=(4) THEN @T.[iDayOfMonth] as [T].[iDayOfMonth] ELSE NULL END)"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1001"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="iDayOfMonth"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1009"></ColumnReference><ScalarOperator ScalarString="MAX(CASE WHEN [Expr1001]=(4) THEN @T.[dblTargetPercent] as [T].[dblTargetPercent] ELSE NULL END)"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1001"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="dblTargetPercent"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Table="@T" Alias="[T]" Column="hProp"></ColumnReference></GroupBy><RelOp NodeId="1" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="8e-008" AvgRowSize="28" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="hProp"></ColumnReference><ColumnReference Table="@T" Alias="[T]" Column="iDayOfMonth"></ColumnReference><ColumnReference Table="@T" Alias="[T]" Column="dblTargetPercent"></ColumnReference><ColumnReference Column="Expr1001"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" 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="2" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="1" EstimateIO="0" EstimateCPU="2e-008" AvgRowSize="28" EstimatedTotalSubtreeCost="0.00328312" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="hProp"></ColumnReference><ColumnReference Table="@T" Alias="[T]" Column="iDayOfMonth"></ColumnReference><ColumnReference Table="@T" Alias="[T]" Column="dblTargetPercent"></ColumnReference><ColumnReference Column="Segment1018"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Table="@T" Alias="[T]" Column="hProp"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1018"></ColumnReference></SegmentColumn><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="hProp"></ColumnReference><ColumnReference Table="@T" Alias="[T]" Column="iDayOfMonth"></ColumnReference><ColumnReference Table="@T" Alias="[T]" Column="dblTargetPercent"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" 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="hProp"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="@T" Alias="[T]" Column="iDayOfMonth"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="@T" Alias="[T]" Column="dblTargetPercent"></ColumnReference></DefinedValue></DefinedValues><Object Table="[@T]" Index="[PK__#B4E105C__E1D53E8A507FE10D]" Alias="[T]" Storage="RowStore"></Object></IndexScan></RelOp></Segment></RelOp></SequenceProject></RelOp></StreamAggregate></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |