[MSSQL] 날짜차이, 이전 행 정보 LAG LEAD

DATEDIFF, DATEPART

SELECT DATEDIFF(DAY,   '2021-06-12', '2021-07-13') AS [일차이]
     , DATEDIFF(MONTH, '2021-06-12', '2021-07-13') AS [개월차이] 
     , DATEDIFF(YEAR,  '2021-06-12', '2021-07-13') AS [년차이]

 

LAG, LEAD

https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16 

LAG (Transact-SQL) - SQL Server

SELECT empno  
     , ename
     , job
     , sal
     , LAG(sal) OVER(PARTITION BY job ORDER BY job, sal) AS sal_prev
     , LEAD(sal) OVER(PARTITION BY job ORDER BY job, sal) AS sal_next
  FROM emp
 WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN')
 ORDER BY job, sal
 
 
 CREATE TABLE T (a INT, b INT, c INT);   
GO  
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);   
  
SELECT b, c,   
    LAG(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i  
FROM T;

https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver16 

LEAD (Transact-SQL) - SQL Server

CREATE TABLE T (a INT, b INT, c INT);   
GO  
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);   
  
SELECT b, c,   
    LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i  
FROM T;