Create a slowly changing dimension in SQL Server using SQL query - Stack Overflow

admin 管理员组

文章数量: 1086019

I have an employee seating table and I have taken a snapshot of dummy data with out sharing the actual data

table data

I need a logic to convert this to this output

output table

How would I achieve this with just SQL?

I have tried few tricks but nothing is working

Here is the code to create the dataset just in case you need it

create table #test
(
    start_date date,
    end_date date,
    emp_id varchar(20),
    seating_type varchar(10)
)

create table #test
(
    start_date date,
    end_date date,
    emp_id varchar(20),
    seating_type varchar(10)
)

insert into #test
select '01-01-2025','01-10-2025','123','abc'
union
select '01-11-2025','01-20-2025','123','abc'
union
select '01-21-2025','01-31-2025','123','def'
union
select '02-01-2025','02-10-2025','123','abc'
union
select '02-11-2025','02-20-2025','123','def'
union
select '02-21-2025','02-28-2025','123','gih'
union
select '03-01-2025','03-10-2025','123','def'
union
select '02-10-2025','02-25-2025','456','def'
union
select '02-26-2025','03-10-2025','456','abc'
union
select '03-11-2025','03-27-2025','456','abc'
union
select '03-28-2025','04-10-2025','456','gih'

I have an employee seating table and I have taken a snapshot of dummy data with out sharing the actual data

table data

I need a logic to convert this to this output

output table

How would I achieve this with just SQL?

I have tried few tricks but nothing is working

Here is the code to create the dataset just in case you need it

create table #test
(
    start_date date,
    end_date date,
    emp_id varchar(20),
    seating_type varchar(10)
)

create table #test
(
    start_date date,
    end_date date,
    emp_id varchar(20),
    seating_type varchar(10)
)

insert into #test
select '01-01-2025','01-10-2025','123','abc'
union
select '01-11-2025','01-20-2025','123','abc'
union
select '01-21-2025','01-31-2025','123','def'
union
select '02-01-2025','02-10-2025','123','abc'
union
select '02-11-2025','02-20-2025','123','def'
union
select '02-21-2025','02-28-2025','123','gih'
union
select '03-01-2025','03-10-2025','123','def'
union
select '02-10-2025','02-25-2025','456','def'
union
select '02-26-2025','03-10-2025','456','abc'
union
select '03-11-2025','03-27-2025','456','abc'
union
select '03-28-2025','04-10-2025','456','gih'

• sql
• sql-server

Share Improve this question Follow edited Mar 27 at 19:50 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 27 at 17:32 PratPrat 2333 bronze badges 7

• You are just combining rows 1 and 2 (using the start date of row 1 and the end date for row 2). This combining seem to only occur if the rows are adjacent and of the same empt_id+seating_type. This is a gaps and island problem that requires us to create groups of uninterrupted empt_id+seating_type combinations. – Bart McEndree Commented Mar 27 at 17:47
• in the example yes, but the ask is to get min startdate and max enddate for each instance(change) for the seatingtype – Prat Commented Mar 27 at 17:51
• Please add more example rows with a different emp_id than 123 or remove the emp_id column as not important to the solution. – Bart McEndree Commented Mar 27 at 17:51
• I am not sure what the ask is from you ? empid is required as the seating type is tagged to empid. I just shared one empid to keep it simple. Just creating a group using empid+seating_type will not solve the problem as in the sample you can see 'abc' seating type came again after 'def'. so we would need two rows for abc in the final output – Prat Commented Mar 27 at 17:57
• Without multiple emp_id values I cannot be sure that my solution works for the full variety of examples you might have. – Bart McEndree Commented Mar 27 at 18:08

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 2

Generate island groups using 2 types of row number partitioning

WITH CTE as
(
SELECT *, 
      ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY start_date) rn,
      ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY start_date) -  ROW_NUMBER() OVER (PARTITION BY emp_id,Seating_Type ORDER BY start_date) grp
FROM #test
)
SELECT Min(start_date) as Start_date, max(end_date) as End_date, emp_id, seating_type
FROM CTE
GROUP BY grp,  emp_id, seating_type
ORDER BY min(rn)

fiddle

本文标签： Create a slowly changing dimension in SQL Server using SQL queryStack Overflow