Number of last connected records where the MachineStatus bit is 0

Transact-SQL
1

create table dbo.tblLog
(LogTime datetime
,MachineStatus bit
)

insert into dbo.tblLog values (getdate(),0)
insert into dbo.tblLog values (getdate(),0)
insert into dbo.tblLog values (getdate(),0)
insert into dbo.tblLog values (getdate(),0)
insert into dbo.tblLog values (getdate(),1)
insert into dbo.tblLog values (getdate(),1)
insert into dbo.tblLog values (getdate(),1)
insert into dbo.tblLog values (getdate(),1)
insert into dbo.tblLog values (getdate(),0)
insert into dbo.tblLog values (getdate(),0)
insert into dbo.tblLog values (getdate(),0)

select * from dbo.tblLog

Need a query to find out "Number of last connected records where the MachineStatus bit is 0) i.e result should be 3 in my insert values

Thanks

2

Maybe this:

select count(*)
  from (select max(logtime) as logtime
          from dbo.tbllog
         where machinestatus<>0
       ) as a
       inner join dbo.tbllog as b
               on b.logtime>a.logtime
              and b.machinestatus=0
;
1 Like