这个表的查询结果如下
schedule_id job_id start_time end_time total_count
1428 1064 2009-09-08 00:01:00.000 2009-09-13 08:59:00.000 4000
1432 1068 2009-09-08 10:31:00.000 2009-09-12 08:59:00.000 3000
1472 1092 2009-09-11 12:20:00.000 2009-09-14 08:59:00.000 3400
1483 1103 2009-09-11 22:10:00.000 2009-09-13 08:59:00.000 2500
1485 1104 2009-09-11 22:25:00.000 2009-09-14 08:59:00.000 2000
1518 1125 2009-09-17 14:51:00.000 2009-09-20 08:59:00.000 3000
1534 1136 2009-09-18 14:05:00.000 2009-09-20 08:59:00.000 1800每条记录的start_time和end_time的小时差是超过24小时的
我现在想得到start_time和end_time小时差不能超过24小时的多条记录,并且total_count也要根据分割的天数均分
比如
1428 1064 2009-09-08 00:01:00.000 2009-09-13 08:59:00.000 4000
我想要的结果是
1428 1064 2009-09-08 00:01:00.000 2009-09-09 00:01:00.000 800
1428 1064 2009-09-09 00:01:00.000 2009-09-10 00:01:00.000 800
1428 1064 2009-09-11 00:01:00.000 2009-09-12 00:01:00.000 800
1428 1064 2009-09-12 00:01:00.000 2009-09-13 00:01:00.000 800
1428 1064 2009-09-13 00:01:00.000 2009-09-13 08:59:00.000 800
有办法一句话实现吗 因为要放在view里面的,谢谢了
schedule_id job_id start_time end_time total_count
1428 1064 2009-09-08 00:01:00.000 2009-09-13 08:59:00.000 4000
1432 1068 2009-09-08 10:31:00.000 2009-09-12 08:59:00.000 3000
1472 1092 2009-09-11 12:20:00.000 2009-09-14 08:59:00.000 3400
1483 1103 2009-09-11 22:10:00.000 2009-09-13 08:59:00.000 2500
1485 1104 2009-09-11 22:25:00.000 2009-09-14 08:59:00.000 2000
1518 1125 2009-09-17 14:51:00.000 2009-09-20 08:59:00.000 3000
1534 1136 2009-09-18 14:05:00.000 2009-09-20 08:59:00.000 1800每条记录的start_time和end_time的小时差是超过24小时的
我现在想得到start_time和end_time小时差不能超过24小时的多条记录,并且total_count也要根据分割的天数均分
比如
1428 1064 2009-09-08 00:01:00.000 2009-09-13 08:59:00.000 4000
我想要的结果是
1428 1064 2009-09-08 00:01:00.000 2009-09-09 00:01:00.000 800
1428 1064 2009-09-09 00:01:00.000 2009-09-10 00:01:00.000 800
1428 1064 2009-09-11 00:01:00.000 2009-09-12 00:01:00.000 800
1428 1064 2009-09-12 00:01:00.000 2009-09-13 00:01:00.000 800
1428 1064 2009-09-13 00:01:00.000 2009-09-13 08:59:00.000 800
有办法一句话实现吗 因为要放在view里面的,谢谢了
declare @tb table([schedule_id] int,[job_id] int,[start_time] datetime,[end_time] datetime,[total_count] int)
insert @tb
select 1428,1064,'2009-09-08 00:01:00.000','2009-09-13 08:59:00.000',4000
select [schedule_id],[job_id],dif,[end_time]=dateadd(day,1,dif),
[total_count]=[total_count]/(select datediff(day,[start_time],[end_time]) from @tb) from
(
select *, dif=dateadd(day,number,[start_time]) from master..spt_values,@tb where type='p'
and dateadd(day,number,[start_time])<[end_time]-1
) t/*
schedule_id job_id dif end_time total_count
----------- ----------- ----------------------- ----------------------- -----------
1428 1064 2009-09-08 00:01:00.000 2009-09-09 00:01:00.000 800
1428 1064 2009-09-09 00:01:00.000 2009-09-10 00:01:00.000 800
1428 1064 2009-09-10 00:01:00.000 2009-09-11 00:01:00.000 800
1428 1064 2009-09-11 00:01:00.000 2009-09-12 00:01:00.000 800
1428 1064 2009-09-12 00:01:00.000 2009-09-13 00:01:00.000 800*/
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([schedule_id] int,[job_id] int,[start_time] datetime,[end_time] datetime,[total_count] int)
insert [TB]
select 1428,1064,'2009-09-08 00:01:00.000','2009-09-13 08:59:00.000',4000 union all
select 1432,1068,'2009-09-08 10:31:00.000','2009-09-12 08:59:00.000',3000 union all
select 1472,1092,'2009-09-11 12:20:00.000','2009-09-14 08:59:00.000',3400 union all
select 1483,1103,'2009-09-11 22:10:00.000','2009-09-13 08:59:00.000',2500 union all
select 1485,1104,'2009-09-11 22:25:00.000','2009-09-14 08:59:00.000',2000 union all
select 1518,1125,'2009-09-17 14:51:00.000','2009-09-20 08:59:00.000',3000 union all
select 1534,1136,'2009-09-18 14:05:00.000','2009-09-20 08:59:00.000',1800select * from [TB]select schedule_id,
job_id,
start_time=dateadd(dd,number,start_time),
end_time=case when datediff(hh,dateadd(dd,number,start_time),end_time)<24 then end_time else dateadd(dd,number+1,start_time) end,
total_count
from spt_values A,TB B
where type='p' and dateadd(dd,number,start_time)<=end_time/*
schedule_id job_id start_time end_time total_count
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ -----------
1428 1064 2009-09-08 00:01:00.000 2009-09-09 00:01:00.000 4000
1428 1064 2009-09-09 00:01:00.000 2009-09-10 00:01:00.000 4000
1428 1064 2009-09-10 00:01:00.000 2009-09-11 00:01:00.000 4000
1428 1064 2009-09-11 00:01:00.000 2009-09-12 00:01:00.000 4000
1428 1064 2009-09-12 00:01:00.000 2009-09-13 00:01:00.000 4000
1428 1064 2009-09-13 00:01:00.000 2009-09-13 08:59:00.000 4000
1432 1068 2009-09-08 10:31:00.000 2009-09-09 10:31:00.000 3000
1432 1068 2009-09-09 10:31:00.000 2009-09-10 10:31:00.000 3000
1432 1068 2009-09-10 10:31:00.000 2009-09-11 10:31:00.000 3000
1432 1068 2009-09-11 10:31:00.000 2009-09-12 08:59:00.000 3000
1472 1092 2009-09-11 12:20:00.000 2009-09-12 12:20:00.000 3400
1472 1092 2009-09-12 12:20:00.000 2009-09-13 12:20:00.000 3400
1472 1092 2009-09-13 12:20:00.000 2009-09-14 08:59:00.000 3400
1483 1103 2009-09-11 22:10:00.000 2009-09-12 22:10:00.000 2500
1483 1103 2009-09-12 22:10:00.000 2009-09-13 08:59:00.000 2500
1485 1104 2009-09-11 22:25:00.000 2009-09-12 22:25:00.000 2000
1485 1104 2009-09-12 22:25:00.000 2009-09-13 22:25:00.000 2000
1485 1104 2009-09-13 22:25:00.000 2009-09-14 08:59:00.000 2000
1518 1125 2009-09-17 14:51:00.000 2009-09-18 14:51:00.000 3000
1518 1125 2009-09-18 14:51:00.000 2009-09-19 14:51:00.000 3000
1518 1125 2009-09-19 14:51:00.000 2009-09-20 08:59:00.000 3000
1534 1136 2009-09-18 14:05:00.000 2009-09-19 14:05:00.000 1800
1534 1136 2009-09-19 14:05:00.000 2009-09-20 08:59:00.000 1800(所影响的行数为 23 行)*/drop table TB
declare @tb table([schedule_id] int,[job_id] int,[start_time] datetime,[end_time] datetime,[total_count] int)
insert @tb
select 1428,1064,'2009-09-08 00:01:00.000','2009-09-13 08:59:00.000',4000
;
with cte1 as
(
select *, dif=dateadd(day,number,[start_time]) from master..spt_values,@tb where type='p'
and dateadd(day,number,[start_time])<[end_time]-1
)
select [schedule_id],[job_id],dif ,
[end_time]= case when not exists(select 1 from cte1 where dif>t.dif) then [end_time] else dateadd(day,1,dif) end,
[total_count]=[total_count]/(select datediff(day,[start_time],[end_time]) from @tb)
from cte1 t /*
schedule_id job_id dif end_time total_count
----------- ----------- ----------------------- ----------------------- -----------
1428 1064 2009-09-08 00:01:00.000 2009-09-09 00:01:00.000 800
1428 1064 2009-09-09 00:01:00.000 2009-09-10 00:01:00.000 800
1428 1064 2009-09-10 00:01:00.000 2009-09-11 00:01:00.000 800
1428 1064 2009-09-11 00:01:00.000 2009-09-12 00:01:00.000 800
1428 1064 2009-09-12 00:01:00.000 2009-09-13 08:59:00.000 800*/修正时间,为什么最后一条的日期是一样(都是09-13),不明白
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([schedule_id] int,[job_id] int,[start_time] datetime,[end_time] datetime,[total_count] int)
insert [TB]
select 1428,1064,'2009-09-08 00:01:00.000','2009-09-13 08:59:00.000',4000 union all
select 1432,1068,'2009-09-08 10:31:00.000','2009-09-12 08:59:00.000',3000 union all
select 1472,1092,'2009-09-11 12:20:00.000','2009-09-14 08:59:00.000',3400 union all
select 1483,1103,'2009-09-11 22:10:00.000','2009-09-13 08:59:00.000',2500 union all
select 1485,1104,'2009-09-11 22:25:00.000','2009-09-14 08:59:00.000',2000 union all
select 1518,1125,'2009-09-17 14:51:00.000','2009-09-20 08:59:00.000',3000 union all
select 1534,1136,'2009-09-18 14:05:00.000','2009-09-20 08:59:00.000',1800select * from [TB]select schedule_id,
job_id,
start_time=dateadd(dd,number,start_time),
end_time=case when datediff(hh,dateadd(dd,number,start_time),end_time)<24 then end_time else dateadd(dd,number+1,start_time) end,
[total_count]=[total_count]/(select datediff(day,[start_time],[end_time]) from TB WHERE [schedule_id]=B.[schedule_id])
from master..spt_values A,TB B
where type='p' and DATEDIFF(dd,dateadd(dd,number,start_time),end_time)>=0
----------------------------------------------
1428 1064 2009-09-08 00:01:00.000 2009-09-09 00:01:00.000 800
1428 1064 2009-09-09 00:01:00.000 2009-09-10 00:01:00.000 800
1428 1064 2009-09-10 00:01:00.000 2009-09-11 00:01:00.000 800
1428 1064 2009-09-11 00:01:00.000 2009-09-12 00:01:00.000 800
1428 1064 2009-09-12 00:01:00.000 2009-09-13 00:01:00.000 800
1428 1064 2009-09-13 00:01:00.000 2009-09-13 08:59:00.000 800
1432 1068 2009-09-08 10:31:00.000 2009-09-09 10:31:00.000 750
1432 1068 2009-09-09 10:31:00.000 2009-09-10 10:31:00.000 750
1432 1068 2009-09-10 10:31:00.000 2009-09-11 10:31:00.000 750
1432 1068 2009-09-11 10:31:00.000 2009-09-12 08:59:00.000 750
1432 1068 2009-09-12 10:31:00.000 2009-09-12 08:59:00.000 750
1472 1092 2009-09-11 12:20:00.000 2009-09-12 12:20:00.000 1133
1472 1092 2009-09-12 12:20:00.000 2009-09-13 12:20:00.000 1133
1472 1092 2009-09-13 12:20:00.000 2009-09-14 08:59:00.000 1133
1472 1092 2009-09-14 12:20:00.000 2009-09-14 08:59:00.000 1133
1483 1103 2009-09-11 22:10:00.000 2009-09-12 22:10:00.000 1250
1483 1103 2009-09-12 22:10:00.000 2009-09-13 08:59:00.000 1250
1483 1103 2009-09-13 22:10:00.000 2009-09-13 08:59:00.000 1250
1485 1104 2009-09-11 22:25:00.000 2009-09-12 22:25:00.000 666
1485 1104 2009-09-12 22:25:00.000 2009-09-13 22:25:00.000 666
1485 1104 2009-09-13 22:25:00.000 2009-09-14 08:59:00.000 666
1485 1104 2009-09-14 22:25:00.000 2009-09-14 08:59:00.000 666
1518 1125 2009-09-17 14:51:00.000 2009-09-18 14:51:00.000 1000
1518 1125 2009-09-18 14:51:00.000 2009-09-19 14:51:00.000 1000
1518 1125 2009-09-19 14:51:00.000 2009-09-20 08:59:00.000 1000
1518 1125 2009-09-20 14:51:00.000 2009-09-20 08:59:00.000 1000
1534 1136 2009-09-18 14:05:00.000 2009-09-19 14:05:00.000 900
1534 1136 2009-09-19 14:05:00.000 2009-09-20 08:59:00.000 900
1534 1136 2009-09-20 14:05:00.000 2009-09-20 08:59:00.000 900