--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
-- OLAP Specifications - by Birgitta Hauser
--+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Commit;

cl:CHGLIBL LIBL(QTEMP COMSQLQRY SYSTOOLS QGPL);
CL:CHGJOB LANGID(DEU) CNTRYID(DE) CCSID(1141);

CL:CHGQRYA QRYOPTLIB(HSCOMMON05);

Select * from Library_List_Info;

------------------------------------------------------------------------------------------------------
-- Windows Order Clause
------------------------------------------------------------------------------------------------------
Select a.* from SalesCustY a
Where SalesYear = 2009
;
--....................................................................................................
-- Row_Number
--....................................................................................................
Select Row_Number() Over() RowNbr,
       CustNo, Amount
  From SalesCusty
  Where SalesYear = 2009
--  Order By CustNo
-- Order By Amount Desc
;

Select Row_Number() over(Order By Amount Desc) RowNbr,
       CustNo, Amount
  From SalesCustY
  Where SalesYear = 2009
  Order By Amount Desc
--  Order By CustNo
  ;
  
Select Row_Number() over(Order By CustNo) RowNbr,
       CustNo, Amount
  From SalesCustY
  Where SalesYear = 2009
--  Order By Amount Desc
  Order By CustNo
  ;
  
--....................................................................................................
-- RANK and DENSE_RANK
--....................................................................................................
-- Select * from Salesx;
-- 
-- Create View COMSQLQRY.SalesCustY
-- as (Select Year(SalesDate) SalesYear, CustNo, Sum(Amount) Amount
--       from SalesX
--       Group By Year(SalesDate), custNo);
--       
-- Commit;   

Select * From SalesEmp
Where SalesYear = 2008
order By Amount Desc;   

 Select  Employee, Amount, Row_Number() Over(Order By Amount Desc)
   From SalesEmp
   Where SalesYear = 2008
   Order By Amount Desc;
 
 Select Employee, Amount, 
        Rank()       Over(Order By Amount Desc) Rank,
        Dense_Rank() Over(Order By Amount Desc) DenseRank
    from SalesEmp
    Where SalesYear = 2008
    Order By Amount Desc, Employee
    ;
    
With Year2008 as (Select  Employee, Amount Amount2008,
                          Rank() Over(Order By Amount Desc) as Rank2008
                    From  SalesEmp
                    Where SalesYear = 2008),
     Year2009 as (Select  Employee, Amount Amount2009,
                          Rank() Over(Order By Amount Desc) as Rank2009
                    From  SalesEmp
                    Where SalesYear = 2009) 
                    
Select Coalesce(a.Employee, b.Employee) AllEmployees, 
       Amount2008, Rank2008, Amount2009, Rank2009,
       case When Rank2009 = Rank2008 then ' = '
            When Rank2009 > Rank2008 then ' - '
            When Rank2009 < Rank2008 Then ' + '
            When Rank2008 is NULL     and Rank2009 is Not NULL Then ' ++'
            When Rank2008 is Not NULL and Rank2009 is NULL Then ' --'
       End as Progress
from year2008 a full join year2009 b on a.employee = b.employee
Order By AllEmployees
;    
 
------------------------------------------------------------------------------------------------------    
-- Partition By Clause
------------------------------------------------------------------------------------------------------
Select * FRom SalesCusty
Order By SalesYear, CustNo;

Select SalesYear, Dense_Rank() Over(Order By Amount Desc) Rank, a.*
  from SalesCusty a
--  Order By SalesYear, CustNo
  Order By SalesYear, Amount Desc
  ;
  
Select SalesYear, Dense_Rank() Over(Partition By SalesYear Order By Amount Desc) Rank, a.*
  from SalesCusty a
--  Order By SalesYear, CustNo
  Order By SalesYear, Amount Desc
  ;  

 Select SalesYear,
        Dense_Rank() Over(Partition By SalesYear
                          Order By Amount Desc) Rank,
        Employee, Amount
   From SalesEmp
   Order By SalesYear, Amount Desc;
--   Order BY Employee, SalesYear, Amount Desc   
   ;

------------------------------------------------------------------------------------------------------   
-- Rank 2-4 with Sub-Select or CTE
------------------------------------------------------------------------------------------------------
Select *
   from (Select a.*, Dense_Rank() Over(Order By Amount Desc) as DenseRank
            from SalesEmp a
            Where SalesYear = 2009) as x
