Column to see Completion

I’m trying to come up with a way to add an extra column to this result-set that is a bit value of 1/0if the particular owner has all the rows as not null.

The column would be:

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'

Answer

You can use window functions:

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

Advertisement

Answer