How to create sequence data with SQL Server?

Question

I have the data like this.

ID	prev_org	org	event	event_date
1	-	A	Hire	202301
1	A	B	Transfer	202304
2	-	A	Hire	202301
2	-	A	Leave	202304
2	-	A	Return	202311
2	A	B	Transfer	202401

And What I want to achieve is like this.

ID	prev_org	org	event	prev_org_start_date	event_date
1	-	A	Hire	-	202301
1	A	B	Transfer	202301	202304
2	-	A	Hire	-	202301
2	-	A	Leave	-	202304
2	-	A	Return	-	202311
2	A	B	Transfer	202301	202401

In short, I want to know how long each employee has belonged to the same organization. Employee A joined the company in January 2023 and remained in organization A until April 2023, when he was transferred to organization B. Therefore, this employee was in organization A from January 2023, so column "prev_org_start_date" is 202301.

Employee B, on the other hand, had a leave of absence and a return to work, but was in organization A from January 2023 until his move in January 2024, so the "prev_org_start_date" column would be 202301.

Due to the company's development environment I need to implement this in SQL server. Any advice would be appreciated.

Answer 1

The following assumes that the date you want it always when they were hired.

If this logic is incorrect, update the question and be more specific

DECLARE @t TABLE (ID int, prev_org varchar(10), org varchar(10), event varchar(10), event_date int)
INSERT INTO @t values 
(1, '-', 'A', 'Hire', 202301),
(1, 'A', 'B', 'Transfer', 202304),
(2, '-', 'A', 'Hire', 202301),
(2, '-', 'A', 'Leave', 202304),
(2, '-', 'A', 'Return', 202311),
(2, 'A', 'B', 'Transfer', 202401)

SELECT 
    t.ID,
    t.prev_org,
    t.org,
    t.event,
    h.event_date as Prev_org_start_date,
    t.event_date
    FROM @t t 
    LEFT JOIN (SELECT ID, Org, event_date FROM @t WHERE event = 'Hire') h 
        on t.id = h.id and t.prev_org = h.org
        

Here's a working fiddle so you can see the results.

https://sqlfiddle.com/sql-server/online-compiler?id=82898dc8-e450-4841-b27c-54f66bf4cc9f

Answer 2

Check if this solution works for your question.

SELECT 
  id,
  prev_org,
  org,
  event,
  CASE 
   WHEN event = 'transfer' THEN min(event_date) OVER(PARTITION BY id ORDER BY event_date) 
   ELSE null
  END prev_org_start_date,
  event_date
FROM employee_events