Where DenseRank between 2 and 4
;


With x as (Select a.*, 
                  Dense_Rank() Over(Order By Amount Desc) as Rang
              From SalesEmp a
              Where SalesYear = 2009)
Select * from x
  where 
Rang between 2 and 4
;      

--====================================================================================================
-- Create Views if not yet exist
--====================================================================================================
-- Create Or Replace View COMSQLQRY/SalesVWYearMonth as
-- Select  Year(SalesDate) SalesYear, Month(SalesDate) SalesMonth, CustNo, ItemNo, --Item, 
--         Sum(Amount) Total, Min(Amount) Minimum, Max(Amount) Maximum, Cast(Avg(Amount) as Dec(11, 4)) Average, Count(*) NbrPos
--   From Salesx
--   Group By Year(SalesDate), Month(SalesDate), CustNo, ItemNo --Item  
--   ;     
-- 
-- Create Or Replace View COMSQLQRY/SalesVWYear as
-- Select  Year(SalesDate) SalesYear, CustNo, ItemNo, --- Item, 
--         Sum(Amount) Total, Min(Amount) Minimum, Max(Amount) Maximum, Cast(Avg(Amount) as Dec(11, 4)) Average, Count(*) NbrPos
--   From SAlesx
--   Group By Year(SalesDate), CustNo, ItemNo--, Item  
--   ;   
--       
-- Commit; 

--****************************************************************************************************
-- Ordered OLAP Specifications
--****************************************************************************************************  
------------------------------------------------------------------------------------------------------         
-- LAG / LEAD
------------------------------------------------------------------------------------------------------
-- Determine previous and following row values
-----------------------------------------------------
  Select * from SalesVWYear;

  Select SalesYear, CustNo, ItemNo, Item, Total,
         Lag(Total)  Over(Order By Total Desc)  as "Previous",
         Lead(Total) Over(Order By Total Desc)  as "Next"
     from SalesVWYear a
     Where SalesYear = 2009 and ItemNo = '5200' 
     Order By SalesYear, Total Desc
 ;  
 
   Select SalesYear, CustNo, ItemNo, Item, Total,
         Lag(Total,  3)             Over(Order By Total Desc)  as "Previous 3",
         Lead(Total, 2, -999999,99) Over(Order By Total Desc)  as "Next 2"
     from SalesVWYear a
     Where SalesYear = 2009 and ItemNo = '5200' 
     Order By SalesYear, Total Desc
--     Order By CustNo
     ;

-- Multiple Years
--------------------------
  Select SalesYear, CustNo, ItemNo, Item, Total,
         Lag(Total)  Over(Partition By SalesYear Order By Total Desc)  as "Previous",
         Lead(Total) Over(Partition By SalesYear Order By Total Desc)  as "Next"
     from SalesVWYear a
     Where ItemNo = '5200'
     Order By SalesYear, Total Desc
--     Order By SalesYear, CustNo
;

