用于从历史更改表中选择实体的 SQL 查询 [英] SQL Query for selecting entities from a history change table
问题描述
我必须创建一个我不确定如何处理的查询，因为我无法涵盖所有情况.
I have to create a query that I am not really sure how to handle since I can't get all the cases covered.
我有一张名为 company 的表，其中包含以下(相对)列
I have one table called company with the following (relative) columns
餐桌公司
列:
Id | Name | Status | Status_Effective_Date
假设 Status 可以从 1 到 12 取值.逻辑是公司处于状态 2 例如自 01/01/2018 (status_effective_date)
Let's say that Status can take values from 1 to 12. The logic is that the company is in status 2 for example since 01/01/2018 (status_effective_date)
表公司状态历史
列
Id | Company_Id | Status | Status_Effective_Date
此表保存状态发生的更改的历史记录.例如，如果我有两个 Id = 10 的公司条目，例如
This table holds the history of the changes that have happened to the status. If for example I have two entries for the company with Id = 10 like
Row_1 : 1 | 10 | 1 | 02/03/2011
Row_2 : 2 | 10 | 5 | 06/08/2013
那么 Id = 10 的公司从 02/03/2011 到 06/08/2013 的状态是 1.之后就是状态5.
Then the company with Id = 10 was is status 1 from 02/03/2011 until 06/08/2013. After that it was is status 5.
我想要做的是创建一个报告，向我显示在选定日期范围内处于选定状态的所有公司.
现在，假设我想使用 status = 1 和 01/01/2017 - 31/12/2017 之间的日期范围进行查询
Now for example let's say I want to query with status = 1 and date range between 01/01/2017 - 31/12/2017
我必须涵盖的我能理解的案例是:(是我想要的案例，没有我不想要的案例)
The cases that I have to cover that I can understand are: (YES the cases that I want and NO the cases that I don't want)
一家公司始终处于状态 1 且从未改变(是)
One company was always in status 1 and never changed (YES)
1.1 company_table 条目的状态 = 1 且生效日期早于开始日期
1.1 The company_table entry has status = 1 and effective date before the start date
1.2 company_status_history 表没有任何行，因为没有应用状态更改
1.2 The company_status_history table doesn't have any rows since no change in the status has been applied
一家公司处于状态 1 并在日期范围 (NO) 之前更改为不同状态
One company was in status 1 and changed to different status before the date range (NO)
2.1 company_table 条目的状态 <> 1 和生效日期早于开始日期
2.1 The company_table entry has status <> 1 and effective date before the start date
2.2 company_status_history_table 有一个状态为 1 的公司条目，生效日期为初始生效日期(初始状态)，以及一个新状态 (<>1) 的公司条目，生效日期为初始生效日期更改(在日期范围之前)
2.2 The company_status_history_table has one entry of the company in the status 1 with effective date the initial effective date (initial state) and one entry of the company in the new status (<>1) with effective date the date of the change (before the date range)
一家公司处于状态 1 并在日期范围内更改为不同的状态 (YES)
One company was in status 1 and changed to different status inside the date range (YES)
3.1 company_table 条目的状态为 <> 1 并且生效日期在开始日期之后和结束日期之前
3.1 The company_table entry has status <> 1 and effective date after the start date and before the end date
3.2 company_status_history_table 有一个状态为 1 的公司条目，其生效日期为初始生效日期(初始状态)，以及一个处于新状态 (<>1) 的公司条目，生效日期为初始生效日期更改(在日期范围内)
3.2 The company_status_history_table has one entry of the company in the status 1 with effective date the initial effective date (initial state) and one entry of the company in the new status (<>1) with effective date the date of the change (inside the date range)
一家公司处于状态 1 并在日期范围之后更改为不同的状态 (YES)
One company was in status 1 and changed to different status after the date range (YES)
4.1 company_table 条目的状态为 <> 1 并且生效日期在结束日期之后
4.1 The company_table entry has status <> 1 and effective date after the after the end date
4.2 company_status_history_table 有一个状态为 1 的公司条目，生效日期为初始生效日期(初始状态)，以及一个处于新状态 (<>1) 的公司条目，生效日期为初始生效日期更改(在日期范围之后)
4.2 The company_status_history_table has one entry of the company in the status 1 with effective date the initial effective date (initial state) and one entry of the company in the new status (<>1) with effective date the date of the change (after the date range)
一家公司在日期范围之前处于状态 <>1 并更改为状态 = 1 (是)
One company was in status <>1 and changed to status = 1 before the date range (YES)
5.1 company_table 条目的状态为 1 或 <> 1(因为它可能再次更改)，如果它仍处于 status = 1，则生效日期可能早于日期范围；如果再次更改，则生效日期可能在日期范围之前.
5.1 The company_table entry has status 1 or <> 1 (since it might have changed again) and the effective date could be before the date range if it is still in status = 1 or some later date if it has changed again.
5.2 company_status_history_table 有一项前状态公司的条目，其生效日期为初始生效日期(初始状态)，以及至少一项新状态(=1)的公司条目，生效日期为生效日期更改(在日期范围之前)
5.2 The company_status_history_table has one entry of the company in the previous status with effective date the initial effective date (initial state) and at least one entry of the company in the new status (=1) with effective date the date of the change (before the date range)
一家公司处于状态 <>1 并在日期范围内更改为状态 1 (YES)
One company was in status <>1 and changed to status 1 inside the date range (YES)
6.1 company_table 条目的状态为 1 或 <>1(如果再次更改)，并且生效日期为 date_range 内的日期，如果再次更改，则为某个较晚的日期
6.1 The company_table entry has status 1 or <>1 (if it has changed again) and effective date the date inside the date_range or some later one if it has changed again
6.2 company_status_history_table 有一项处于初始状态的公司条目，生效日期为初始生效日期(初始状态)，并且至少有一项处于状态 1 的公司条目，生效日期为变更日期(内部日期范围)
6.2 The company_status_history_table has one entry of the company in the initial status with effective date the initial effective date (initial state) and at least one entry of the company in the status 1 with effective date the date of the change (inside the date range)
一家公司的状态为 <>1，但在日期范围 (NO) 之后更改为状态 1
One company was in status <>1 and changed to status 1 after the date range (NO)
7.1 company_table 条目的状态为 1 或 <>1(如果有其他更改)和结束日期之后的生效日期
7.1 The company_table entry has status 1 or <>1 (if there has been another change) and effective date after the after the end date
7.2 company_status_history_table 有一项初始状态的公司 <>1 的生效日期为初始生效日期(initial state)和至少一项新状态的公司(1)的生效日期为更改日期(在日期范围之后)
7.2 The company_status_history_table has one entry of the company in the initial status <>1 with effective date the initial effective date (initial state) and at least one entry of the company in the new status (1) with effective date the date of the change (after the date range)
到目前为止我所尝试的是以下内容:
What I have tried so far is the below:
-- Case 6
select *
from company com, company_status_history csh
where csh.company_status_id = 1
and com.company_id = csh.company_id
and csh.company_status_eff_date > '20170101'
and csh.company_status_eff_date < '20171231'
union
-- Case 1
select *
from company com
where com.company_status_id = 1
and com.company_status_eff_date < '20181231'
and com.company_id NOT IN (select company_id
from company_status_history csh)
我猜使用联合可能有更有效的方法.
I am guessing there might be a more effective way from using the union.
我缺少的是案例 3、4、5，这是我应该从以下查询的前一个 company_status_history 条目(生效日期)中了解的部分，如果更改应将公司包括在我的最终列表中.
What I am missing is cases 3,4,5 and it is the part that I should understand from the previous company_status_history entry (effective date) of the following query if the change should include the company into my final list.
select * from company com, company_status_history csh
where com.company_id = csh.company_id
我们将不胜感激.
推荐答案
感谢 这个答案.我发布它以防其他人需要它.
I finally got the working query thanks to this answer to my simplified problem. I am posting it in case someone else needs it.
-- View that gets the entity_history_status with start date and end date
-- At the moment if it is only one entry in the table (it went to current) the end_date is the same as the start date
;WITH changed_companies_with_ranges AS
(
-- Get the final table for the selected status
SELECT company_id,
MIN(company_status_eff_date) AS start_date,
CASE
WHEN COUNT(*) > 1 THEN DATEADD(DAY, -1, MAX(company_status_eff_date))
ELSE min(company_status_eff_date) -- Check why it changes the result when I set the today as end_date
END AS end_date,
grp
FROM (
-- Something with counting again in order to get the start and end date according to this grp
SELECT company_status_hist_id, company_id, company_status_id, company_status_eff_date,
ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY company_status_eff_date) -
cnt AS grp
FROM (
-- Get the entries from the history table that had at any point the selected status and add a new column saying how many entries do they have
SELECT esh2.company_status_hist_id, esh2.company_id, esh2.ecompany_status_id, esh2.company_status_eff_date, x.cnt
FROM company_status_history AS esh2
OUTER APPLY
(
SELECT COUNT(*) AS cnt
FROM ecompany_status_history AS c
WHERE c.company_status_id = 1 -- The selected status
AND c.company_id = esh2.company_id
AND c.company_status_eff_date < esh2.company_status_eff_date
) AS x
) as CTE
) as CTE2
GROUP BY company_id, grp
HAVING COUNT(CASE WHEN company_status_id = 1 THEN 1 END) > 0 -- The selected status
)
SELECT * FROM (
SELECT
en.company_id
,en.company_name
,en.company_reg_num
FROM company en
where en.company_id in(
select company_id
from changed_entitities_with_ranges
where start_date = end_date
)
and en.company_status_eff_date > '2017-01-01 00:00:00.000' -- Start Date
union
select
en.company_id
,en.company_name
,en.company_reg_num
FROM company en
where en.company_id in(
select company_id
from changed_entitities_with_ranges
where (start_date between '2017-01-01 00:00:00.000' and '2017-12-31 00:00:00.000') -- Range
or (start_date < '2017-01-01 00:00:00.000' and end_date > '2017-01-01 00:00:00.000') -- Start date -- End date
)
union
-- 1. Without any history changes + 3. changed to 1 before start date + 5 Changes to the normal status from other statuses before the end date + 7 Changes to the normal status from other statuses in between the period
-- Gets the entities that haven't changed at all and have been in status 1 before the end date
SELECT
en.company_id
,en.company_name
,en.company_reg_num
FROM company en
WHERE en.company_status_id = 1
AND en.company_status_eff_date < '2017-12-31 00:00:00.000'
UNION
-- 2. Changes to the other statuses from the status of normal after the start date + 4. Changes to the other statuses from the status of normal before the end date + 6. Changes to the other statuses from the status of normal in between the period
-- Gets the entities that have been changed to any status but were created or altered ato some point inside the range
SELECT
en.company_id
,en.company_name
,en.company_reg_num
from company en
where en.company_id IN (select company_id from company_status_history es
where es.company_status_eff_date BETWEEN '2017-01-01 00:00:00.000' AND '2017-12-31 00:00:00.000' AND es.company_status_id = 1)
) as result ORDER BY company_id
这篇关于用于从历史更改表中选择实体的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
