员工请假表leave:记录每次请假的时间以及本次请假对应的分值 id badge begindate enddate value
1 001 1/1/2005 1/1/2005 1
2 001 1/3/2005 1/3/2005 0.5
3 001 1/4/2005 1/4/2005 1
4 001 1/11/2005 1/11/2005 1
5 002 1/11/2005 1/11/2005 1
6 002 1/12/2005 1/12/2005 0.5
7 002 1/14/2005 1/14/2005 1要求统计年假:假如1/5/2005(A点)---1/10/2005 (B点)(月/日/年) 放年假。对每个员工来说,要求统计出 从A开始往前连续的记录和B点往后连续的记录 也就是说假前,假后连续的请假找出来并计算value值
得到的结果应该是:
--------------------
001 2.5
002 1.5 不知道我说清楚了没有
1 001 1/1/2005 1/1/2005 1
2 001 1/3/2005 1/3/2005 0.5
3 001 1/4/2005 1/4/2005 1
4 001 1/11/2005 1/11/2005 1
5 002 1/11/2005 1/11/2005 1
6 002 1/12/2005 1/12/2005 0.5
7 002 1/14/2005 1/14/2005 1要求统计年假:假如1/5/2005(A点)---1/10/2005 (B点)(月/日/年) 放年假。对每个员工来说,要求统计出 从A开始往前连续的记录和B点往后连续的记录 也就是说假前,假后连续的请假找出来并计算value值
得到的结果应该是:
--------------------
001 2.5
002 1.5 不知道我说清楚了没有
from leave
group by badge
from leave
where begindate < '1/5/2005'
or begindate > '1/10/20055'
group by badge
if object_id('[leave]') is not null drop table [leave]
create table [leave] (id int,badge varchar(3),begindate datetime,enddate datetime,value numeric(2,1))
insert into [leave]
select 1,'001','1/1/2005','1/1/2005',1 union all
select 2,'001','1/3/2005','1/3/2005',0.5 union all
select 3,'001','1/4/2005','1/4/2005',1 union all
select 4,'001','1/11/2005','1/11/2005',1 union all
select 5,'002','1/11/2005','1/11/2005',1 union all
select 6,'002','1/12/2005','1/12/2005',0.5 union all
select 7,'002','1/14/2005','1/14/2005',1declare @t1 datetime,@t2 datetime
set @t1='2005-01-05'
set @t2='2005-01-10'
select badge,value=sum(value) from [leave] a
where exists(select 1 from leave where badge=a.badge and abs(datediff(dd,begindate,a.begindate))=1)
or abs(datediff(dd,begindate,@t1))=1
or abs(datediff(dd,begindate,@t2))=1
group by badge
--结果:
badge value
----- ---------------------------------------
001 2.5
002 1.5
or begindate > '1/10/20055'完全没有读懂我的描述
2> go
id |badge|begindate |enddate |value
-----------|-----|-----------------------|--------------------|-----
1|001 |2005-01-01 00:00:00.000| 2005-01-01 00:00:00| 1.0
2|001 |2005-01-03 00:00:00.000| 2005-01-03 00:00:00| .5
3|001 |2005-01-04 00:00:00.000| 2005-01-04 00:00:00| 1.0
4|001 |2005-01-11 00:00:00.000| 2005-01-11 00:00:00| 1.0
5|002 |2005-01-11 00:00:00.000| 2005-01-11 00:00:00| 1.0
6|002 |2005-01-12 00:00:00.000| 2005-01-12 00:00:00| .5
7|002 |2005-01-14 00:00:00.000| 2005-01-14 00:00:00| 1.0(7 rows affected)
1> select badge,sum(value)
2> from leave
3> where not begindate between '2005-01-05' and '2005-01-10'
4> group by badge
5> go
badge|
-----|----------------------------------------
001 | 3.5
002 | 2.5(2 rows affected)
1>
--> 测试数据: [leave]
if object_id('[leave]') is not null drop table [leave]
create table [leave] (id int,badge varchar(3),begindate datetime,enddate datetime,value numeric(2,1))
insert into [leave]
select 1,'001','1/1/2005','1/1/2005',1 union all
select 2,'001','1/3/2005','1/3/2005',0.5 union all
select 3,'001','1/4/2005','1/4/2005',1 union all
select 4,'001','1/11/2005','1/11/2005',1 union all
select 5,'002','1/11/2005','1/11/2005',1 union all
select 6,'002','1/12/2005','1/12/2005',0.5 union all
select 7,'002','1/14/2005','1/14/2005',1declare @begintime datetime,@endtime datetime
set @begintime='2005-01-05'
set @endtime='2005-01-10'
select
badge,[value]=sum(value) from [leave] a
where
exists(select 1 from leave where badge=a.badge and abs(datediff(dd,begindate,a.begindate))=1)
or
datediff(dd,begindate,@begintime)=1
or
datediff(dd,@endtime,begindate)=1
group by
badge
/*badge value
----- ---------------------------------------
001 2.5
002 1.5(2 行受影响)*/
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[badge] varchar(3),[begindate] datetime,[enddate] datetime,[value] numeric(2,1))
insert [tb]
select 1,'001','1/1/2005','1/1/2005',1 union all
select 2,'001','1/3/2005','1/3/2005',0.5 union all
select 3,'001','1/4/2005','1/4/2005',1 union all
select 4,'001','1/11/2005','1/11/2005',1 union all
select 5,'002','1/11/2005','1/11/2005',1 union all
select 6,'002','1/12/2005','1/12/2005',0.5 union all
select 7,'002','1/14/2005','1/14/2005',1declare @begdate datetime,@enddate datetime
select @begdate = '1/5/2005',@enddate = '1/10/2005'select [badge],sum([value]) as [value]
from(select cast(right(convert(varchar(10),[begindate],120),2) as int) - id
as group_id,* from [tb])t
where group_id in (select group_id from
(select cast(right(convert(varchar(10),[begindate],120),2) as int) - id
as group_id,* from [tb]) h where datediff(day,[begindate],@begdate) =1
or datediff(day,@enddate,[begindate]) = 1 and [badge] = t.[badge])
group by [badge]
---------------------------------
001 2.5
002 1.5