------------------------------------------------------------------------------------------------------ 
-- Caluclate differences (incl. Partition By)
------------------------------------------------------------------------------------------------------
  Select * from Comsqlqry.Salescustyear;
 
  -- 1. For a single year
  ---------------------------------
    Select  SalesYear, Sales,
          Lag(Sales)   Over(Order By Sales Desc)                  as "Previous",
          Lead(Sales)  Over(Order By Sales Desc)                  as "Next",
          Lag(Sales)   Over(Order By Sales Desc) - Sales          as "DiffPrv",
          Sales - Lead(Sales) Over(Order By Sales Desc)           as "DiffNext",
          Lag(Sales, 1, 0)    Over(Order By Sales Desc) - Sales   as "DiffPrvDft",
          Sales - Lead(Sales, 1, 0) Over(Order By Sales Desc)     as "DiffNextDft"
    --      , a.*
 from SalesCustYear a
 Where SalesYear = 2009
 Order By SalesYear, Sales Desc;
 
   -- 2. For a multiple years
   --------------------------------------
 Select  SalesYear, Sales,
          Lag(Sales)   Over(Partition By SalesYear Order By Sales Desc)                  as "Previous",
          Lead(Sales)  Over(Partition By SalesYear Order By Sales Desc)                  as "Next",
          Lag(Sales)   Over(Partition By SalesYear Order By Sales Desc) - Sales          as "DiffPrv",
          Sales - Lead(Sales) Over(Partition By SalesYear Order By Sales Desc)           as "DiffNext",
          Lag(Sales, 1, 0)    Over(Partition By SalesYear Order By Sales Desc) - Sales   as "DiffPrvDft",
          Sales - Lead(Sales, 1, 0) Over(Partition By SalesYear Order By Sales Desc)     as "DiffNextDft"
    --      , a.*
 from SalesCustYear a
 Order By SalesYear, Sales Desc;
 
 -- 3. Including Total
 ---------------------------------------
 With x as ( Select  SalesYear, Sales,
                     Lag(Sales)   Over(Partition By SalesYear Order By Sales Desc)                  as "Previous",
                     Lead(Sales)  Over(Partition By SalesYear Order By Sales Desc)                  as "Next",
                     Lag(Sales)   Over(Partition By SalesYear Order By Sales Desc) - Sales          as "DiffPrv",
                     Sales - Lead(Sales) Over(Partition By SalesYear Order By Sales Desc)           as "DiffNext",
                     Lag(Sales, 1, 0)    Over(Partition By SalesYear Order By Sales Desc) - Sales   as "DiffPrvDft",
                     Sales - Lead(Sales, 1, 0) Over(Partition By SalesYear Order By Sales Desc)     as "DiffNextDft"
                     --      , a.*
                from SalesCustYear a)
 Select SalesYear, 
        Case When Grouping(Sales) = 1 
             then Sum(Sales)
             Else Sales
             End                       as Sales, 
        "Previous", "Next", "DiffPrv", "DiffNext", "DiffPrvDft", "DiffNextDft" 
   from x
   Group By Grouping Sets((SalesYear, Sales, "Previous", "Next", "DiffPrv", "DiffNext", "DiffPrvDft", "DiffNextDft"),
                          (SalesYear)); 
 
 -- LAG/LEAD with SALESEMP
-- Select * from SalesEmp;
 
 -- 1. For a single year
--    Select  SalesYear, Amount,
--           Lag(Amount)  Over(Order By Amount Desc)                 as "Previous",
--           Lead(Amount) Over(Order By Amount Desc)                 as "Next",
--           Lag(Amount)  Over(Order By Amount Desc) - Amount        as "DiffPrv",
--           Amount - Lead(Amount) Over(Order By Amount Desc)        as "DiffNext",
--           Lag(Amount, 1, 0)  Over(Order By Amount Desc) - Amount  as "DiffPrvDft",
--           Amount - Lead(Amount, 1, 0) Over(Order By Amount Desc)  as "DiffNextDft"
--           , a.*
--  from SalesEmp a
--  Where SalesYear = 2009
--  Order By SalesYear, Amount Desc;   
 
 -- 2. For Multiple Years: Over(Partition By AND Order By)
--    Select  SalesYear, Employee, Amount,
--           Dense_Rank() Over(Partition By SalesYear Order By Amount Desc)  as "DenseRank",
--           Lag(Amount)  Over(Partition by SalesYear Order By Amount Desc)  as "Previous",
--           Lead(Amount) Over(Partition By SalesYear Order By Amount Desc)  as "Next",
--           Lag(Amount)  Over(Partition by SalesYear Order By Amount Desc) - Amount  as "DiffPrv",
--           Amount - Lead(Amount) Over(Partition By SalesYear Order By Amount Desc)  as "DiffNext"
--           , a.*
--  from SalesEmp a
--  Order By SalesYear, Amount Desc;   
 
 -- Calculate sales differences between 3 diffent years
 ------------------------------------------------------------
   With x as (Select Year(SalesDate) SalesYear, CustNo, Sum(Amount) Total
                From Sales
                Group By Year(SalesDate), CustNo),
        y as (Select SalesYear, CustNo, 
                     Lag(Total, 2, 0) Over(Partition By CustNo Order By SalesYear)  as PrvPrvYear,
                     Lag(Total, 1, 0) Over(Partition By CustNo Order By SalesYear)  as PrvYear,
                     Total
                from x)  
         
       Select SalesYear, CustNo, 
              PrvPrvYear "2008", PrvYear "2009", Total   "2010", 
              Total - PrvPrvYear   "2010 - 2008",
              Total - PrvYear      "2010 - 2009",
              PrvYear - PrvPrvYear "2009 - 2008"
         From y
         Where SalesYear = 2010
         Order By CustNo, Salesyear
 ; 

