跨月的时段如何拆分

Question

本帖最后由 chirea 于 2011-11-09 15:49:53 编辑

Answer 1

--测试数据
create table #test (员工编号 nvarchar(50),开始时间 datetime,结束时间 datetime,请假天数 decimal(4,1))
insert into #test values('1030','2011-11-08 14:00:00.000','2011-11-11 14:00:00.000',3.0)
insert into #test values('1030','2011-11-12 08:43:00.000','2011-11-12 12:43:00.000',0.5)
insert into #test values('1030','2011-11-29 15:27:00.000','2011-12-09 15:27:00.000',10.0)GO
SELECT CONVERT(VARCHAR(7),开始时间+a.number ,120),COUNT(a.number)
FROM master.dbo.spt_values AS a,#test AS b  
WHERE a.type='P' AND 开始时间+a.number<=结束时间 AND a.number>0
GROUP BY  CONVERT(VARCHAR(7),开始时间+a.number ,120)

Answer 2

create table #test (员工编号 nvarchar(50),开始时间 datetime,结束时间 datetime,请假天数 decimal(4,1))insert into #test values('1030','2011-11-08 14:00:00.000','2011-11-11 14:00:00.000',3.0)
insert into #test values('1030','2011-11-12 08:43:00.000','2011-11-12 12:43:00.000',0.5)
insert into #test values('1030','2011-11-29 15:27:00.000','2011-12-09 15:27:00.000',10.0)
goselect 员工编号,sum(case when convert(varchar(6),开始时间,112) = convert(varchar(6),结束时间,112) then 请假天数
 else datediff(dd,开始时间,convert(varchar(6),dateadd(mm,1,开始时间),112)+'01') end) cnt
from #test
group by 员工编号drop table #test/**************员工编号                                               cnt
-------------------------------------------------- ---------------------------------------
1030                                               5.5(1 行受影响)

Answer 3

第一条测试写错了
insert into #test values('1030','2011-10-28 14:00:00.000','2011-11-02 14:00:00.000',5.0)

Answer 4

select sum(请假天数)
from(
select 请假天数 from #test where 开始时间>'2011-11-01' and 结束时间<'2011-12-01'
union all
select datediff(day,'2011-11-01',结束时间) from #test where 开始时间<'2011-11-01' and 结束时间>'2011-11-01'
union all
select datediff(day,开始时间,'2011-12-01') from #test where 开始时间>'2011-11-01' and 结束时间>'2011-12-01'
) t
--貌似很不帅

Answer 5

----测试数据
--create table #test (员工编号 nvarchar(50),开始时间 datetime,结束时间 datetime,请假天数 decimal(4,1))
--insert into #test values('1030','2011-10-28 14:00:00.000','2011-11-02 14:00:00.000',5.0)
--insert into #test values('1030','2011-11-08 14:00:00.000','2011-11-11 14:00:00.000',3.0)
--insert into #test values('1030','2011-11-12 08:43:00.000','2011-11-12 12:43:00.000',0.5)
--insert into #test values('1030','2011-11-29 15:27:00.000','2011-12-09 15:27:00.000',10.0)GO
DECLARE @dt DATETIME
SET @dt='20111101'SELECT 
员工编号,SUM(diffday+CASE WHEN 开始时间<@dt THEN DATEDIFF(d,@dt,开始时间) ELSE 0 END+CASE WHEN 结束时间>DATEADD(m,1,@dt) THEN DATEDIFF(d,结束时间,DATEADD(m,1,@dt))+1 ELSE 0 end
+DATEDIFF(hh,DATEADD(d,diffday,开始时间),结束时间)/4*0.5) AS 天數
FROM 
(
SELECT 
员工编号,DATEDIFF(d,开始时间,结束时间) AS diffday,开始时间,结束时间
FROM #test
WHERE 开始时间<DATEADD(m,1,@dt) AND 结束时间>@dt
)t
GROUP BY 员工编号/*
员工编号 天數
1030 7.5
*/

Answer 6

--大版,我把表改一下,好像值还是不对,查员工编号相应的请假类型一个月的请假天数create table #test (员工编号 nvarchar(50),开始时间 datetime,结束时间 datetime,请假天数 decimal(4,1),请假类型 int)
insert into #test values('1030','2011-10-28 14:00:00.000','2011-11-02 14:00:00.000',5.0,1)
insert into #test values('1030','2011-11-08 14:00:00.000','2011-11-11 14:00:00.000',3.0,1)
insert into #test values('1030','2011-11-12 08:43:00.000','2011-11-12 12:43:00.000',0.5,2)
insert into #test values('1030','2011-11-29 15:27:00.000','2011-12-09 15:27:00.000',10.0,2)

Answer 7

insert into #test values('1030','2011-10-28 14:00:00.000','2011-11-02 14:00:00.000',5.0,1)第1條記錄為1.5天還是1天?

