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

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