Блог

Я пытаюсь придумать способ добавить дополнительный столбец к этому результирующему набору, который имеет битовое значение 1/0, если у конкретного владельца все строки не равны null.

Столбец будет:

 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'
  

Вы можете использовать оконные функции:

 select t.*
    min(case when signed is null then 0 else 1 end) over(partition by owner) as status
from mytable t
  

Другой метод 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
  

Другой метод :

 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
  

Другой метод 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