MSSQL 基础之补全日期

今天在技术群，有位群友提到一个关于T-SQL查询的需求，在此权当分享记录。

需求：在数据库有如下数据：

希望把缺失的日期都补上，然后补上的S列值取前一列的值 /7，即2012-12-04  -- 2012-12-10 的S列值为：1058.142856556400/7，日期的特点是：两个日期间隔7天。补全的数据如下：

补全日期简单，可以使用spt_values表的自增特点实现，S列如何生成数据比较恼人，关键点是如何找到参考值。好在有一个隐含条件，间隔7天。

WITH tmp AS
(
 SELECT DATEADD(d,number,'2012-12-03') d,number % 7 number
  FROM master..spt_values
  WHERE type='P' AND DATEADD(d,number,'2012-12-03') <= '2012-12-17'
)
,tmp1 AS
(
 SELECT '2012-12-03' A,1058.142856556400 S
 UNION ALL
 SELECT '2012-12-10',1054.571551871100
 UNION ALL
 SELECT '2012-12-17',1058.285714285200  
)
SELECT d,number,CASE WHEN number = 0 THEN tmp1.S ELSE tmp1.S/7 END
 FROM tmp
 LEFT JOIN tmp1 ON tmp.d = tmp1.A OR tmp.d = DATEADD(d,number,tmp1.A)

需求实现，接着又有群友提出新的需求，如果时间间隔不固定怎么办。这样隐含条件失败，如果才到参考值呢。思路还是得放在补全日期和数据日期的关系上。

WITH tmp AS
(
 SELECT DATEADD(d,number,'2012-12-03') d,number % 7 number
  FROM master..spt_values
  WHERE type='P' AND DATEADD(d,number,'2012-12-03') <= '2012-12-17'
)
,tmp1 AS
(
 SELECT '2012-12-03' A,1058.142856556400 S
 UNION ALL
 SELECT '2012-12-12',1054.571551871100
 UNION ALL
 SELECT '2012-12-15',1058.285714285200  
)
SELECT d,number,CASE WHEN tmp1.S IS null THEN t.S / 7 ELSE tmp1.S END,t.A
 FROM tmp
 LEFT JOIN tmp1 ON tmp.d = tmp1.A
 OUTER APPLY (SELECT TOP 1 * FROM tmp1 WHERE tmp.d > A ORDER BY A DESC) t

需求实现，接着群友又提出更BT的需求，之前补全值是前一列除以固定的7，现在要求除以两个日期间隔天数。

WITH tmp AS
(
 SELECT DATEADD(d,number,'2012-12-03') d,number % 7 number
  FROM master..spt_values
  WHERE type='P' AND DATEADD(d,number,'2012-12-03') <= '2012-12-17'
)
,tmp1 AS
(
 SELECT '2012-12-03' A,1058.142856556400 S
 UNION ALL
 SELECT '2012-12-12',1054.571551871100
 UNION ALL
 SELECT '2012-12-17',1058.285714285200  
)
SELECT d,number,CASE WHEN tmp1.S IS null THEN t.S / DATEDIFF(d,t.A,m.A) ELSE tmp1.S END,t.A,m.A
 FROM tmp
 LEFT JOIN tmp1 ON tmp.d = tmp1.A
 OUTER APPLY (SELECT TOP 1 * FROM tmp1 WHERE tmp.d > A ORDER BY A DESC) t
 OUTER APPLY (SELECT TOP 1 * FROM tmp1 WHERE tmp.d <= A ORDER BY A) m

需求实现。