代码之家 › 专栏 › 技术社区 › Anastasios Selmani

用于从历史记录更改表中选择实体的SQL查询

union sql-server-2008 sql-server sql

Anastasios Selmani · 技术社区 · 7 年前

我必须创建一个我不确定如何处理的查询,因为我不能涵盖所有的案例。

我有一个名为company的表,其中包含以下(相对)列

表公司

柱:

Id | Name | Status | Status_Effective_Date

就这么说吧 Status 可以从中获取值 1 to 12 。逻辑是公司处于状态 2 例如自 01/01/2018 (状态\生效日期)

表公司状态历史记录

柱

Id | Company_Id | Status | Status_Effective_Date

此表保存状态更改的历史记录。例如,如果我有两个公司条目 Id = 10 喜欢

Row_1 : 1 | 10 | 1 | 02/03/2011
Row_2 : 2 | 10 | 5 | 06/08/2013

然后公司 ID=10 是状态 1 从 02/03/2011 直到 06/08/2013 。之后是状态 5 .

我要做的是创建一个报告,该报告将显示选定日期范围内某个时间点处于选定状态的所有公司。

例如,我想查询 status = 1 日期范围介于 01/01/2017 - 31/12/2017

我所能理解的案例包括:(是我想要的案例,不是我不想要的案例)

一家公司始终处于状态1,且从未改变(是)

1.1公司表格条目的状态为1,生效日期早于开始日期。

1.2由于未应用状态更改,因此“公司状态”历史记录表没有任何行。
一家公司的状态为1,并在日期范围(否)之前更改为不同的状态。

2.1公司表格条目的状态为<gt;1,生效日期早于开始日期。

2.2“公司状态”历史记录表中有一项公司状态为1的条目,生效日期为初始生效日期(初始状态),一项公司状态为新状态的条目(<gt;1),生效日期为变更日期(在日期范围之前)。
一家公司的状态为1,并在日期范围内更改为不同的状态(是)

3.1公司表格条目的状态为<gt;1,生效日期在开始日期之后和结束日期之前。

3.2“公司状态”历史记录表中有一条公司状态为1的条目,生效日期为初始生效日期(初始状态),新状态为1的条目(<>1),生效日期为变更日期(在日期范围内)。
一家公司处于状态1,在日期范围后更改为不同的状态(是)

4.1公司表格条目的状态为<gt;1,生效日期在结束日期之后。

4.2“公司状态”历史记录表中有一项公司状态1的条目,生效日期为初始生效日期(初始状态),以及一项公司新状态的条目(<>1),生效日期为变更日期(日期范围之后)。
一家公司的状态为<gt;1,并在日期范围(是)之前更改为状态=1。

5.1公司表格条目的状态为1或<gt;1(因为它可能再次更改),如果它仍然处于状态=1,则生效日期可能早于日期范围;如果它再次更改,则生效日期可能早于日期范围。

5.2“公司状态”历史记录表中有一个公司的前一状态条目,生效日期为初始生效日期(初始状态),至少有一个公司的新状态条目(=1),生效日期为变更日期(在日期范围之前)。
一家公司的状态为<gt;1,在日期范围内更改为状态1(是)

6.1公司表格条目的状态为1或<gt;1(如果再次更改),生效日期为日期范围内的日期,如果再次更改,则为更晚的日期。

6.2“公司状态”历史记录表中有一项公司初始状态的条目,生效日期为初始生效日期(初始状态),至少有一项公司状态1的条目,生效日期为变更日期(在日期范围内)。
一家公司的状态为<gt;1,在日期范围(否)后更改为状态1。

7.1公司表格条目的状态为1或<gt;1(如果有其他更改),生效日期在结束日期之后。

7.2“公司状态”历史记录表中有一条公司初始状态条目<gt;1,生效日期为初始生效日期(初始状态),至少有一条公司新状态条目(1),生效日期为变更日期(日期范围之后)。

到目前为止,我尝试的是:

-- 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)

我想有一个更有效的方法来使用工会。

我遗漏的是案例3、4、5,如果变更应将公司包括在我的最终列表中,我应该从以下查询的上一个公司状态记录(生效日期)中了解这一部分。

select * from company com, company_status_history csh 
where com.company_id = csh.company_id

