83.开票统计--涉及到连号处理

拼搏现实的明天。 2024-02-19 18:03 10阅读 0赞

/*
    	开票统计--涉及到连号处理
    */
    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