A query to get the minimum and max dates for values as they change over time in SQL Server

user_3634559 0
Viewed 47

I have a table that has monthly values for 3 metrics as per the following:

CREATE TABLE #Temp
(
StoreId INT
, FromDate DATE
, ToDate DATE
, AValue DECIMAL (18, 4)
, BValue DECIMAL (18, 4)
, CValue DECIMAL (18, 4)
)

INSERT INTO #Temp
(
    StoreId
  , FromDate
  , ToDate
  , AValue
  , BValue
  , CValue
)
VALUES
    (1, N'2020-01-01', N'2020-01-31', 5.5346, 0.0000, 0.0000)
  , (1, N'2020-02-01', N'2020-02-29', 5.5346, 0.0000, 0.0000)
  , (1, N'2020-03-01', N'2020-03-31', 5.5346, 0.0000, 0.0000)
  , (1, N'2020-04-01', N'2020-04-30', 5.5346, 0.0000, 2.8400)
  , (1, N'2020-05-01', N'2020-05-31', 5.5346, 0.0000, 2.8400)
  , (1, N'2020-06-01', N'2020-06-30', 5.5346, 0.0000, 4.8400)
  , (1, N'2020-07-01', N'2020-07-31', 5.5346, 0.0000, 0.0000)
  , (2, N'2020-01-01', N'2020-01-31', 2.1234, 1.0000, 0.0000)
  , (2, N'2020-02-01', N'2020-02-29', 2.1234, 2.0000, 0.0000)
  , (2, N'2020-03-01', N'2020-03-31', 2.1234, 2.0000, 0.0000)
  , (2, N'2020-04-01', N'2020-04-30', 2.4567, 2.0000, 1.1020)
  , (2, N'2020-05-01', N'2020-05-31', 2.4567, 2.0000, 1.1020)
  , (2, N'2020-06-01', N'2020-06-30', 2.4567, 2.0000, 1.1020)
  , (2, N'2020-07-01', N'2020-07-31', 2.4567, 2.0000, 1.1020)

and I need to create a condensed timeline for a store and track how the AValue, BValue, and CValue changes over time, having a FromDate and ToDate for each section. The resulting table would be:

CREATE TABLE #Temp2
(
StoreId INT
, FromDate DATE
, ToDate DATE
, AValue DECIMAL (18, 4)
, BValue DECIMAL (18, 4)
, CValue DECIMAL (18, 4)
)

INSERT INTO #Temp2
(
    StoreId
  , FromDate
  , ToDate
  , AValue
  , BValue
  , CValue
)
VALUES
    (1, N'2020-01-01', N'2020-03-31', 5.5346, 0.0000, 0.0000)
  , (1, N'2020-04-01', N'2020-05-31', 5.5346, 0.0000, 2.8400)
  , (1, N'2020-06-01', N'2020-06-30', 5.5346, 0.0000, 4.8400)
  , (1, N'2020-07-01', N'2020-07-31', 5.5346, 0.0000, 0.0000)
  , (2, N'2020-01-01', N'2020-01-31', 2.1234, 1.0000, 0.0000)
  , (2, N'2020-02-01', N'2020-03-31', 2.1234, 2.0000, 0.0000)
  , (2, N'2020-04-01', N'2020-07-31', 2.4567, 2.0000, 1.1020)

I started looking at using ROW_NUMBER and the LEAD functions but I still haven't figured it out:

SELECT  StoreId
      , FromDate
      , ToDate
      , AValue
      , BValue
      , CValue
      , ROW_NUMBER() OVER (PARTITION BY StoreId
                           ORDER BY FromDate
                                  , ToDate)                                                                                                       RecordNumber
      , CONVERT(VARCHAR (30), AValue) + '_' + CONVERT(VARCHAR (30), BValue) + '_' + CONVERT(VARCHAR (30), CValue)                                 CombinedValues
      , LEAD(CONVERT(VARCHAR (30), AValue) + '_' + CONVERT(VARCHAR (30), BValue) + '_' + CONVERT(VARCHAR (30), CValue), 1) OVER (PARTITION BY StoreId
                                                                                                                                 ORDER BY FromDate
                                                                                                                                        , ToDate) NextCombinedValues
FROM    #Temp

Note that I can't just use Max(FromDate) and Max(ToDate) while grouping by StoreId, AValue, BValue, and CValue as it would produce an incorrect result.

tsql sql-server
2 Answers

I eventually figured this out:

;WITH x AS (
    SELECT  StoreId
          , FromDate
          , ToDate
          , AValue
          , BValue
          , CValue
          , CONVERT(VARCHAR (30), AValue) + '_' + CONVERT(VARCHAR (30), BValue) + '_' + CONVERT(VARCHAR (30), CValue) CombinedValues
    FROM    #Temp
)
   , y AS (
    SELECT  x.StoreId
          , x.FromDate
          , x.ToDate
          , x.AValue
          , x.BValue
          , x.CValue
          , x.CombinedValues
          , DENSE_RANK() OVER (PARTITION BY x.StoreId
                               ORDER BY x.FromDate)                                           StoreDateRank
          , DENSE_RANK() OVER (PARTITION BY x.StoreId
                                          , x.CombinedValues
                               ORDER BY x.FromDate)                                           ValuesAndDatesRank
          , DENSE_RANK() OVER (PARTITION BY x.StoreId
                               ORDER BY x.FromDate) - DENSE_RANK() OVER (PARTITION BY x.StoreId
                                                                                    , x.CombinedValues
                                                                         ORDER BY x.FromDate) ValueAndDatesGroups
    FROM    x
)
SELECT      y.StoreId
          , MIN(y.FromDate) FromDate
          , MAX(y.ToDate)   ToDate
          , y.AValue
          , y.BValue
          , y.CValue
FROM        y
GROUP BY    y.StoreId
          , y.AValue
          , y.BValue
          , y.CValue
          , y.ValueAndDatesGroups
ORDER BY    y.StoreId
          , FromDate
user_36345590

Have you checked temporal tables?

They give you a way of tracking changes over time. And they also automatically add the to/from columns seamlessly.

When you do a SELECT statement, you can use FOR SYSTEM_TIME and you’re able to see how the table was at that point of time, or maybe the changes made to a specific PK.

user_116701830
Related