/*
开票统计--涉及到连号处理
*/
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
83.开票统计--涉及到连号处理
最新推荐文章于 2023-11-29 16:42:35 发布