SQL Server Find the value present in more than one date ranges

beq87vna 于 2023-03-07 发布在其他

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

I have a table similar to this. I want to find the users active in more than one department at any time in this data. Please suggest the SQL query for this. In the below example, U1 will need to be returned as U1 was active from 2021-04-01 to 2021-12-31 in more than one department

+--------+------------+----------------+--------------+
| UserId | Department | ActiveFromDate | ActiveToDate |
+--------+------------+----------------+--------------+
| U1     | D1         | 2021-01-01     | 2021-12-31   |
+--------+------------+----------------+--------------+
| U1     | D2         | 2021-04-01     | 2022-12-31   |
+--------+------------+----------------+--------------+
| U2     | D2         | 2021-01-01     | 2021-03-31   |
+--------+------------+----------------+--------------+
| U3     | D1         | 2021-01-01     | 2022-12-31   |
+--------+------------+----------------+--------------+
| U2     | D1         | 2021-04-01     | 2022-12-31   |
+--------+------------+----------------+--------------+

sql-server

来源：https://stackoverflow.com/questions/75611388/find-the-value-present-in-more-than-one-date-ranges

1条答案

按热度按时间

gj3fmq9x1#

You can get the desired result using self-join and the below query

Sample Table and Data:

CREATE TABLE SampleData (
  UserId VARCHAR(50),
  Department VARCHAR(50),
  ActiveFromDate DATE,
  ActiveToDate DATE
);

INSERT INTO SampleData (UserId, Department, ActiveFromDate, ActiveToDate)
VALUES 
  ('U1', 'D1', '2021-01-01', '2021-12-31'),
  ('U1', 'D2', '2021-04-01', '2022-12-31'),
  ('U2', 'D2', '2021-01-01', '2021-03-31'),
  ('U3', 'D1', '2021-01-01', '2022-12-31'),
  ('U2', 'D1', '2021-04-01', '2022-12-31');

Query:

SELECT DISTINCT a.UserId
FROM SampleData a
JOIN SampleData b ON a.UserId = b.UserId 
AND a.Department <> b.Department
WHERE a.ActiveFromDate <= b.ActiveToDate 
AND a.ActiveToDate >= b.ActiveFromDate

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Find the value present in more than one date ranges

1条答案

相关问题

热门标签

最新问答