Answer 8

這要與班次比較，班次固定還要定義一個時間為半天/1天的判斷，這里下15點為例，>15點算1天DECLARE @dt DATETIME
SET @dt='20111101'SELECT 
    员工编号,
   SUM(   请假天数+    CASE WHEN 开始时间<@dt THEN CASE WHEN DATEPART(hh,开始时间)<=15 THEN DATEDIFF(d,@dt,开始时间)+0.5 ELSE DATEDIFF(d,@dt,开始时间) end  ELSE 0 END
    +CASE WHEN 结束时间>DATEADD(m,1,@dt) THEN CASE WHEN DATEPART(hh,结束时间)<=15 THEN DATEDIFF(d,结束时间,DATEADD(m,1,@dt))-0.5 ELSE 
    DATEDIFF(d,结束时间,DATEADD(m,1,@dt))-1  END ELSE 0 END
    ) AS 天數FROM #test
WHERE 开始时间<DATEADD(m,1,@dt) AND 结束时间>@dt
GROUP BY 员工编号/*
员工编号 天數
1030 6.5
*/

Answer 9

要按排班时间来算create table #test (员工编号 nvarchar(50),开始时间 datetime,结束时间 datetime,请假天数 decimal(4,1),请假类型 int,班次时间 decimal(4,1))
insert into #test values('1030','2011-10-28 14:00:00.000','2011-11-02 14:00:00.000',5.0,1,8.0)
insert into #test values('1030','2011-11-08 14:00:00.000','2011-11-11 14:00:00.000',3.0,1,12.30)
insert into #test values('1030','2011-11-12 08:43:00.000','2011-11-12 12:43:00.000',0.5,2,6.0)
insert into #test values('1030','2011-11-29 15:27:00.000','2011-12-09 15:27:00.000',10.0,2,8.0)
大版,按你17楼说的,同时我把表改一下,查员工编号相应的请假类型一个月的请假天数,也加了班次时间,我有一个班次表,这样做是不是要好一些呢,还是怎么方便一些呢?

Answer 10

create table #test (员工编号 nvarchar(50),开始时间 datetime,结束时间 datetime,请假天数 decimal(4,1))insert into #test values('1030','2011-11-08 14:00:00.000','2011-11-11 14:00:00.000',3.0)
insert into #test values('1030','2011-11-12 08:43:00.000','2011-11-12 12:43:00.000',0.5)
insert into #test values('1030','2011-11-29 15:27:00.000','2011-12-09 15:27:00.000',10.0)
insert into #test values('1030','2011-10-28 14:00:00.000','2011-11-02 14:00:00.000',5.0)WITH a AS
(
SELECT *,
(CASE WHEN DATEDIFF(m,开始时间,'2011-11-01')<>0 then '2011-10-31 00:00:00.000' else 开始时间 end) as start,
(CASE WHEN DATEDIFF(m,结束时间,'2011-11-01')<>0 then '2011-12-01 00:00:00.000' else 结束时间 end) as ends
FROM #test
)SELECT a.*,
(CASE WHEN DATEDIFF(day,start,ends)=0 THEN 1 ELSE DATEDIFF(day,start,ends) END) AS days
FROM a结果：
1030 2011-11-08 14:00:00.000 2011-11-11 14:00:00.000 3.0 2011-11-08 14:00:00.000 2011-11-11 14:00:00.000 3
1030 2011-11-12 08:43:00.000 2011-11-12 12:43:00.000 0.5 2011-11-12 08:43:00.000 2011-11-12 12:43:00.000 1
1030 2011-11-29 15:27:00.000 2011-12-09 15:27:00.000 10.0 2011-11-29 15:27:00.000 2011-12-01 00:00:00.000 2
1030 2011-10-28 14:00:00.000 2011-11-02 14:00:00.000 5.0 2011-10-31 00:00:00.000 2011-11-02 14:00:00.000 2

Answer 11

如果要设计应该是班次表，然后是员工排班表，指定员工在每天的上班班次。在请假表只需要记录，记录日期和每日请假的时间段insert into #test values('1030','2011-11-12 08:43:00.000','2011-11-12 12:43:00.000',0.5,2,6.0)6:00开始上班，计算上班分钟再换算为小时再换算为天数?
请假最小单位是？最1次列清楚实际情况
如果是厂通常都是半天为单位，公司通常以小时为单位
你给的数据结果是?

Answer 12

