开票统计涉及到连号处理

/*
    开票统计--涉及到连号处理
*/
create table #tb(th int,time datetime,userintime datetime,outtime datetime
    ,userid varchar(4),ph int)
insert into #tb
    select 2,'2002-8-24 8:15:07','8:15:07',null,'4002',Null    
    union all select 2,'2002-8-24 8:58:21',null,null,'4002',1020001
    union all select 2,'2002-8-24 9:01:53',null,null,'4002',1020002
    union all select 2,'2002-8-24 9:02:35',null,null,'4002',1020003
    union all select 2,'2002-8-24 9:03:01',null,null,'4002',1020004
    union all select 2,'2002-8-24 16:04:35',null,null,'4002',1020353
    union all select 2,'2002-8-24 16:05:26',null,'16:05:26','4002',Null
    union all select 2,'2002-8-25 8:15:07','8:15:07',null,'4002',Null    
    union all select 2,'2002-8-25 8:58:21',null,null,'4002',1020337
    union all select 2,'2002-8-25 9:01:53',null,null,'4002',1020339
    union all select 2,'2002-8-25 9:02:35',null,null,'4002',1020340
    union all select 2,'2002-8-25 9:03:01',null,'9:03:01','4002',null
    union all select 2,'2002-8-25 9:03:01','9:03:01',null,'4002',null
    union all select 3,'2002-8-25 16:04:35',null,null,'4002',1020353
    union all select 2,'2002-8-25 16:05:26',null,'16:05:26','4002',Null
    union all select 2,'2002-8-25 8:15:07','8:15:07',null,'4003',Null    
    union all select 2,'2002-8-25 8:58:21',null,null,'4003',1020337
    union all select 2,'2002-8-25 9:01:53',null,null,'4003',1020339
    union all select 2,'2002-8-25 9:02:35',null,null,'4003',1020340
    union all select 2,'2002-8-25 9:03:01',null,null,'4003',1020344
    union all select 2,'2002-8-25 16:04:35',null,null,'4003',1020353
    union all select 2,'2002-8-25 16:05:26',null,'16:05:26','4003',Null

--select * from #tb

/* 仅显示开票情况,不包括上下班记录的处理
--连号开始编号
select id=identity(int,1,1),th,time,userid,ph
    into #temp1
    from #tb a
    where ph is not null and  -- userid='4002' and --如果只需要指定用户,就加上此条件
        not exists(select 1 from #tb where th=a.th and userid=a.userid and ph=a.ph-1)
--select * from #temp1

--连号结束编号
select id=identity(int,1,1),th,time,userid,ph
    into #temp2
    from #tb a
    where ph is not null and -- userid='4002' and --如果只需要指定用户,就加上此条件
        not exists(select 1 from #tb where th=a.th and userid=a.userid and ph=a.ph+1)
--select * from #temp2
--*/


--/* 包括上下班记录的处理
--连号开始编号
select id=identity(int,1,1),th,time,userid
    ,isnull(cast(ph as varchar),case when outtime is null then '上班' else '下班' end) as ph
    into #temp1
    from #tb a
    where -- userid='4002' and --如果只需要指定用户,就加上此条件
        not exists(select 1 from #tb where th=a.th and userid=a.userid and ph=a.ph-1)
--select * from #temp1

--连号结束编号
select id=identity(int,1,1),th,time,userid
    ,isnull(cast(ph as varchar),convert(varchar,time,108)) as ph
    into #temp2
    from #tb a
    where -- userid='4002' and --如果只需要指定用户,就加上此条件
        not exists(select 1 from #tb where th=a.th and userid=a.userid and ph=a.ph+1)
--select * from #temp1
--*/

--得到结果
select a.th,a.time,a.userid
    ,cast(a.ph as varchar)+'--'+cast(b.ph as varchar) as 开票区间
    --,a.ph as 开始编号,b.ph as 结束编号
    from #temp1 a,#temp2 b
    where a.id=b.id

drop table #tb,#temp1,#temp2