J'essaie d'identifier les plages de dates lorsqu'une organisation est sur notre liste «moniteur».

Mes données ressemblent à ceci:

OrgCode OrgName           ReviewDate    MonitorList
8000    Organization A    3/6/2014      1
8000    Organization A    6/4/2014      1
8000    Organization A    9/4/2014      1
8000    Organization A    12/4/2014     0
8000    Organization A    3/5/2015      1
8000    Organization A    6/4/2015      1
8000    Organization A    9/16/2015     1
8000    Organization A    12/16/2015    1
8000    Organization A    3/9/2016      1
8000    Organization A    6/2/2016      1
8000    Organization A    9/8/2016      1
8000    Organization A    12/8/2016     1
8000    Organization A    3/9/2017      0
8000    Organization A    6/14/2018     0

La sortie de requête que je recherche ressemble à ceci:

OrgCode OrgName           MonitorStartDate  MonitorEndDate
8000    Organization A    3/6/2014          12/4/2014
8000    Organization A    3/5/2015          3/9/2017

Cette organisation, l'Organisation A, est apparue deux fois sur notre liste de surveillance: du 06/03/2014 au 04/12/2014 et du 05/03/2015 au 09/03/2017.

J'ai essayé d'accomplir cela de plusieurs manières, y compris,

  • Variétés de LEAD() et LAG(); et,
  • GROUP BY OrgCode, OrgName, MonitorList et définissant MonitorStartDate comme MIN(ReviewDate) et MonitorEndDate comme MAX(ReviewDate).

La deuxième méthode ne tenait pas compte du fait que ces organisations peuvent être activées / désactivées plusieurs fois sur la liste de surveillance. Je pense toujours que certaines combinaisons de LEAD() ou LAG() pourraient fonctionner; mais pas par eux-mêmes.

Tout conseil que vous pourriez fournir serait formidable et merci pour votre aide!

4
Rymatt830 23 mai 2018 à 17:18

3 réponses

Meilleure réponse

Utilisez une somme en cours pour classer les lignes en groupes en redéfinissant la valeur lorsque 0 est rencontré et lead pour obtenir la date de la ligne suivante, car la date de fin doit être à partir du premier 0 rencontré. Ensuite, utilisez min et max sur les colonnes correspondantes avec les regroupements nécessaires.

select orgcode,orgname
,min(case when monitorlist=1 then reviewdate end) as monitorstartdate
,max(next_dt) as monitorenddate
from (select t.*,
      sum(case when monitorlist=0 then 1 else 0 end) over(partition by orgcode order by reviewdate) as grp,
      lead(reviewdate) over(partition by orgcode order by reviewdate) as next_dt
      from tbl t
     ) t
group by orgcode,orgname,grp
having max(cast(monitorlist as int))=1
2
Vamsi Prabhala 23 mai 2018 à 15:12

Vous pouvez identifier les groupes en comptant le nombre de 0 sur ou après chaque ligne. Le reste n'est que l'agrégation:

select orgcode, orgname, min(ReviewDate) as MonitorStartDate,
       coalesce(min(case when monitorlist = 0 then ReviewDate end),
                max(ReviewDate)
               ) as MontiroEndDate
from (select t.*,
             sum(case when monitorlist = 0 then 1 else 0 end) over (partition by orgcode order by reviewdate desc) as grp             
      from t
     ) t
group by orgcode, orgname, grp
having max(monitorlist) = 1;

La logique de la date de fin est un peu délicate:

  • C'est le ReviewDate de l'enregistrement "0".
  • S'il n'y en a pas, le dernier ReviewDate est utilisé.

Voici un SQL Fiddle le démontrant.

-1
Gordon Linoff 23 mai 2018 à 21:15

Avec cette requête

select orgcode,orgname,format(min(reviewdate),'M/d/yyyy') as monitorstartdate,format(max(next_dt),'M/d/yyyy') as monitorenddate
from (select t.*,
   sum(case when monitorlist=0 then 1 else 0 end) 
     over(partition by orgcode order by reviewdate) as grp,
     lead(reviewdate) over(partition by orgcode order by reviewdate) as next_dt
   from tbl t
   ) t
group by orgcode,orgname,grp,MonitorList
having MonitorList = 1

Le résultat est le suivant

orgcode     orgname             monitorstartdate    monitorenddate
8000        "Organization A"    3/6/2014            12/4/2014
8000        "Organization A"    3/5/2015            3/9/2017

Le lien Fiddle se trouve ici si les gens veulent vérifier.

1
webmite 23 mai 2018 à 16:17