問題描述
我必須創建一個我不確定如何處理的查詢，因為我無法涵蓋所有??情況.
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 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！