SQL - LEAD and LAG Query-CodePudding

From this logic:

SELECT  HotelId, CompetitorId,
        RateShopDate AS ShopDate,
        Rate, RateRemark,
        RowNumber
    FROM
    (
        SELECT  HotelId, CompetitorId, RateShopDate, ChannelId, RequestedRateType, Rate, RateRemark,
                PreviousRate, PreviousRateRemark, NextRate, NextRateRemark,
                ROW_NUMBER() OVER ( ORDER BY HotelId, CompetitorId, ChannelId, RequestedRateType, RateShopDate ) AS RowNumber
            FROM
            (
                SELECT  HotelId, CompetitorId, RateShopDate, ChannelId, RequestedRateType, Rate, RateRemark, RowNumber,
                        LAG( Rate ) OVER
                        (
                            PARTITION BY HotelId, CompetitorId, ChannelId, RequestedRateType
                            ORDER BY RowNumber
                        ) AS PreviousRate,
                        LAG( RateRemark ) OVER
                        (
                            PARTITION BY HotelId, CompetitorId, ChannelId, RequestedRateType
                            ORDER BY RowNumber
                        ) AS PreviousRateRemark,
                        LEAD( Rate ) OVER
                        (
                            PARTITION BY HotelId, CompetitorId, ChannelId, RequestedRateType
                            ORDER BY RowNumber
                        ) AS NextRate,
                        LEAD( RateRemark ) OVER
                        (
                            PARTITION BY HotelId, CompetitorId, ChannelId, RequestedRateType
                            ORDER BY RowNumber
                        ) AS NextRateRemark
                    FROM
                    (
                        SELECT  HotelId, CompetitorId, RateShopDate, ChannelId, RequestedRateType, Rate, RateRemark,
                                ROW_NUMBER() OVER ( ORDER BY HotelId, CompetitorId, ChannelId, RequestedRateType, RateShopDate ) AS RowNumber
                            FROM #TempRateShop
                    ) Rates
            ) PrevNextRates
                WHERE COALESCE(Rate, 0) <> COALESCE(PreviousRate, 0)
                OR COALESCE(RateRemark, '') <> COALESCE(PreviousRateRemark, '')
                OR COALESCE(Rate, 0) <> COALESCE(NextRate, 0)
                OR COALESCE(RateRemark, '') <> COALESCE(NextRateRemark, '')
    ) FilteredRates
        ORDER BY RateShopDate

I am attempting to get the start and end ShopDate for each rate change. But since there is a potential for rates to go from one to another and back to the original, I am having a little difficulty; e.g. rate goes from $90 to $95 for a period of time and later goes back down to $90.

What I am trying to get is this:

The only solution I have come up with is using LEAD/LAG and just order it by the RowNumber column and then filtering it by odd/even numbers...but I don't really think that is the best solution.

    LEAD ( RateShopDate ) OVER
    (
        ORDER BY RowNumber
    ) AS ShopEndDate

CodePudding user response：

This is a type of gaps-and-islands problem.

There are many solutions, here is one:

Use LAG to identify rows at the start of each group
A running conditional COUNT gives us an ID for each group
Then simply group up by that ID

WITH PrevValues AS (
    SELECT *,
      IsStart = CASE WHEN LAG(rs.Rate, 1, -1) OVER (PARTITION BY rs.HotelId, rs.CompetitorId
                    ORDER BY rs.RateShopDate) <> rs.Rate THEN 1 END
    FROM #RateShop rs
),
Groupings AS (
    SELECT *,
      GroupId = COUNT(IsStart) OVER (PARTITION BY rs.HotelId, rs.CompetitorId
                    ORDER BY rs.RateShopDate ROWS UNBOUNDED PRECEDING)
    FROM PrevValues rs
)
SELECT
  rs.HotelId,
  rs.CompetitorId,
  StartDate  = MIN(rs.RateShopDate),
  EndDate    = MAX(rs.RateShopDate),
  Rate       = MIN(rs.Rate),
  RateRemark = STRING_AGG(rs.RateRemark, '; ')
FROM Groupings rs
GROUP BY
  rs.HotelId,
  rs.CompetitorId,
  rs.GroupId;

db<>fiddle