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

SQLServer2000常用函数大全(经典)

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