SQL Group by balance-CodePudding

I have a table like below:

I want the results to be like below which fetch the start and end of the balance but we can't use group by as balance should be grouped only based on consecutive groups. can you please help me with this ?:

CodePudding user response：

There is most certainly a duplicate of this question, however, it is easier to crank out an answer than to search. These types of problems, data in the order inserted or shown with no order indicator, can simply be solved by two derivative queries. The first to use LAG or LEAD to check for gaps and the second to sum up the changes which are represented by a value of 1 as opposed to 0. The key here, using MSSQL Server, is SUM(x) OVER (ORDER BY Date ROWS UNBOUNDED PRECEDING).

DECLARE @T TABLE(balance INT, date DATETIME)
INSERT @T VALUES
(36,'1/1/2020'),
(36,'1/2/2020'),
(36,'1/3/2020'),
(24,'1/4/2020'),
(24,'1/5/2020'),
(36,'1/6/2020'),
(36,'1/7/2020'),
(37,'1/8/2020'),
(38,'1/9/2020')

;WITH GapsMarked AS
(
    --If the prev value by date (by natural order of data above) does not equal this value mark it as a boundry 
    SELECT *,       
        IsBoundry = CASE WHEN ISNULL(LAG(balance) OVER (ORDER BY date),balance) = balance THEN 0 ELSE 1 END 
    FROM @T
)
,VirtualGroup AS
(
    SELECT 
        *,
        --This serialzes the marked groups into seequntial clusters 
        IslandsMarked = SUM(IsBoundry) OVER (ORDER BY Date ROWS UNBOUNDED PRECEDING)
    FROM 
        GapsMarked
)
SELECT 
    MAX(balance) AS balance,
    MIN(date) AS start,
    MAX(date) AS [end]
FROM 
    VirtualGroup
GROUP BY
    IslandsMarked

CodePudding user response：

select balance, min(start), max(end) from table where balance is in ( select balance from table group by balance)

Hope it will help you