/***************************************2012 - Aggregate with order****************************************/;with CTEOrders as (select cast(1 as int) as OrderID, cast('3/1/2012' as date) as OrderDate, cast(10.00 as money) as OrderAmt, 'Joe' as CustomerName union select 2, '3/1/2012', 11.00, 'Sam' union select 3, '3/2/2012', 10.00, 'Beth' union select 4, '3/2/2012', 15.00, 'Joe' union select 5, '3/2/2012', 17.00, 'Sam' union select 6, '3/3/2012', 12.00, 'Joe' union select 7, '3/4/2012', 10.00, 'Beth' union select 8, '3/4/2012', 18.00, 'Sam' union select 9, '3/4/2012', 12.00, 'Joe' union select 10, '3/4/2012', 11.00, 'Beth' union select 11, '3/5/2012', 14.00, 'Sam' union select 12, '3/6/2012', 17.00, 'Beth' union select 13, '3/6/2012', 19.00, 'Joe' union select 14, '3/7/2012', 13.00, 'Beth' union select 15, '3/7/2012', 16.00, 'Sam' )select OrderID ,OrderDate ,OrderAmt ,CustomerName ,SUM(OrderAmt) OVER (PARTITION BY datepart(yyyy, OrderDate) ORDER BY OrderDate) as AnnualRunning -- accumulate by day within year ,SUM(OrderAmt) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID) as RunningByCustomer -- accumulate by date then orderID within customer from CTEOrders --ORDER BY CustomerName ORDER BY OrderDate;/*OrderID OrderDate OrderAmt CustomerName AnnualRunning RunningByCustomer1 2012-03-01 10.0000 Joe 21.0000 10.00002 2012-03-01 11.0000 Sam 21.0000 11.00005 2012-03-02 17.0000 Sam 63.0000 28.00004 2012-03-02 15.0000 Joe 63.0000 25.00003 2012-03-02 10.0000 Beth 63.0000 10.00006 2012-03-03 12.0000 Joe 75.0000 37.00009 2012-03-04 12.0000 Joe 126.0000 49.00007 2012-03-04 10.0000 Beth 126.0000 20.000010 2012-03-04 11.0000 Beth 126.0000 31.00008 2012-03-04 18.0000 Sam 126.0000 46.000011 2012-03-05 14.0000 Sam 140.0000 60.000012 2012-03-06 17.0000 Beth 176.0000 48.000013 2012-03-06 19.0000 Joe 176.0000 68.000014 2012-03-07 13.0000 Beth 205.0000 61.000015 2012-03-07 16.0000 Sam 205.0000 76.0000*//*****************************************2012 Aggregate with ROWS*****************************************/with CTEOrders as (select cast(1 as int) as OrderID, cast('3/1/2012' as date) as OrderDate, cast(10.00 as money) as OrderAmt, 'Joe' as CustomerName union select 2, '3/1/2012', 11.00, 'Sam' union select 3, '3/2/2012', 10.00, 'Beth' union select 4, '3/2/2012', 15.00, 'Joe' union select 5, '3/2/2012', 17.00, 'Sam' union select 6, '3/3/2012', 12.00, 'Joe' union select 7, '3/4/2012', 10.00, 'Beth' union select 8, '3/4/2012', 18.00, 'Sam' union select 9, '3/4/2012', 12.00, 'Joe' union select 10, '3/4/2012', 11.00, 'Beth' union select 11, '3/5/2012', 14.00, 'Sam' union select 12, '3/6/2012', 17.00, 'Beth' union select 13, '3/6/2012', 19.00, 'Joe' union select 14, '3/7/2012', 13.00, 'Beth' union select 15, '3/7/2012', 16.00, 'Sam' )select OrderID ,OrderDate ,OrderAmt ,CustomerName ,SUM(OrderAmt) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID ROWS 1 PRECEDING) as LastTwoAmts ,SUM(OrderAmt) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID ROWS BETWEEN 1 FOLLOWING and 2 FOLLOWING) as NextTwoAmts ,SUM(OrderAmt) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) as CurrAndNextTwoAmts from CTEOrders order by CustomerName, OrderDate, OrderID;/*OrderID OrderDate OrderAmt CustomerName LastTwoAmts NextTwoAmts CurrAndNextTwoAmts3 2012-03-02 10.0000 Beth 10.0000 21.0000 31.00007 2012-03-04 10.0000 Beth 20.0000 28.0000 38.000010 2012-03-04 11.0000 Beth 21.0000 30.0000 41.000012 2012-03-06 17.0000 Beth 28.0000 13.0000 30.000014 2012-03-07 13.0000 Beth 30.0000 NULL 13.00001 2012-03-01 10.0000 Joe 10.0000 27.0000 37.00004 2012-03-02 15.0000 Joe 25.0000 24.0000 39.00006 2012-03-03 12.0000 Joe 27.0000 31.0000 43.00009 2012-03-04 12.0000 Joe 24.0000 19.0000 31.000013 2012-03-06 19.0000 Joe 31.0000 NULL 19.00002 2012-03-01 11.0000 Sam 11.0000 35.0000 46.00005 2012-03-02 17.0000 Sam 28.0000 32.0000 49.00008 2012-03-04 18.0000 Sam 35.0000 30.0000 48.000011 2012-03-05 14.0000 Sam 32.0000 16.0000 30.000015 2012-03-07 16.0000 Sam 30.0000 NULL 16.0000*//*****************************************2012 Aggregate with RANGE*****************************************/with CTEOrders as (select cast(1 as int) as OrderID, cast('3/1/2012' as date) as OrderDate, cast(10.00 as money) as OrderAmt, 'Joe' as CustomerName union select 2, '3/1/2012', 11.00, 'Sam' union select 3, '3/2/2012', 10.00, 'Beth' union select 4, '3/2/2012', 15.00, 'Joe' union select 5, '3/2/2012', 17.00, 'Sam' union select 6, '3/3/2012', 12.00, 'Joe' union select 7, '3/4/2012', 10.00, 'Beth' union select 8, '3/4/2012', 18.00, 'Sam' union select 9, '3/4/2012', 12.00, 'Joe' union select 10, '3/4/2012', 11.00, 'Beth' union select 11, '3/5/2012', 14.00, 'Sam' union select 12, '3/6/2012', 17.00, 'Beth' union select 13, '3/6/2012', 19.00, 'Joe' union select 14, '3/7/2012', 13.00, 'Beth' union select 15, '3/7/2012', 16.00, 'Sam' )select OrderID ,OrderDate ,OrderAmt ,CustomerName ,SUM(OrderAmt) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID RANGE BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as RunningTotalAmt from CTEOrders order by CustomerName, OrderDate, OrderID;/*OrderID OrderDate OrderAmt CustomerName RunningTotalAmt3 2012-03-02 10.0000 Beth 10.00007 2012-03-04 10.0000 Beth 20.000010 2012-03-04 11.0000 Beth 31.000012 2012-03-06 17.0000 Beth 48.000014 2012-03-07 13.0000 Beth 61.00001 2012-03-01 10.0000 Joe 10.00004 2012-03-02 15.0000 Joe 25.00006 2012-03-03 12.0000 Joe 37.00009 2012-03-04 12.0000 Joe 49.000013 2012-03-06 19.0000 Joe 68.00002 2012-03-01 11.0000 Sam 11.00005 2012-03-02 17.0000 Sam 28.00008 2012-03-04 18.0000 Sam 46.000011 2012-03-05 14.0000 Sam 60.000015 2012-03-07 16.0000 Sam 76.0000*//*****************************************2012 Aggregate with CURRENT ROWRANGE with Only CURRENT ROW specified - aggregates based on PARTITION and ORDER BY clausesROWS with Only CURRENT ROW specified - aggregates the current row only (or no aggregation)*****************************************/with CTEOrders as (select cast(1 as int) as OrderID, cast('3/1/2012' as date) as OrderDate, cast(10.00 as money) as OrderAmt, 'Joe' as CustomerName union select 2, '3/1/2012', 11.00, 'Sam' union select 3, '3/2/2012', 10.00, 'Beth' union select 4, '3/2/2012', 15.00, 'Joe' union select 5, '3/2/2012', 17.00, 'Sam' union select 6, '3/3/2012', 12.00, 'Joe' union select 7, '3/4/2012', 10.00, 'Beth' union select 8, '3/4/2012', 18.00, 'Sam' union select 9, '3/4/2012', 12.00, 'Joe' union select 10, '3/4/2012', 11.00, 'Beth' union select 11, '3/5/2012', 14.00, 'Sam' union select 12, '3/6/2012', 17.00, 'Beth' union select 13, '3/6/2012', 19.00, 'Joe' union select 14, '3/7/2012', 13.00, 'Beth' union select 15, '3/7/2012', 16.00, 'Sam' )select OrderID ,OrderDate ,OrderAmt ,CustomerName ,SUM(OrderAmt) OVER (PARTITION BY CustomerName ORDER BY OrderDate RANGE CURRENT ROW) as RunningTotalAmtRange ,SUM(OrderAmt) OVER (PARTITION BY CustomerName ORDER BY OrderDate ROWS CURRENT ROW) as RunningTotalAmtRow from CTEOrders order by CustomerName, OrderDate, OrderID;/*OrderID OrderDate OrderAmt CustomerName RunningTotalAmtRange RunningTotalAmtRow3 2012-03-02 10.0000 Beth 10.0000 10.00007 2012-03-04 10.0000 Beth 21.0000 10.000010 2012-03-04 11.0000 Beth 21.0000 11.000012 2012-03-06 17.0000 Beth 17.0000 17.000014 2012-03-07 13.0000 Beth 13.0000 13.00001 2012-03-01 10.0000 Joe 10.0000 10.00004 2012-03-02 15.0000 Joe 15.0000 15.00006 2012-03-03 12.0000 Joe 12.0000 12.00009 2012-03-04 12.0000 Joe 12.0000 12.000013 2012-03-06 19.0000 Joe 19.0000 19.00002 2012-03-01 11.0000 Sam 11.0000 11.00005 2012-03-02 17.0000 Sam 17.0000 17.00008 2012-03-04 18.0000 Sam 18.0000 18.000011 2012-03-05 14.0000 Sam 14.0000 14.000015 2012-03-07 16.0000 Sam 16.0000 16.0000*//*****************************************2012 Analytic functions*****************************************/with CTEOrders as (select cast(1 as int) as OrderID, cast('3/1/2012' as date) as OrderDate, cast(10.00 as money) as OrderAmt, 'Joe' as CustomerName union select 2, '3/1/2012', 11.00, 'Sam' union select 3, '3/2/2012', 10.00, 'Beth' union select 4, '3/2/2012', 15.00, 'Joe' union select 5, '3/2/2012', 17.00, 'Sam' union select 6, '3/3/2012', 12.00, 'Joe' union select 7, '3/4/2012', 10.00, 'Beth' union select 8, '3/4/2012', 18.00, 'Sam' union select 9, '3/4/2012', 12.00, 'Joe' union select 10, '3/4/2012', 11.00, 'Beth' union select 11, '3/5/2012', 14.00, 'Sam' union select 12, '3/6/2012', 17.00, 'Beth' union select 13, '3/6/2012', 19.00, 'Joe' union select 14, '3/7/2012', 13.00, 'Beth' union select 15, '3/7/2012', 16.00, 'Sam' )select OrderID ,OrderDate ,OrderAmt ,CustomerName ,LAG(OrderAmt) OVER (ORDER BY OrderDate, OrderID) as PrevOrderAmt ,LEAD(OrderAmt) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID) as NextAmtForCustomer ,LAG(OrderID, 2) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID) as TwoOrdersAgoID ,LAG(OrderAmt,1, 0) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID) as PrevOrderAmtDef0 ,LAG(OrderDate, 2, '9999-12-31') OVER (PARTITION BY CustomerName ORDER BY OrderID) as NextTwoOrdDtNoNull ,FIRST_VALUE(OrderDate) OVER (ORDER BY OrderID) as FirstOrdDt ,LAST_VALUE(CustomerName) OVER (PARTITION BY OrderDate ORDER BY OrderID) as LastCustToOrdByDay ,LAG(OrderAmt,(select count(*)-1 from CTEOrders c where c.CustomerName = CTEOrders.CustomerName) , 0) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID) as FirstOrderAmt -- works on last row only here from CTEOrders order by CustomerName, OrderDate, OrderID;/*OrderID OrderDate OrderAmt CustomerName PrevOrderAmt NextAmtForCustomer TwoOrdersAgoID PrevOrderAmtDef0 NextTwoOrdDtNoNull FirstOrdDt LastCustToOrdByDay FirstOrderAmt3 2012-03-02 10.0000 Beth 11.0000 10.0000 NULL 0.0000 9999-12-31 2012-03-01 Beth 0.00007 2012-03-04 10.0000 Beth 12.0000 11.0000 NULL 10.0000 9999-12-31 2012-03-01 Beth 0.000010 2012-03-04 11.0000 Beth 12.0000 17.0000 3 10.0000 2012-03-02 2012-03-01 Beth 0.000012 2012-03-06 17.0000 Beth 14.0000 13.0000 7 11.0000 2012-03-04 2012-03-01 Beth 0.000014 2012-03-07 13.0000 Beth 19.0000 NULL 10 17.0000 2012-03-04 2012-03-01 Beth 10.00001 2012-03-01 10.0000 Joe NULL 15.0000 NULL 0.0000 9999-12-31 2012-03-01 Joe 0.00004 2012-03-02 15.0000 Joe 10.0000 12.0000 NULL 10.0000 9999-12-31 2012-03-01 Joe 0.00006 2012-03-03 12.0000 Joe 17.0000 12.0000 1 15.0000 2012-03-01 2012-03-01 Joe 0.00009 2012-03-04 12.0000 Joe 18.0000 19.0000 4 12.0000 2012-03-02 2012-03-01 Joe 0.000013 2012-03-06 19.0000 Joe 17.0000 NULL 6 12.0000 2012-03-03 2012-03-01 Joe 10.00002 2012-03-01 11.0000 Sam 10.0000 17.0000 NULL 0.0000 9999-12-31 2012-03-01 Sam 0.00005 2012-03-02 17.0000 Sam 15.0000 18.0000 NULL 11.0000 9999-12-31 2012-03-01 Sam 0.00008 2012-03-04 18.0000 Sam 10.0000 14.0000 2 17.0000 2012-03-01 2012-03-01 Sam 0.000011 2012-03-05 14.0000 Sam 11.0000 16.0000 5 18.0000 2012-03-02 2012-03-01 Sam 0.000015 2012-03-07 16.0000 Sam 13.0000 NULL 8 14.0000 2012-03-04 2012-03-01 Sam 11.0000*/with CTEOrders as (select cast(1 as int) as OrderID, cast('3/1/2012' as date) as OrderDate, cast(10.00 as money) as OrderAmt, 'Joe' as CustomerName union select 2, '3/1/2012', 11.00, 'Sam' union select 3, '3/2/2012', 10.00, 'Beth' union select 4, '3/2/2012', 15.00, 'Joe' union select 5, '3/2/2012', 17.00, 'Sam' union select 6, '3/3/2012', 12.00, 'Joe' union select 7, '3/4/2012', 10.00, 'Beth' union select 8, '3/4/2012', 18.00, 'Sam' union select 9, '3/4/2012', 12.00, 'Joe' union select 10, '3/4/2012', 11.00, 'Beth' union select 11, '3/5/2012', 14.00, 'Sam' union select 12, '3/6/2012', 17.00, 'Beth' union select 13, '3/6/2012', 19.00, 'Joe' union select 14, '3/7/2012', 13.00, 'Beth' union select 15, '3/7/2012', 16.00, 'Sam' ), CTEOrderAnalytics as (select OrderID ,OrderDate ,OrderAmt ,CustomerName ,COUNT(*) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID) as RunningCount ,LAG(OrderAmt,(select count(*)-1 from CTEOrders c where c.CustomerName = CTEOrders.CustomerName) , 0) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID) as FirstOrderAmt -- works on last row only here from CTEOrders )select * ,LAG(OrderAmt,(select RunningCount-1 from CTEOrderAnalytics c where c.CustomerName = CTEOrderAnalytics.CustomerName and c.OrderID = CTEOrderAnalytics.OrderID) , 0) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID) as FirstOrderAmt -- works on last row only here ,FIRST_VALUE(OrderAmt) OVER (PARTITION BY CustomerName ORDER BY OrderDate, OrderID) as FirstOrderAmtEasyfrom CTEOrderAnalytics;/*OrderID OrderDate OrderAmt CustomerName RunningCount FirstOrderAmt FirstOrderAmt FirstOrderAmtEasy3 2012-03-02 10.0000 Beth 1 0.0000 10.0000 10.00007 2012-03-04 10.0000 Beth 2 0.0000 10.0000 10.000010 2012-03-04 11.0000 Beth 3 0.0000 10.0000 10.000012 2012-03-06 17.0000 Beth 4 0.0000 10.0000 10.000014 2012-03-07 13.0000 Beth 5 10.0000 10.0000 10.00001 2012-03-01 10.0000 Joe 1 0.0000 10.0000 10.00004 2012-03-02 15.0000 Joe 2 0.0000 10.0000 10.00006 2012-03-03 12.0000 Joe 3 0.0000 10.0000 10.00009 2012-03-04 12.0000 Joe 4 0.0000 10.0000 10.000013 2012-03-06 19.0000 Joe 5 10.0000 10.0000 10.00002 2012-03-01 11.0000 Sam 1 0.0000 11.0000 11.00005 2012-03-02 17.0000 Sam 2 0.0000 11.0000 11.00008 2012-03-04 18.0000 Sam 3 0.0000 11.0000 11.000011 2012-03-05 14.0000 Sam 4 0.0000 11.0000 11.000015 2012-03-07 16.0000 Sam 5 11.0000 11.0000 11.0000*//******************************CUME_DIST and PCT_RANK******************************/with CTEOrders as (select cast(1 as int) as OrderID, cast('3/1/2012' as date) as OrderDate, cast(10.00 as money) as OrderAmt, 'Joe' as CustomerName union select 2, '3/1/2012', 11.00, 'Sam' union select 3, '3/2/2012', 10.00, 'Beth' union select 4, '3/2/2012', 15.00, 'Joe' union select 5, '3/2/2012', 17.00, 'Sam' union select 6, '3/3/2012', 12.00, 'Joe' union select 7, '3/4/2012', 10.00, 'Beth' union select 8, '3/4/2012', 18.00, 'Sam' union select 9, '3/4/2012', 12.00, 'Joe' union select 10, '3/4/2012', 11.00, 'Beth' union select 11, '3/5/2012', 14.00, 'Sam' union select 12, '3/6/2012', 17.00, 'Beth' union select 13, '3/6/2012', 19.00, 'Joe' union select 14, '3/7/2012', 13.00, 'Beth' union select 15, '3/7/2012', 16.00, 'Sam' )select OrderID ,OrderDate ,OrderAmt ,CustomerName ,CUME_DIST() OVER (ORDER BY OrderAmt) CumDist ,PERCENT_RANK() OVER (ORDER BY OrderAmt) PctRankfrom CTEOrders/*1 2012-03-01 10.0000 Joe 0.2 03 2012-03-02 10.0000 Beth 0.2 07 2012-03-04 10.0000 Beth 0.2 02 2012-03-01 11.0000 Sam 0.333333333333333 0.21428571428571410 2012-03-04 11.0000 Beth 0.333333333333333 0.2142857142857146 2012-03-03 12.0000 Joe 0.466666666666667 0.3571428571428579 2012-03-04 12.0000 Joe 0.466666666666667 0.35714285714285714 2012-03-07 13.0000 Beth 0.533333333333333 0.511 2012-03-05 14.0000 Sam 0.6 0.5714285714285714 2012-03-02 15.0000 Joe 0.666666666666667 0.64285714285714315 2012-03-07 16.0000 Sam 0.733333333333333 0.7142857142857145 2012-03-02 17.0000 Sam 0.866666666666667 0.78571428571428612 2012-03-06 17.0000 Beth 0.866666666666667 0.7857142857142868 2012-03-04 18.0000 Sam 0.933333333333333 0.92857142857142913 2012-03-06 19.0000 Joe 1 1*//******************************PERCENTILE_CONT and PERCENTILE_DISC******************************/with CTEOrders as (select cast(1 as int) as OrderID, cast('3/1/2012' as date) as OrderDate, cast(10.00 as money) as OrderAmt, 'Joe' as CustomerName union select 2, '3/1/2012', 11.00, 'Sam' union select 3, '3/2/2012', 10.00, 'Beth' union select 4, '3/2/2012', 15.00, 'Joe' union select 5, '3/2/2012', 17.00, 'Sam' union select 6, '3/3/2012', 12.00, 'Joe' union select 7, '3/4/2012', 10.00, 'Beth' union select 8, '3/4/2012', 18.00, 'Sam' union select 9, '3/4/2012', 12.00, 'Joe' union select 10, '3/4/2012', 11.00, 'Beth' union select 11, '3/5/2012', 14.00, 'Sam' union select 12, '3/6/2012', 17.00, 'Beth' union select 13, '3/6/2012', 19.00, 'Joe' union select 14, '3/7/2012', 13.00, 'Beth' union select 15, '3/7/2012', 16.00, 'Sam' ) select OrderID as ID ,OrderDate as ODt ,OrderAmt as OAmt ,CustomerName as CName ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY OrderAmt) OVER () PerCont05 ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY OrderAmt) OVER () PerDisc05 ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY OrderAmt) OVER (PARTITION BY OrderDate) PerContDt ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY OrderAmt) OVER (PARTITION BY OrderDate) PerDiscDt ,PERCENTILE_CONT(0) WITHIN GROUP (ORDER BY OrderAmt) OVER() PerCont0from CTEOrders;/*ID ODt OAmt CName PerCont05 PerDisc05 PerContDt PerDiscDt PerCont01 2012-03-01 10.0000 Joe 13 13.0000 10.5 10.0000 102 2012-03-01 11.0000 Sam 13 13.0000 10.5 10.0000 103 2012-03-02 10.0000 Beth 13 13.0000 15 15.0000 104 2012-03-02 15.0000 Joe 13 13.0000 15 15.0000 105 2012-03-02 17.0000 Sam 13 13.0000 15 15.0000 106 2012-03-03 12.0000 Joe 13 13.0000 12 12.0000 107 2012-03-04 10.0000 Beth 13 13.0000 11.5 11.0000 1010 2012-03-04 11.0000 Beth 13 13.0000 11.5 11.0000 109 2012-03-04 12.0000 Joe 13 13.0000 11.5 11.0000 108 2012-03-04 18.0000 Sam 13 13.0000 11.5 11.0000 1011 2012-03-05 14.0000 Sam 13 13.0000 14 14.0000 1012 2012-03-06 17.0000 Beth 13 13.0000 18 17.0000 1013 2012-03-06 19.0000 Joe 13 13.0000 18 17.0000 1014 2012-03-07 13.0000 Beth 13 13.0000 14.5 13.0000 1015 2012-03-07 16.0000 Sam 13 13.0000 14.5 13.0000 10*/