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

根据我读到的内容,我假设您希望所有状态为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,则该公司应包括在您的结果中。我们不关心您的日期范围之后的更改,所以我将限制放在嵌套视图中。

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

一家公司的状态为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

多亏了 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