-- Multiple Aggregate Functions: Attention Order By Total for Average! to get the same sequence as for Total!!! 
------------------------------------------------------------------------------------------------------------------
  Select SalesYear, CustNo, ItemNo, Item, Total,
         Lag(Total)    Over(Order By Total Desc)  as "Previous",
         Lead(Total)   Over(Order By Total Desc)  as "Next",
         Average,
         Lag(Average)  Over(Order By Total Desc)  as "Prev.Avg",
         Lead(Average) Over(Order By Total Desc)  as "Next Avg"         
     from SalesVWYear a
     Where SalesYear = 2009 and CustNo = '10001'
--     Order By SalesYear, Total Desc
     Order By CustNo, SalesYear, Total Desc
 ;        
   

-- Quantile NTILE(X) - Example
------------------------------------
 Select SalesYear, Amount,
        Ntile(2) Over(Order By Amount Desc) Quantile2,
        Ntile(5) Over(Order By Amount Desc) Quantile5
 From SalesEmp
 Where SalesYear = 2009;      
 
  Select SalesYear, Amount,
        Ntile(2) Over(Partition By SalesYear Order By Amount Desc) Quantile2,
        Ntile(5) Over(Partition By SalesYear Order By Amount Desc) Quantile5
 From SalesEmp
 Order By SalesYear, Amount Desc;      
 
 -- Cume_Dist() / Percent_Rank()
 -------------------------------------
 -- Cume Dist:     Current_Dense Rank      / Number of Rows        --> 2 / 5 = 0,6
 -- Percent Rank: (Current Dense Rank - 1) / (Number of rows - 1)  --> 2-1 / (5-1) = 0,25 
 
  With x as (Select Year(SalesDate) SalesYear, CustNo, Sum(Amount) Total
              From Sales
              Group By Year(SalesDate), CustNo)
  Select SalesYear, Total,
        Cast(Cume_Dist()    Over(Order By Total ) as Dec(7, 5))    as "Cume_Dist",
        Cast(Percent_Rank() Over(Order By Total ) as Dec(7, 5))    as "Percent_Rank"
    from  x
    Where SalesYear = 2009 
    Order By SalesYear, Total ;            
 
--====================================================================================================
-- OLAP Aggregate Functions
--====================================================================================================
Select * from SalesVWYear;

Select * from SalesCustY
Order By SalesYear, CustNo;

Refresh Table SalesCustY;

Commit;

------------------------------------------------------------------------------------------------------
-- 1. Without windows-Aggregation-Group Clause
------------------------------------------------------------------------------------------------------
-- 1. Without Window-Aggregation-Group and without Windows-Order
--    --> All rows are accumulated
-- Accumulated Aggregate Information for a specific year
 Select  SalesYear, CustNo, CustName1, Amount,
         Sum(Amount) Over() "Sum Part",
         Avg(Amount) Over() "Avg Part",
         Max(Amount) Over() "Maximum Part", 
         Min(Amount) Over() "Minmum Part"                                                       
   From SalesCustY
   Where SalesYear = 2009
   Order By SalesYear, CustNo;  
 
 
-- Accumulated Aggregate Information for a specific year - Incl. Calculating % and Grand Total
 Select  SalesYear, CustNo, CustName1,
         Case When Grouping(CustNo) = 0 
              Then Amount
              else Sum(Amount) Over() 
              End           "Amount",
         Sum(Amount) Over() "Sum Part",
         Avg(Amount) Over() "Avg Part",
         Max(Amount) Over() "Maximum Part", 
         Min(Amount) Over() "Minmum Part",  
         Sum(Amount) * 100,0000 / Sum(Amount) Over() "% Year"                                                     
   From SalesCustY
   Where SalesYear = 2009
   Group By Grouping Sets((SalesYear, CustNo, CustName1, Amount), ())
   Order By SalesYear, CustNo;    

