(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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; *****************************************************