select a.* from tb where date in
(
  select max(date) from tb t1,
  (select dateadd(month , -1 , date) from tb where status = 0) t2
  where convert(varchar(7),t1.date,120) = convert(varchar(7),t2.date,120)
)

------解决方案--------------------
SQL code
create table tb(ID int,jobNo int, Date datetime,[Month] datetime,status int)
insert into tb values(1,         111,           '2007-2-10',             '2007-2-1',           1 )
insert into tb values(2,         111,           '2007-2-20',             '2007-2-1',           1 )
insert into tb values(3,         111,           '2007-3-1' ,             '2007-3-1',           1 )
insert into tb values(4,         111,           '2007-3-10',             '2007-3-1',           0 )
go

select a.* from tb a where date in
(
  select max(date) from tb t1,
  (select dateadd(month , -1 , date) tdate from tb where status = 0) t2
  where convert(varchar(7),t1.date,120) = convert(varchar(7),t2.tdate,120)
)

drop table tb

/*
ID          jobNo       Date                                                   Month                                                  status      
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ ----------- 
2           111         2007-02-20 00:00:00.000                                2007-02-01 00:00:00.000                                1

（所影响的行数为 1 行）
*/

------解决方案--------------------
SQL code

create table #
(ID   int,    jobNo int,          [Date]           varchar(10),            Month varchar(10),               status int)
insert into #
select 1,         111  ,         '2007-2-10' ,            '2007-2-1',           1  union all
select 2,         111  ,         '2007-2-20' ,            '2007-2-1',           1  union all
select 3,         111  ,         '2007-3-1'  ,            '2007-3-1',           1  union all
select 4,         111  ,         '2007-3-10' ,            '2007-3-1',           0 



select * from #
where Date =
(select max(Date) from # where Month=dateadd(month,-1,(select Month from # where status =0)) 
)

/*
ID          jobNo       Date       Month      status      
----------- ----------- ---------- ---------- ----------- 
2           111         2007-2-20  2007-2-1   1

（所影响的行数为 1 行）
*/

------解决方案--------------------
SQL code
create table tb(ID int,jobNo int, Date datetime,[Month] datetime,status int)
insert into tb values(1,   111,     '2007-2-10',       '2007-2-1',     1 )
insert into tb values(2,   111,     '2007-2-20',       '2007-2-1',     1 )
insert into tb values(3,   111,     '2007-3-1' ,       '2007-3-1',     1 )
insert into tb values(4,   111,     '2007-3-10',       '2007-3-1',     0 )
go



select *   
from  tb a   
where exists(select 1 from tb where a.jobno =jobno and [date]<   a.[date]) 
and 
id not in(
       select c.id from tb b   
       left   join   tb c on   datepart(mm,b.date)   =   datepart(mm,c.date)     
       where b.status = 0 and a.jobno = b.jobno)   

drop table tb

/*

ID          jobNo       Date                                                   Month                                                  status      
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ ----------- 
2           111         2007-02-20 00:00:00.000