------------------------------------------------------------------------------------------------------
-- With Partition By
------------------------------------------------------------------------------------------------------
-- Accumulated Aggregate Information for all years
 Select  SalesYear, CustNo, CustName1, Amount,
         Sum(Amount) Over(Partition By SalesYear) "Sum Part",
         Avg(Amount) Over(Partition By SalesYear) "Avg Part",
         Max(Amount) Over(Partition By SalesYear) "Maximum Part", 
         Min(Amount) Over(Partition By SalesYear) "Minmum Part"                                                       
   From SalesCustY
   Order By SalesYear, CustNo    
--Where SalesYear = 2009
--Order BY SalesYear, Amount Desc   
;

-- Accumulated Aggreagate Information for all years (Level break SALESYEAR)
-- Including % per year, Total per Sales Year and Grand Total
 Select  SalesYear, CustNo, CustName1, 
         Case When Grouping(SalesYear) = 1
              Then Sum(Amount) Over()
              When Grouping(CustNo)    = 1 
              Then Sum(Amount) Over(Partition By SalesYear)
              Else Amount End                     "Amount",
         Sum(Amount) Over(Partition By SalesYear) "Sum Part",
         Avg(Amount) Over(Partition By SalesYear) "Avg Part",
         Max(Amount) Over(Partition By SalesYear) "Maximum Part", 
         Min(Amount) Over(Partition By SalesYear) "Minmum Part",
         Sum(Amount) Over()                       "Sum Total",   
         Case When Grouping(CustNo) = 1 
              Then Sum(Amount) 
              Else Amount End * 100,0000 / Sum(Amount) Over(Partition By SalesYear) "% Year"                                                              
   From SalesCustY
   Group By Grouping Sets((SalesYear, CustNo, CustName1, Amount), (SalesYear), ())
   Order By SalesYear, CustNo ;   

------------------------------------------------------------------------------------------------------
-- With ORDER BY
------------------------------------------------------------------------------------------------------
-- 2. Without window-aggregation-group-Clause but with window order clause
--    --> Rolling results
Select  SalesYear, CustNo, CustName1, Amount,
        Sum(Amount) Over(Order By Amount desc) "Rolling Sum",
        Avg(Amount) Over(Order By Amount Desc) "Rolling Avg",
        Max(Amount) Over(Order By Amount Desc) "Rolling Maximum", 
        Min(Amount) Over(Order By Amount Desc) "Rolling Minmum"                                                       
From SalesCustY
Where SalesYear = 2009
Order BY SalesYear, Amount Desc   
--Order By SalesYear, CustNo    
;

Select  SalesYear, CustNo, CustName1, Amount,
        Sum(Amount) Over(Partition By SalesYear Order By Amount desc) "Rolling Sum",
        Avg(Amount) Over(Partition By SalesYear Order By Amount Desc) "Rolling Avg",
        Max(Amount) Over(Partition By SalesYear Order By Amount Desc) "Rolling Maximum", 
        Min(Amount) Over(Partition By SalesYear Order By Amount Desc) "Rolling Minmum"                                                       
From SalesCustY
-- Where SalesYear = 2009
Order BY SalesYear, Amount Desc   
-- Order By SalesYear, CustNo    
;

-- Rolling Totals over multiple years
Select  SalesYear, CustNo, CustName1, Amount,
        Sum(Amount) Over(Partition By SalesYear Order By Amount desc) "Rolling Sum",
        Sum(Amount) Over(Partition By SalesYear)                      "Sum/Partition",
        Avg(Amount) Over(Partition By SalesYear Order By Amount Desc) "Rolling Avg",
        Avg(Amount) Over(Partition By SalesYear)                      "Avg/Partition"                                                       
From SalesCustY
Order BY SalesYear, Amount Desc ;  

-- Rolling Totals over multiple years including Totals per customer and year
Select  SalesYear, CustNo, CustName1, 
        Case When Grouping(SalesYear) = 1
             Then Sum(Amount) Over()
             When Grouping(CustNo) = 1 
             Then Sum(Amount) Over(Partition By SalesYear)
             Else Amount End                                          "Amount",
        Case When Grouping(CustNo) = 1 
             Then NULL 
             Else Sum(Amount) Over(Partition By SalesYear 
                                   Order By Amount Asc) end           "Rolling Sum",
        Sum(Amount) Over(Partition By SalesYear)                      "Sum/Partition",
        Case When Grouping(CustNo) = 1
             then NULL
             Else Avg(Amount) Over(Partition By SalesYear Order By Amount Asc)  
             End                                                      "Rolling Avg",
        Avg(Amount) Over(Partition By SalesYear)                      "Avg/Partition"                                                       
