CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

Get how many groups have all values of column set as null?

I have a table @EmplTable that has four rows. It's the name of four employees.

Then I have a table @MonitorTable, which monitors the work these employees do.

Assuming that nothing's havppened to the table, @MonitorTable will have 12 rows corresponding to the three weeks that these four employees will be working. Column IsProcessed is null for everything, which means nothing's been assigned.

Let's say IsProcessed is set to true for EmpId 3 of WeekNum 1, how can I determine how many groups of WeekNum have all four rows null? In this case, the result would be 2 (WeekNum 2 & 3), since WeekNum 1 has one row with IsProcessed set to true.

I'm including the script. Any help is appreciated.

Thanks.

declare @EmplTable table
(
    EmpId INT IDENTITY NOT NULL PRIMARY KEY,   
    Name varchar(20)
)

insert into @EmplTable (Name) select 'John'
insert into @EmplTable (Name) select 'Mary'
insert into @EmplTable (Name) select 'Ron'
insert into @EmplTable (Name) select 'Bob'

declare @count int
select @count = count(*) from @EmplTable

declare @MonitorTable table
(
    EmpId int,
    WeekNum int,
    IsProcessed bit
)

insert into @MonitorTable (EmpId, WeekNum) select 1, 1
insert into @MonitorTable (EmpId, WeekNum) select 2, 1
insert into @MonitorTable (EmpId, WeekNum) select 3, 1
insert into @MonitorTable (EmpId, WeekNum) select 4, 1
insert into @MonitorTable (EmpId, WeekNum) select 1, 2
insert into @MonitorTable (EmpId, WeekNum) select 2, 2
insert into @MonitorTable (EmpId, WeekNum) select 3, 2
insert into @MonitorTable (EmpId, WeekNum) select 4, 2
insert into @MonitorTable (EmpId, WeekNum) select 1, 3
insert into @MonitorTable (EmpId, WeekNum) select 2, 3
insert into @MonitorTable (EmpId, WeekNum) select 3, 3
insert into @MonitorTable (EmpId, WeekNum) select 4, 3

update @MonitorTable set IsProcessed = 1 where EmpId = 1 and WeekNum = 1

SELECT  WeekNum
FROM    @MonitorTable
GROUP BY WeekNum
HAVING  COUNT(CASE WHEN IsProcessed = 1 THEN 0
                   ELSE 1
              END) = @count

-- Should return 2, 3 since WeekNum has been updated. It returns 1, 2, 3.

Upvotes: 1

Views: 50

Answers (2)

JamieD77
JamieD77

Reputation: 13949

this should give you the 2 records you need

SELECT  WeekNum
FROM    @MonitorTable
GROUP BY WeekNum
HAVING  SUM(CASE WHEN IsProcessed = 1 THEN 0
                   ELSE 1
              END) = 4

Upvotes: 1

S3S
S3S

Reputation: 25112

declare @EmplTable table
(
    EmpId INT IDENTITY NOT NULL PRIMARY KEY,   
    Name varchar(20)
)

insert into @EmplTable (Name) select 'John'
insert into @EmplTable (Name) select 'Mary'
insert into @EmplTable (Name) select 'Ron'
insert into @EmplTable (Name) select 'Bob'

declare @MonitorTable table
(
    EmpId int,
    WeekNum int,
    IsProcessed bit
)

insert into @MonitorTable (EmpId, WeekNum) select 1, 1
insert into @MonitorTable (EmpId, WeekNum) select 2, 1
insert into @MonitorTable (EmpId, WeekNum, IsProcessed) select 3, 1, 1
insert into @MonitorTable (EmpId, WeekNum) select 4, 1
insert into @MonitorTable (EmpId, WeekNum) select 1, 2
insert into @MonitorTable (EmpId, WeekNum) select 2, 2
insert into @MonitorTable (EmpId, WeekNum) select 3, 2
insert into @MonitorTable (EmpId, WeekNum) select 4, 2
insert into @MonitorTable (EmpId, WeekNum) select 1, 3
insert into @MonitorTable (EmpId, WeekNum) select 2, 3
insert into @MonitorTable (EmpId, WeekNum) select 3, 3
insert into @MonitorTable (EmpId, WeekNum) select 4, 3

select * from @MonitorTable

if object_id('tempdb..#temp') is not null drop table #temp

select WeekNum as ct
into #temp
from  @MonitorTable
group by WeekNum
having sum(cast(isnull(IsProcessed,0) as int)) = 0


select count(ct) as ct from #temp

Upvotes: 0

Related Questions