ms-sql–lead抓取與下筆資料日期差異天數，依群組條件(每月)重置，該月最後一筆天期計算至下月初

SELECT 
    t1.TransactionType,
    t1.TransactionDate,
    YEAR(t1.TransactionDate) AS TransactionYear,
    MONTH(t1.TransactionDate) AS TransactionMonth,
    LEAD(t1.TransactionDate, 1, DATEADD(day, 1, EOMONTH(t1.TransactionDate)))
            OVER (PARTITION BY t1.TransactionType,YEAR(t1.TransactionDate),MONTH(t1.TransactionDate) ORDER BY t1.TransactionDate) AS NextTransactionDate,
    DATEDIFF(day, t1.TransactionDate, 
        LEAD(t1.TransactionDate, 1, DATEADD(day, 1, EOMONTH(t1.TransactionDate)))
            OVER (PARTITION BY t1.TransactionType,YEAR(t1.TransactionDate),MONTH(t1.TransactionDate) ORDER BY t1.TransactionDate)) AS DifferenceInDays
FROM Transactions t1
GROUP BY 
    t1.TransactionType,
    t1.TransactionDate,
    YEAR(t1.TransactionDate),
    MONTH(t1.TransactionDate)
ORDER BY 
    t1.TransactionType,
    t1.TransactionDate;

LEAD (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )  provides access to a row at a specified physical offset which follows the current row. It allows you to query more than one row in a SQL statement without having to join the table to itself

The  EOMONTH function in SQL is used to get the last day of the month for a specified date

DATEADD(day, 1, EOMONTH(t1.TransactionDate)) 找出月底再+1得到下月初