From SalesCustY
Group By Grouping Sets((SalesYear, CustNo, CustName1, Amount), (SalesYear), ())
Order BY SalesYear, Amount Asc  
;

 with SalesPlan as (Select * 
                      from (Values(2008, 2500,00 ), 
                                  (2009, 15000,00), 
                                  (2010, 20000,00)) x (SalesYear, Plan)),
      SalesCust as (Select SalesYear, CustNo, Sum(Total) TotalCustYear
                      From SalesVWYear
                      Group By SalesYear, CustNo),
      Totals    as (Select s.SalesYear, CustNo, TotalCustYear, Plan,
                           Sum(TotalCustYear) Over(Partition By s.SalesYear)                        "Total Year",                                                                 
                           Sum(TotalCustYear) Over(Partition By s.SalesYear Order By CustNo)        "Rolling Sum",
                           Sum(TotalCustYear) Over(Partition By s.SalesYear Order By CustNo) - Plan "Rolling Diff Plan"  
                    From SalesCust s join SalesPlan p on s.SalesYear = p.SalesYear)
 Select SalesYear, CustNo,   "Total Year", 
        TotalCustYear,       TotalCustYear * 100,0000 / "Total Year" "% Year", 
        "Rolling Sum",       "Rolling Sum" * 100,0000 / "Total Year" "% Rolling",
        Plan,                TotalCustYear * 100,0000 / Plan         "% Plan",
                             "Rolling Sum" * 100,0000 / Plan         "% Rolling Plan",
        "Rolling Diff Plan", "Rolling Diff Plan" * 100,0000 / Plan   "% Rolling Diff Plan"    
      from Totals
      Order By SalesYear, CustNo;

--====================================================================================================
-- OLAP Aggregate Functions
--====================================================================================================
Select  SalesYear, CustNo, CustName1, Amount,
        First_Value(CustNo)  Over(Partition By SalesYear Order By Amount desc) "First Value",
        Last_Value(CustNo)   Over(Partition By SalesYear)                      "Last Value" ,                                   
        Nth_Value(CustNo, 4) Over(Partition By SalesYear)                      "4th Value",                                            
        Nth_Value(CustNo, 4) From Last Over(Partition By SalesYear)            "4th From Last"                                                                                                             
From SalesCustY
-- Where SalesYear = 2009
Order BY SalesYear, Amount Desc  ;

------------------------------------------------------------------------------------------------------
-- Ratio to Report (Percent Caluclation)
------------------------------------------------------------------------------------------------------
  Select CustNo, SalesYear, Amount,
         Cast(Ratio_To_Report(Amount) Over(Partition By CustNo) * 100,0000
            as Dec(11, 2))                                                "Ratio Amount",
       Amount * 100,000 / Sum(Amount) Over(Partition By CustNo)            "% Amount"
  From SalesCustY
  Order By CustNo, SalesYear;     
  
-- Incl. Totals  
With x as (Select CustNo, SalesYear, Amount,
                  Ratio_To_Report(Amount) Over(Partition By CustNo) * 100,0000       "Ratio Amount",
                  Amount * 100,0000 / Sum(Double(Amount)) Over(Partition By CustNo)  "% Amount"
              From SalesCustY)
Select CustNo, SalesYear, Sum(Amount)                             "Total Amount", 
                          Cast(Sum("Ratio Amount") as Dec(11, 2)) "Ratio Amount", 
                          Cast(Sum("% Amount")     as Dec(11, 2)) "% Amount"
  From x
  Group By Grouping Sets((CustNo, SalesYear), (CustNo), ())
  Order By CustNo, SalesYear;               

