My goal is to have a single row by id.

Now I have multiple rows like this for each one

For example user number 001 has this:

My goal is to when the Ini.Date of the following row is the next day it update the end date and sums the Qnt.

My expected ouput would be:

Can anyone please point me in the right direction ?

CodePudding user response：

This is a classic gaps-and-islands problem.

There are a number of solutions. Here is one common one:

• Use LAG to identify rows which have gaps.
• Use a windowed conditional COUNT to number the groups between the gaps with an ID.
• Group by that ID.

WITH PrevValues AS (
    SELECT *,
      IsNewGroup = CASE WHEN LAG(t.[End Date]) OVER (ORDER BY t.[End Date])
                        < DATEADD(day, -1, t.[Ini Date]) THEN 1 END
    FROM YourTable t
),
Groups AS (
    SELECT *,
      GroupId = COUNT(t.IsNewGroup) OVER (ORDER BY t.[Ini Date])
    FROM PrevValues t
)
SELECT
  [Ini Date] = MIN(t.[Ini Date]),
  [End Date] = MAX(t.[End Date]),
  Qnty = SUM(t.Qnty)
FROM Groups t
GROUP BY
  t.GroupId;

db<>fiddle

CodePudding user response：

You can try code like below. Explanation is in the comments.

Note : You will need to use separate loop for each user.

-- Variables to hold current and next dates
DECLARE @CurrentDate date, @NextDate date

-- Initially select min date as current date
SELECT @CurrentDate = MIN(IniDate) FROM @TempTable;
-- Select next date as min date which is greater than current selected min date
SELECT @NextDate = MIN(IniDate) FROM @TempTable WHERE IniDate > @CurrentDate;

-- If next date is null then we reach at the end of dataset
WHILE (@NextDate IS NOT NULL)
BEGIN

    -- Check if we add 1 day in current day is same as next day then update current row and delete row with next date
    IF DATEADD(day, 1, @CurrentDate) = @NextDate
    BEGIN

        -- Update EndDate & Quantity with current date
        UPDATE t1
        SET t1.EndDate = t2.IniDate,
            t1.Qnty = t1.Qnty   t2.Qnty
        FROM @TempTable t1 
            JOIN @TempTable t2 
                ON t1.EndDate = @CurrentDate AND t2.IniDate = @NextDate

        -- Delete next date row
        DELETE FROM @TempTable WHERE IniDate = @NextDate

    END

    -- Set current date as next date
    SET @CurrentDate = @NextDate;
    -- Recalculate next date
    SELECT @NextDate = MIN(IniDate) FROM @TempTable WHERE IniDate > @CurrentDate;

END

Here is the updated code which will work for multiple users as well. Added JOIN with UserId.

-- Variables to hold current and next dates
DECLARE @CurrentDate date, @NextDate date

-- Initially select min date as current date
SELECT @CurrentDate = MIN(IniDate) FROM @TempTable;
-- Select next date as min date which is greater than current selected min date
SELECT @NextDate = MIN(IniDate) FROM @TempTable WHERE IniDate > @CurrentDate;

-- If next date is null then we reach at the end of dataset
WHILE (@NextDate IS NOT NULL)
BEGIN

    -- Check if we add 1 day in current day is same as next day then update current row and delete row with next date
    IF DATEADD(day, 1, @CurrentDate) = @NextDate
    BEGIN

        -- Update EndDate & Quantity with current date
        -- Use join with UserId as well
        UPDATE t1
        SET t1.EndDate = t2.IniDate,
            t1.Qnty = t1.Qnty   t2.Qnty
        FROM @TempTable t1 
            JOIN @TempTable t2 
                ON t1.EndDate = @CurrentDate AND t2.IniDate = @NextDate AND t1.UserId = t2.UserId

        -- Delete next date row with respective UserId
        -- Add condition t1.IniDate <> t2.IniDate so that row does not have join with itself
        DELETE t2
        FROM @TempTable t1 
            JOIN @TempTable t2 
                ON t1.IniDate <> t2.IniDate AND t1.EndDate = t2.IniDate AND t1.UserId = t2.UserId

    END

    -- Set current date as next date
    SET @CurrentDate = @NextDate;
    -- Recalculate next date
    SELECT @NextDate = MIN(IniDate) FROM @TempTable WHERE IniDate > @CurrentDate;

END