DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 95--------------------------------------------------
       SQL Server 2012 Analytical Queries
       By: srinivasma_exceldbp Date: November 9, 2014, 9:12 pm
       ---------------------------------------------------------
       -- create a table that has a column for ordering the data
       CREATE TABLE #numbers (
       nbr    INT
       ,tempus DATE  -- used for ordering the data
       );
       -- insert some sample data
       INSERT INTO #numbers
       (tempus,nbr)
       VALUES
       ('1/1/2018',1)
       ,('2/2/2018',2)
       ,('3/3/2018',3)
       ,('4/4/2018',4)
       ,('5/5/2018',5)
       ,('6/6/2018',6)
       ,('7/7/2018',7)
       ,('8/8/2018',8)
       ,('9/9/2018',9)
       ;
       -- run an ordinary query ordering by the tempus columns
       SELECT nbr
       FROM #numbers
       ORDER BY tempus;
       -- show the nbr value in the current row and in the previous row
       -- the first row retrieved has a NULL for the previous nbr
       SELECT nbr
       ,LAG(nbr, 1) OVER (ORDER BY tempus) AS prevNbr
       FROM #numbers
       ORDER BY tempus;
       -- return the nbr value in the following row
       -- the last row retrieved has a NULL for the following nbr
       SELECT nbr
       ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
       ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
       FROM #numbers
       ORDER BY tempus;
       -- return the first value
       SELECT nbr
       ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
       ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
       ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr
       FROM #numbers
       ORDER BY tempus;
       -- return the last value
       -- notice how it is really the last value so far
       SELECT nbr
       ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
       ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
       ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr
       ,LAST_VALUE(nbr)  OVER (ORDER BY tempus) AS lastNbr
       FROM #numbers
       ORDER BY tempus;
       -- modify code to always return the last value
       SELECT nbr
       ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
       ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
       ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr
       ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN
       UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lastNbr
       FROM #numbers
       ORDER BY tempus;
       -- this returns the same results as the previous query
       SELECT nbr
       ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
       ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
       ,FIRST_VALUE(nbr) OVER (ORDER BY tempus) AS firstNbr
       ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN
       CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr
       FROM #numbers
       ORDER BY tempus;
       -- notice that the syntax for lastNbr is not what is needed for
       firstNbr
       SELECT nbr
       ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
       ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
       ,FIRST_VALUE(nbr) OVER (ORDER BY tempus ROWS BETWEEN
       CURRENT ROW AND UNBOUNDED FOLLOWING) AS firstNbr
       ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN
       CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr
       FROM #numbers
       ORDER BY tempus;
       -- fix the previous query to always show the very first value
       SELECT nbr
       ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
       ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
       ,FIRST_VALUE(nbr) OVER (ORDER BY tempus ROWS BETWEEN
       UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS firstNbr
       ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN
       CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr
       FROM #numbers
       ORDER BY tempus;
       SELECT nbr
       ,LAG (nbr, 1) OVER (ORDER BY tempus) AS prevNbr
       ,LEAD(nbr, 1) OVER (ORDER BY tempus) AS nextNbr
       ,FIRST_VALUE(nbr) OVER (ORDER BY tempus ROWS BETWEEN
       UNBOUNDED PRECEDING AND CURRENT ROW) AS firstNbr
       ,LAST_VALUE(nbr)  OVER (ORDER BY tempus ROWS BETWEEN
       CURRENT ROW AND UNBOUNDED FOLLOWING) AS lastNbr
       FROM #numbers
       ORDER BY tempus;
       DROP TABLE #numbers;
       *****************************************************