这个表的查询结果如下
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里面的,谢谢了

解决方案 »

  1.   

    --> 测试数据:@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 
    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*/
      

  2.   


    --> 测试数据:[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
      

  3.   

    --> 测试数据:@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),不明白
      

  4.   

    老吴,试试这个,呵呵
    --> 测试数据:[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
      

  5.   

    可能测试数据造得有点问题,导致出现最后一行starttime>endtime了,你用实际数据看看吧。