SQL Server: Generate squashed range data from daily dates by an id

Question

Basically to sum up the goal. I want to generate something using the following data. Where it subtotals like excel where "each change in ... ordered however" generate summary records and only generate summary records and squash date ranges.

All I want are the count records would generate rows and would copy the fee data except for the begin/end fields would be the ranged dates. So first record would be fee 9858 from 3/31 - 4/14 for example. Each count row would be a new fee record.

I am not sure which combination of grouping, partition by.... and such to get what I need or if there is something else I can use. I can provide sql if needed but I am mainly looking for finding the right combination of tools(partition by, grouping, rollup....) that would provide this functionality.

Answer 1

WITH [ag]
  AS (SELECT *,
             LAG([Fee_ID]) OVER (ORDER BY [FeeTypeID], [FeeBeginDate]) [FirstFee],
             LAG([Fee_ID]) OVER (ORDER BY [FeeTypeID], [FeeBeginDate] DESC) [LastFee]
        FROM [dbo].[HHTFees]
       WHERE [Retailer] = 517),
     [agf]
  AS (SELECT *,
             'Beg' [FeeStopType]
        FROM [ag]
       WHERE [ag].[FirstFee] <> [ag].[Fee_ID]
          OR [ag].[FirstFee] IS NULL),
     [agl]
  AS (SELECT *,
             'End' [FeeStopType]
        FROM [ag]
       WHERE [ag].[LastFee] <> [ag].[Fee_ID]
          OR [ag].[LastFee] IS NULL),
     [results]
  AS (SELECT *
        FROM [agf]
      UNION
      SELECT *
        FROM [agl]),
     [indexed]
  AS (SELECT *,
             ROW_NUMBER() OVER (ORDER BY [results].[FeeTypeID], [results].[FeeBeginDate]) [RowNum]
        FROM [results])
SELECT      [Starts].[Retailer],
            [Starts].[Chain_key],
            [Starts].[Fee_ID],
            [Starts].[FeeTypeID],
            [Starts].[FeeChgTypeID],
            [Starts].[Fee],
            [Starts].[FeeDescription],
            [Starts].[FeeTypeDescription],
            [Starts].[FeeBeginDate],
            [Ends].[FeeEndDate],
            [Starts].[FeeAmt],
            [Starts].[HHT_ID],
            [Starts].[CreatedBy],
            [Starts].[CreatedDate],
            [Starts].[ModifiedBy],
            [Starts].[ModifiedDate],
            [Starts].[MsgID],
            [Starts].[ScopeOrder],
            [Starts].[Scope],
            [Starts].[FirstFee],
            [Starts].[LastFee],
            [Starts].[FeeStopType],
            [Starts].[RowNum]
  FROM      [indexed] [Starts]
 INNER JOIN [indexed] [Ends]
    ON [Starts].[Fee_ID] = [Ends].[Fee_ID]
   AND [Ends].[RowNum]   = [Starts].[RowNum] + 1;

I used lag to find where the fee id changed sorted by feetype / start date. This allowed me to mimic when fee id changed sorted by begin date like excel subtotal. Now to optimize and tweak to fit the set of data.