Is it possible to create sequence(?) data with SQL Server?

j13ufse2 于 2023-10-15 发布在 SQL Server

关注(0)|答案(1)|浏览(107)

I have the employee data like this:
| ID | previous_org | new_org | event | event_date |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | | A | Hire | 202201 |
| 1 | A | B | Transfer | 202207 |
| 1 | | B | Retired | 202301 |
| 2 | | A | Hire | 202201 |
| 2 | A | B | Transfer | 202207 |
| 2 | B | C | Transfer | 202301 |
| 2 | | C | Retired | 202308 |

I want to add two columns to above data and manage it as historical data.

The columns I want to add are start_date and end_date .

It is difficult to explain what I want to do, so I will show the desired result below.
| ID | previous_org | new_org | event | event_date | start_date | end_date |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | | A | Hire | 202201 | 202201 | 202206 |
| 1 | A | B | Transfer | 202207 | 202207 | 202212 |
| 1 | | B | Retired | 202301 | 202301 | |
| 2 | | A | Hire | 202201 | 202201 | 202206 |
| 2 | A | B | Transfer | 202207 | 202207 | 202212 |
| 2 | B | C | Transfer | 202301 | 202301 | 202307 |
| 2 | | C | Retired | 202308 | 202308 | |

The start_date column contains the date in the event_date column of the row.

The end_date column contains the date one month before the date in the event_date column for the next event.

Calculations must be made for each employee.

Can I get this result with SQL Server?

I understand that this can be accomplished using something other than SQL Server, but in this case I must use SQL Server.

Any answer would be helpful.

Thank you.

sql-server

来源：https://stackoverflow.com/questions/77236758/is-it-possible-to-create-sequence-data-with-sql-server

1条答案

按热度按时间

sqyvllje1#

Code:

with cte as(
SELECT 
    ID, previous_org, new_org, event, event_date, startdate=event_date,
    Lead(event_date) OVER (PARTITION BY ID ORDER BY event_date) as ed
  FROM employee)
select ID, previous_org, new_org, event, event_date, startdate,
  format(dateadd(month,-1,cast(iif(ed is not null,concat(ed,'01'),null) as date)),'yyyyMM') as enddate from cte

This code uses a CTE to derive the ed column (temporary column) as the next event_date using the LEAD function. Then, it calculates the enddate column by concatenating the ed column with '01' to get the complete date format yyyyMMdd , and then it is casted to a date data type. Then DATEADD - is used to substract one month from the derived value. Later, it formats as a string in the 'yyyyMM' format using the FORMAT function.

demo

赞(0）回复(0）举报 2023-10-15

我来回答

Is it possible to create sequence(?) data with SQL Server?

1条答案

相关问题

热门标签

最新问答