谢谢大版,我按你说的,把我要的结果贴出来.重新做了几个表--新的测试数据
create table #ClassManage(ClassNumber int ,OnDuty1 datetime,OffDuty1,OnDuty2 datetime,OffDuty2) --班次表
insert into #ClassManage values(1,'1900-01-01 08:00:00','1900-01-01 12:00:00','1900-01-01 13:30:00','1900-01-01 15:30:00')
insert into #ClassManage values(2,'1900-01-01 20:00:00.000','1900-01-01 08:00:00.000',null,null)
create table #Scheduling(Numbers varchar(30),DateMonths datetime , day1 int,day2 int,day3 int,day4 int,day29 int,day30 int ,day31 int)--排班表,day是日期天数字段
insert into #Scheduling values('001','2011-10-01 00:00:00.00',2,2,2,2,1,1,1)
insert into #Scheduling values('001','2011-11-01 00:00:00.00',2,2,2,1,1,1,1)
insert into #Scheduling values('001','2011-12-01 00:00:00.00',1,1,1,1,2,2,2)
create table #Leave(Numbers varchar(30),StartTime datetime, EndTime datetime,LaveType int,请假小时 decimal(4,2))
insert into #Leave values('001','2011-10-29 08:00:00.000','2011-11-02 14:00:00.000',1,null)
insert into #Leave values('001','2011-11-03 13:30:00.000','2011-11-04 14:00:00.000',1,null)
insert into #Leave values('001','2011-11-29 08:43:00.000','2011-12-03 08:00:00.000',2,null)
--想要的结果
Numbers 1 2
------- ------- ------
001 28.5 15.5

Answer 13

--刚才的有问题,数据类型错误有的没加
create table #ClassManage(ClassNumber int ,OnDuty1 datetime,OffDuty1 datetime,OnDuty2 datetime,OffDuty2 datetime) --班次表
insert into #ClassManage values(1,'1900-01-01 08:00:00','1900-01-01 12:00:00','1900-01-01 13:30:00','1900-01-01 15:30:00')
insert into #ClassManage values(2,'1900-01-01 20:00:00.000','1900-01-01 08:00:00.000',null,null)
create table #Scheduling(Numbers varchar(30),DateMonths datetime , day1 int,day2 int,day3 int,day4 int,day29 int,day30 int ,day31 int)--排班表,day是日期天数字段
insert into #Scheduling values('001','2011-10-01 00:00:00.00',2,2,2,2,1,1,1)
insert into #Scheduling values('001','2011-11-01 00:00:00.00',2,2,2,1,1,1,1)
insert into #Scheduling values('001','2011-12-01 00:00:00.00',1,1,1,1,2,2,2)
create table #Leave(Numbers varchar(30),StartTime datetime, EndTime datetime,LaveType int,请假小时 decimal(4,2))
insert into #Leave values('001','2011-10-29 08:00:00.000','2011-11-02 14:00:00.000',1,null)
insert into #Leave values('001','2011-11-03 13:30:00.000','2011-11-04 14:00:00.000',1,null)
insert into #Leave values('001','2011-11-29 08:43:00.000','2011-12-03 08:00:00.000',2,null)
能过上面的表我用这个写法可以得到每天的上班开始时间,与下班的结束时间.
SELECT BPT.*,CM.* FROM #ClassManage CM
right join
(
select  
Numbers AS 员工编号,
日期=DATEADD(dd,cast(stuff(Cols,1,3,'')as int)-1,DateMonths),
ClassNumbers
from 
(select * 
from #Scheduling  where convert(varchar(7),DateMonths,120) between convert(varchar(7),'2011-11-01',120) and convert(varchar(7),'2011-11-01',120)
)
#Scheduling  
unpivot (ClassNumbers for Cols in(
[day1],[day2],[day3],[day4],[day29],[day30],[day31]
)) as b) BPT
on BPT.ClassNumbers=CM.ClassNumber
---------------------------
员工编号                           日期                      ClassNumbers ClassNumber OnDuty1                 OffDuty1                OnDuty2                 OffDuty2
------------------------------ ----------------------- ------------ ----------- ----------------------- ----------------------- ----------------------- -----------------------
001                            2011-11-01 00:00:00.000 2            2           1900-01-01 20:00:00.000 1900-01-01 08:00:00.000 NULL                    NULL
001                            2011-11-02 00:00:00.000 2            2           1900-01-01 20:00:00.000 1900-01-01 08:00:00.000 NULL                    NULL
001                            2011-11-03 00:00:00.000 2            2           1900-01-01 20:00:00.000 1900-01-01 08:00:00.000 NULL                    NULL
001                            2011-11-04 00:00:00.000 1            1           1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 1900-01-01 13:30:00.000 1900-01-01 15:30:00.000
001                            2011-11-29 00:00:00.000 1            1           1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 1900-01-01 13:30:00.000 1900-01-01 15:30:00.000
001                            2011-11-30 00:00:00.000 1            1           1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 1900-01-01 13:30:00.000 1900-01-01 15:30:00.000
001                            2011-12-01 00:00:00.000 1            1           1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 1900-01-01 13:30:00.000 1900-01-01 15:30:00.000(7 row(s) affected)
--这样查询出来的结果,可能会方便计算一些吧.把这个做一个with表达式再计算.如何?