我们将非常感谢您的帮助。

3 回复 | 直到 7 年前

Michelos 7 年前

根据我读到的内容,我假设您希望所有状态为1的公司都在您的日期范围内。如果这是你想要的,那很容易。

SELECT C.*
  FROM COMPANY C
  LEFT JOIN ( SELECT H.STATUS, H.COMPANY_ID
                FROM COMPANYSTATUS H
               WHERE H.STATUS_EFFECTIVE_DATE = (SELECT MAX(H1.STATUS_EFFECTIVE_DATE)
                                                  FROM COMPANYSTATUS H1
                                                 WHERE H1.COMPANY_ID = H.COMPANY_ID
                                                   AND H1.STATUS_EFFECTIVE_DATE <= '20171231'
             ) CH ON CH.COMPANY_ID = C.ID   
 WHERE ( C.STATUS = 1 AND CH.COMPANY_ID IS NULL ) -- CASE #1 : STATUS = 1 AND NEVER CHANGED (NO HISTORY)
    OR H.STATUS = 1

我所做的是创建一个嵌套视图,在您的日期范围结束之前具有最后一个状态,因此,如果公司状态的最后一个更改是1,则该公司应包括在您的结果中。我们不关心您的日期范围之后的更改,所以我将限制放在嵌套视图中。

L0uis 7 年前

似乎您希望提供处于给定状态和日期范围内的任何公司的列表。当我们意识到您希望了解在提供的日期范围内具有任何状态的任何公司时,这似乎就不那么复杂了。

一家公司的状态为1,并在日期范围内更改为不同的状态(是) 一家公司处于状态1,在日期范围后更改为不同的状态(是)

   --TEST DATA
        create table #company
        (
        id int identity not null,
        name varchar(222) not null,
        status int not null,
        status_effective_date datetime not null
        )
        create table #companystatus
        (
        id int identity not null,
        company_id int not null,
        status int not null,
        status_effective_date datetime not null
        )

        insert into #company (name,status,status_effective_date) values('foo',1,'12/31/2017')
        insert into #company (name,status,status_effective_date) values('biz',2,'11/30/2017')
        insert into #company (name,status,status_effective_date) values('baz',1,'12/31/2017')
        insert into #company (name,status,status_effective_date) values('bloh',3,'11/30/2017')
        insert into #company (name,status,status_effective_date) values('blee',4,'12/31/2017')

        declare @fooid int
        set @fooid = (select id from #company where name = 'foo')
        declare @bizid int
        set @bizid =  (select id from #company where name = 'biz')
        declare @bazid int
        set @bazid = (select id from #company where name = 'baz')
        declare @blohid int
        set @blohid =   (select id from #company where name = 'bloh')

        insert into #companystatus (company_id,status,status_effective_date) values (@fooid,2,'1/1/2018')
        insert into #companystatus (company_id,status,status_effective_date) values (@bizid,5,'3/1/2018')
        insert into #companystatus (company_id,status,status_effective_date) values (@bazid,4,'2/1/2018')
        insert into #companystatus (company_id,status,status_effective_date) values (@blohid,1,'2/1/2018')
        insert into #companystatus (company_id,status,status_effective_date) values (@fooid,2,'4/1/2018')
        insert into #companystatus (company_id,status,status_effective_date) values (@fooid,4,'9/1/2018')
        insert into #companystatus (company_id,status,status_effective_date) values (@bizid,2,'5/1/2018')
        insert into #companystatus (company_id,status,status_effective_date) values (@bazid,1,'10/1/2018')
        insert into #companystatus (company_id,status,status_effective_date) values (@blohid,3,'7/1/2018')
        insert into #companystatus (company_id,status,status_effective_date) values (@fooid,1,'6/1/2018')

            --get only one distinct company id and name where the status date is between my two dates and the status is the value I have chosen (1)
        select distinct(cn.id), cn.name from #companystatus c
        join #company cn on
        cn.id = c.company_id
        where c.status_effective_date between '1/1/2017' and '5/1/2018'
        and c.status in (1) 
--replace the dates and status with what you want
        --clean up
        drop table #company
        drop table #companystatus

Anastasios Selmani 7 年前

多亏了 this answer

-- 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