J'essaie de trouver un moyen d'ajouter une colonne supplémentaire à cet ensemble de résultats qui est une valeur de bit de 1/0 si le propriétaire particulier a toutes les lignes comme non nulles.
La colonne serait:
create table #temp ( Owner varchar(100), Area varchar(100), Signed date null ) insert into #temp ( Owner, Area, Signed ) select 'Owner 1', 'Area 1', NULL union all select 'Owner 1', 'Area 2', NULL union all select 'Owner 1', 'Area 3', '15 Nov 2020' union all select 'Owner 2', 'Area 1', '12 Nov 2020' union all select 'Owner 3', 'Area 10', '5 Nov 2020' union all select 'Owner 3', 'Area 5', '8 Nov 2020'
4 Réponses :
Vous pouvez utiliser les fonctions de fenêtre:
select t.* min(case when signed is null then 0 else 1 end) over(partition by owner) as status from mytable t
Autre méthode:
select f1.*, isnull((select top 1 0 from temp f2 where f1.Owner=f2.Owner and f2.Signed is null), 1) as status from temp f1
Autre méthode 2:
select f1.*, isnull(f3.result, 1) as status from temp f1 outer apply ( select top 1 0 as Result from temp f2 where f1.Owner=f2.Owner and f2.Signed is null ) f3
Autre méthode 3:
with temp2 as ( select Owner, count(*) NbRow, count(Signed) NbSignedNotNull from temp group by Owner ) select f1.*, case when f2.NbRow=f2.NbSignedNotNull then 1 else 0 end as Status from temp f1 inner join temp2 f2 on f1.Owner=f2.Owner