use ForTest
go

if OBJECT_ID('tb_v1','u')is not null
    drop table dbo.tb_v1
go

create table dbo.tb_v1(
    ID int
    constraint pk_tb_v1 primary key(ID)
    ,Name nvarchar(50)
    ,[Status] int
    constraint chk_tb_v1_Status check([Status] in (1,2))
    ,[Date] datetime
)

insert into dbo.tb_v1 (ID,Name,[Status],[Date])
    select 1,N'A',1,N'2010-1-3'
    union all select 2,N'A',2,N'2010-1-4'
    union all select 3,N'I',1,N'2010-1-3'
    union all select 4,N'I',2,N'2010-1-4'
    union all select 5,N'A',1,N'2011-1-3'
    union all select 6,N'B',1,N'2011-1-3'
    union all select 7,N'C',1,N'2011-1-3'
    union all select 8,N'D',1,N'2011-1-3'
    union all select 9,N'I',1,N'2011-1-3'
    union all select 10,N'I',2,N'2011-1-4'
    union all select 11,N'J',1,N'2011-1-4'
    union all select 12,N'A',2,N'2011-2-3'
    union all select 13,N'B',2,N'2011-2-3'
    union all select 14,N'E',1,N'2011-2-3'
    union all select 15,N'E',2,N'2011-2-3'
    union all select 16,N'F',1,N'2011-2-3'
    union all select 17,N'F',2,N'2011-2-3'
    union all select 18,N'E',1,N'2011-2-5'
    union all select 19,N'E',2,N'2011-2-6'
    union all select 20,N'G',1,N'2011-2-3'
    union all select 21,N'H',1,N'2011-2-3'
    union all select 22,N'J',2,N'2011-2-6'
    union all select 23,N'J',1,N'2011-2-7'
    union all select 24,N'G',2,N'2011-3-3'
go

--select * from tb_v1

/*
1.
查询时间设定：2011-2-1到2011-2-28之间
相对于这个时间查询新打开的灯
所有新打开的灯，结果如下：
14 E 1 2011-2-3
18 E 1 2011-2-5
16 F 1 2011-2-3
20 G 1 2011-2-3
21 H 1 2011-2-3
23 J 1 2011-2-7
*/

;with cte_v1 as(
    select * from tb_v1
    where Date between N'2011-2-1' and N'2011-2-28'
)
    select * from cte_v1 
    where Status=1
    order by Name,ID
    
/*
2.
新打开的灯中还开着的，结果如下：
20 G 1 2011-2-3
21 H 1 2011-2-3
23 J 1 2011-2-7
*/

;with cte_v2 as (
    select * from tb_v1
    where Date between N'2011-2-1' and N'2011-2-28'
)
    select a.* from cte_v2 a
    where a.status=1 
        and not exists(
            select 1 from cte_v2
            where ID>a.ID
                and Name=