------------------------------------------------------------------------------------------------------
-- Running Ratio / %
------------------------------------------------------------------------------------------------------
Select SalesYear, CustNo, CustName1, Amount,
       Cast(Ratio_To_Report(Amount) Over(Partition By SalesYear 
                                         Order By Amount Desc) * 100,00
            as Dec(7, 2))                                                         "Ratio Rolling",
       Cast(Dec(Amount, 11, 2) * 100,00 / Sum(Amount) Over(Partition By SalesYear 
                                                           Order By Amount Desc)  
            as Dec(7, 2))                                                         "% Rolling",
       Cast(Ratio_To_Report(Amount) Over(Partition By SalesYear) * 100,00
            as Dec(7, 2))                                                         "Ratio Amount",
       Cast(Dec(Amount, 11, 2) *100,00 / Sum(Amount) Over(Partition By SalesYear) 
            as Dec(7, 2))                                                         "% Amount"
  From SalesCustY
  Order By SalesYear, Amount Desc; 
  
  Values('1350,00 / 1350,00',                                       1350,00 / 1350,00), 
        (' 535,00 / (1350,00 + 535,00)',                             535,00 / (1350,00 + 535,00)),
        (' 470,00 / (1350,00 + 535,00 + 470,00)',                    470,00 / (1350,00 + 535,00 + 470,00)),
        (' 310,00 / (1350,00 + 535,00 + 470,00 + 310,00)',           310,00 / (1350,00 + 535,00 + 470,00 + 310,00)),
        (' 115,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00)',  115,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00));
        
  Values('1350,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00)', 1350,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00)), 
        (' 535,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00)', 535,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00)),
        (' 470,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00)', 470,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00)),
        (' 310,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00)', 310,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00)),
        (' 115,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00)', 115,00 / (1350,00 + 535,00 + 470,00 + 310,00 + 115,00));      

--====================================================================================================
-- Rows and Ranges
--====================================================================================================
-- Rows
------------------------------------------------------------------------------------------------------
 Select  SalesYear, CustNo, CustName1, Amount,
         Sum(Amount) Over(Partition by SalesYear
                          Order By Amount desc 
                          Rows between 1 preceding and 1 following) "Sum Rows +/- 1",
           Lag(Amount, 1, 0) Over(Partition By SalesYear
                                  Order By Amount Desc) 
         + Amount  
         + Lead(Amount, 1, 0) Over(Partition By SalesYear 
                                  Order By Amount Desc)            "Lag/Lead"                                                       
   From SalesCustY
   Where SalesYear = 2009
   Order BY SalesYear, Amount Desc;

-- Other Aggregate Functions
--------------------------------*              
Select  SalesYear, CustNo, CustName1, Amount,
        Sum(Amount) Over(Partition by SalesYear
                         Order By Amount desc 
                         Rows between 1 preceding and 1 following) "Sum Rows +/- 1",
        Avg(Amount) Over(Order By Amount Desc 
                         Rows between 1 preceding and 1 following) "Avg Rows +/- 1",
        Max(Amount) Over(Order By Amount Desc 
                         Rows between 1 preceding and 1 following) "Maximum Rows +/- 1", 
        Min(Amount) Over(Order By Amount Desc 
                         Rows between 1 preceding and 1 following) "Minmum Rows +/- 1"                                                       
From SalesCustY
Where SalesYear = 2009
Order BY SalesYear, Amount Desc;           

------------------------------------------------------------------------------------------------------
-- Range
------------------------------------------------------------------------------------------------------

Select * from SalesVWYearMonth;
Select * from SalesVwYear;

Select SalesYear, Sum(Total)
From SalesVwYear
Group By SalesYear;
                                                                                                                                                                                            
-- Range
-------------------------------------
 Select  SalesYear, Trim(CustNo) CustNo, Trim(CustName1) CustName1, Amount,
         Sum(Amount) Over(Partition by SalesYear
                          Order By Amount desc 
                          Range between 1000 preceding and 1000 following) "Sum +/- 1000",
         Avg(Amount) Over(Order By Amount Desc 
                          Range between 1000 preceding and 1000 following) "Avg +/- 1000",
         Max(Amount) Over(Order By Amount Desc 
                          Range between 1000 preceding and 1000 following) "Maximum +/- 1000", 
         Min(Amount) Over(Order By Amount Desc 
                          Range between 1000 preceding and 1000 following) "Minmum +/- 1000"                                                       
   From SalesCustY
   Where SalesYear = 2009
   Order BY SalesYear, Amount Desc;           

Values(4589,86 + 3741,95 , 
       2673,95 + 2634,30 ,
       2673,95 + 2634,20 + 1636,25 ,
       2634,20 + 1636,25);