Home Forums SQL Server 2008 T-SQL (SS2K8) Reduce records in a slowly changing dimension table RE: Reduce records in a slowly changing dimension table

  • So, are you looking for something like this?

    IF OBJECT_ID('dbo.GroupMembers', 'U') IS NOT NULL DROP TABLE dbo.GroupMembers;

    CREATE TABLE dbo.GroupMembers
    (
        GroupCode VARCHAR(1) NOT NULL,
        GroupMember INT NOT NULL,
        StartDate DATE NOT NULL,
        EndDate DATE NULL
    );


    --Some of my groups have proper ranges for start and end dates
    INSERT INTO dbo.GroupMembers VALUES ('A', 1, '20000101', '20170101');

    --Some of my groups have were populated with a snapshot for every day
    INSERT INTO dbo.GroupMembers VALUES ('B', 2, '20000101', '20000101');
    INSERT INTO dbo.GroupMembers VALUES ('B', 2, '20000102', '20000102');
    INSERT INTO dbo.GroupMembers VALUES ('B', 2, '20000103', '20000103');
    INSERT INTO dbo.GroupMembers VALUES ('B', 2, '20000104', '20000104');

    --Other groups are open ended and that is fine
    INSERT INTO dbo.GroupMembers VALUES ('C', 3, '20000104', NULL);

    --Some members may be removed and then re-added
    INSERT INTO dbo.GroupMembers VALUES ('D', 4, '20170101', '20170101');
    INSERT INTO dbo.GroupMembers VALUES ('D', 4, '20170102', '20170102');
    INSERT INTO dbo.GroupMembers VALUES ('D', 4, '20170104', '20170104');
    INSERT INTO dbo.GroupMembers VALUES ('D', 4, '20170105', '20170105');


    SELECT * FROM dbo.GroupMembers;

    WITH base AS (
    SELECT
      [GroupCode]
      , [GroupMember]
      , [StartDate]
      , [EndDate]
      , rn = ROW_NUMBER() OVER (PARTITION BY [GroupCode], [GroupMember] ORDER BY [StartDate])
    FROM
      dbo.GroupMembers
    ), Interim AS (
    SELECT
      .[GroupCode]
      , .[GroupMember]
      , .[StartDate]
      , .[EndDate]
      , .[rn]
      , [GroupDate] = DATEADD(DAY,-.[rn],.[StartDate])
    FROM
      base b
    )
    SELECT
      .[GroupCode]
      , .[GroupMember]
      , [StartDate] = MIN(.[StartDate])
      , [EndDate] = MAX(.[EndDate])
    FROM
      [Interim] i
    GROUP BY
      .[GroupCode]
      , .[GroupMember]
      , .[GroupDate];