Requête pour les enregistrements qui étaient actifs pendant une plage de dates spécifiée

Voici la solution que j'ai trouvée:

ItemID
------
A
B
C
D
E
F
G

Résultat:

-- Date range includes all of 2017
declare
    @beginSearchDate date = '2017-01-01',
    @endSearchDate date = '2017-12-31';

with
    -- CTE: Existing data combined with current value as of today
    a as (
        select ItemID, Valid, StartDate
        from #Temp
        union
        select t1.ItemID, t1.Valid, convert(date, getdate())
        from (
                    select ItemID, max(StartDate) as LatestStartDate
                    from #Temp
                    group by ItemID
                ) as t2
                inner join #Temp as t1
                        on t1.ItemID = t2.ItemID
                            and t1.StartDate = t2.LatestStartDate
    ),
    -- CTE: Current and previous values included in each record
    b as (
        select a1.*,
                lag(a1.Valid) over ( partition by a1.ItemID order by a1.StartDate )
                    as PrevValid,
                lag(a1.StartDate) over ( partition by a1.ItemID order by a1.StartDate )
                    as PrevStartDate
        from a as a1
                inner join a as a2
                        on a1.ItemID = a2.ItemID
                            and a1.StartDate = a2.StartDate
    ),
    -- CTE: Values as a series of date ranges
    c as (
        select distinct ItemID,
                        StartDate     as UntilDate,
                        PrevValid     as Valid,
                        PrevStartDate as FromDate
        from b
        where PrevValid is not null
    )

-- Find all records where date range overlaps
select distinct ItemID
from c
where Valid = 1
    and FromDate <= @endSearchDate
    and UntilDate > @beginSearchDate
order by ItemID;

Voici mon coup d'œil. Je construis la première table avec les éléments qui avaient un drapeau valide = 1 qui tombait n'importe où en dessous de la date de fin. Cela prendrait en compte l'élément A ou un élément similaire.

Je l'ai ensuite comparé jusqu'à la dernière date non valide pour chaque élément s'il en avait un, puis je l'ai filtré par date.

declare
    @beginSearchDate date = '2017-01-01',
    @endSearchDate date = '2017-12-31';


;WITH CTE as (  
 select itemid, VALID, MAX(StartDate) stDate from #temp
       where valid <> 0 and StartDate <= @endSearchDate
           group by itemID, VALID

      )
    SELECT t1.ItemID, VALID , stDate 
        from CTE t1
        outer apply (
             SELECT ItemID, MAX(StartDate) inValDate from #Temp 
               where Valid = 0 
               and StartDate <= @endSearchDate 
               and ItemID = t1.ItemID GROUP BY ItemID) t2
    WHERE t2.inValDate IS NULL 
         or (t1.stDate > t2.inValDate OR t1.stDate > @beginSearchDate OR t2.inValDate > @beginSearchDate)

Voici une solution

DECLARE @SD DATE = '2017-01-01',
        @ED DATE = '2017-12-31';

WITH BSD AS
(
SELECT *,
       LAST_VALUE(Valid) OVER(PARTITION BY ItemID ORDER BY StartDate) LV,
       COUNT(1) OVER(PARTITION BY ItemID ORDER BY StartDate DESC) CNT
FROM #Temp
WHERE StartDate <= @SD
)
SELECT ItemID
FROM BSD
WHERE LV = 1 AND CNT = 1
UNION 
SELECT ItemID
FROM #Temp
WHERE Valid = 1
      AND
      StartDate <= @ED
      AND
      StartDate >= @SD;

Démo en direct

Tout d'abord, vous pouvez transformer la liste d'origine des horodatages:

DECLARE
  @StartDate date = '2017-01-01',
  @EndDate   date = '2018-01-01',
  @ValidValue bit = 1
;

WITH
  ranges AS
  (
    SELECT
      ItemID,
      Valid,
      StartDate,
      EndDate = LEAD(StartDate, 1, CAST(CURRENT_TIMESTAMP AS date))
                OVER (PARTITION BY ItemID ORDER BY StartDate ASC)
    FROM
      #Temp
  )
SELECT DISTINCT
  ItemID
FROM
  ranges
WHERE
  Valid = @ValidValue
  AND StartDate < @EndDate
  AND EndDate > @StartDate
;

en une liste de plages, où la date de fin est soit la prochaine entrée de l'élément StartDate , soit , si la ligne actuelle est la dernière entrée, date du jour:

StartDate < @EndDate AND EndDate > @StartDate

Vous pouvez utiliser le LEAD fonction analytique pour y parvenir:

EndDate = LEAD(StartDate, 1, CAST(CURRENT_TIMESTAMP AS date))
          OVER (PARTITION BY ItemID ORDER BY StartDate ASC)