拆分跨天的时间数据

WITH    x0
AS (
       SELECT ‘A‘ AS     Ident
             ,139185  AS PlayTime
             ,DATEADD(SS,-256199,‘2016-01-11 20:59:43.243‘) date_begin
             ,CONVERT(DATETIME ,‘2016-01-11 20:59:43.243‘) AS date_end
             ,256199 AS OnlineTime
       UNION ALL
       SELECT ‘B‘ AS     Ident
             ,6085  AS PlayTime
             ,DATEADD(SS,-6199,‘2015-10-18 00:12:42.840‘) date_begin
             ,CONVERT(DATETIME ,‘2015-10-18 00:12:42.840‘) AS date_end
             ,6199 AS OnlineTime
   ),/*计算两个时间点之间相差的天数*/
x1
AS (
       SELECT Ident
             ,PlayTime
             ,date_begin
             ,date_end
             ,OnlineTime
             ,DATEDIFF(DAY ,date_begin ,date_end) AS cacl_day--开始时间和结束时间相差的天数
       FROM   x0
   ),/*将隔天的时间分解*/
x2        
AS (
       SELECT Ident
             ,PlayTime
             ,CASE
                   WHEN msv.number=0 THEN date_begin
                   ELSE CONVERT(VARCHAR(10) ,DATEADD(DAY ,msv.number ,date_begin) ,120)
              END                 AS date_begin
             ,CASE
                   WHEN msv.number=x.cacl_day THEN date_end
                   ELSE CONVERT(VARCHAR(10) ,DATEADD(DAY ,msv.number ,date_begin) ,120)
                       +‘ 23:59:59.997‘
              END                 AS date_end
             ,OnlineTime
             ,CASE
                   WHEN msv.number=x.cacl_day THEN 1
                   ELSE 0
              END                 AS flag--如果是最后一天，标识为1，否则为0
       FROM   x1 x
             ,MASTER..spt_values     msv
       WHERE  msv.type = ‘P‘
              AND msv.number<= x.cacl_day
   ),
x3
AS (
       SELECT Ident
             ,PlayTime
             ,date_begin
             ,date_end
             ,OnlineTime
             ,CASE
                   WHEN CONVERT(CHAR(8) ,date_end ,108)=‘23:59:59‘ THEN DATEDIFF(SS ,date_begin ,date_end)+1
                   ELSE DATEDIFF(SS ,date_begin ,date_end)
              END AS cacl_OnlineTime
             ,flag
             ,ROW_NUMBER() OVER(PARTITION BY Ident ORDER BY date_end) AS rn--行号
       FROM   x2
   )
SELECT *
      ,CASE 
            WHEN playtime>=(
                     SELECT SUM(cacl_OnlineTime)
                     FROM   x3 x
                     WHERE  x.Ident = x3.Ident
                            AND x.rn<= x3.rn
                 ) THEN cacl_OnlineTime
            WHEN playtime<(
                     SELECT SUM(cacl_OnlineTime)
                     FROM   x3 x
                     WHERE  x.Ident = x3.Ident
                            AND x.rn<= x3.rn
                 )
                 AND playtime>(
                     SELECT ISNULL(SUM(cacl_OnlineTime) ,0)
                     FROM   x3 x
                     WHERE  x.Ident = x3.Ident
                            AND x.rn<x3.rn
                 ) THEN playtime-(
                     SELECT ISNULL(SUM(cacl_OnlineTime) ,0)
                     FROM   x3 x
                     WHERE  x.Ident = x3.Ident
                            AND x.rn<x3.rn
                 )
            ELSE 0
       END cacl_PlayTime
FROM   x3
ORDER BY Ident,date_begin