Comparer les dates entre les lignes

Question

J'ai les résultats suivants où j'ai ajouté une colonne supplémentaire pour ExpectedResult.

Je ne suis pas sûr de la façon de procéder pour que cela fonctionne, où la valeur DateFrom devrait être comparée à la prochaine valeur DateFrom jusqu'à la dernière ligne de cette Catégorie où elle devrait être comparée à la valeur DateTo. DateTo pourrait aussi être NULL, dans ce cas, elle devrait être comparée à getdate().

Quelqu'un a des idées sur la façon dont cela pourrait être réalisé?

Pour la Catégorie 1, la comparaison est faite de 2019-03-01 à 2019-03-08, puis de 2019-03-08 à 2019-03-11 et enfin de 2019-03-11 à 2019-03-14 et puis se réinitialise pour la prochaine catégorie.

Données d'exemple:

Create Table #TestData
(
    ID int,
    Category int,
    DateFrom datetime,
    DateTo datetime NULL,
    ExpectedResult int
)

insert into #TestData
(
    ID,
    Category,
    DateFrom,
    DateTo,
    ExpectedResult
)
select
    1,
    1,
    '01 Mar 2019',
    '06 Mar 2019',
    7
union all
select
    2,
    1,
    '08 Mar 2019',
    '09 Mar 2019',
    3
union all
select
    3,
    1,
    '11 Mar 2019',
    '14 Mar 2019',
    3
union all
select
    1,
    2,
    '04 Mar 2019',
    '07 Mar 2019',
    4
union all
select
    2,
    2,
    '08 Mar 2019',
    '15 Mar 2019',
    10
union all
select
    3,
    2,
    '18 Mar 2019',
    '24 Mar 2019',
    6

select  * from #TestData

drop table #TestData

Answer 1

Il semble que vous recherchiez principalement la fonction de fenêtrage lead(). Et pour datediff() pour obtenir la différence en jours et coalesce() pour remplacer les NULL.

SELECT id,
       category,
       datefrom,
       dateto,
       expectedresult,
       datediff(day,
                datefrom,
                lead(datefrom,
                     1,
                     coalesce(dateto,
                              getdate())) OVER (PARTITION BY category
                                                ORDER BY datefrom)) calculatedexpectedresult
       FROM #testdata;

db<>fiddle