T数据孤岛和空白-如何按月汇总事务性数据？

Question

我试图查询一些事务性数据，以便在每个月的CurrentProductionHours结束时为每个报表建立值。

如果每个月的每个报告都有交易，这是非常直接的.我可以使用下面代码中的一些内容来按月划分事务，然后选择TransactionByMonth =1的行(实际上是每个月每个报表的最后一个事务)。

SELECT
    ReportId,
    TransactionId,
    CurrentProductionHours,
    ROW_NUMBER() OVER (PARTITION BY [ReportId], [CalendarYear], [MonthOfYear]
                       ORDER BY TransactionTimestamp desc
                      ) AS TransactionByMonth
FROM
    tblSource

的问题是，不一定每个月都有一个交易.在这种情况下，我需要将最后已知的 CurrentProductionHours值转到没有事务的月份，因为这表明没有任何更改。这个值可能需要多次结转。

资料来源：

ReportId       TransactionTimestamp     CurrentProductionHours
1              2014-01-05 13:37:00      14.50
1              2014-01-20 09:15:00      15.00
1              2014-01-21 10:20:00      10.00
2              2014-01-22 09:43:00      22.00 
1              2014-02-02 08:50:00      12.00

目标结果：

ReportId     Month     Year     ProductionHours
1            1         2014     10.00
2            1         2014     22.00
1            2         2014     12.00
2            2         2014     22.00

我还应该提到，我有一个可用的日期表，如果需要，可以参考它。

** 更新05/03/2014 **

我现在有了一个查询，它生成的结果如下面的示例所示，但剩下的是数据孤岛(在那个月中存在事务)和.我的问题仍然相似，但在某些方面更通用-，如果您将下面的数据集作为起点，那么填补数据孤岛之间空白的最佳方法是什么？

ReportId     Month     Year     ProductionHours
1            1         2014     10.00
1            2         2014     12.00
1            3         2014     NULL
2            1         2014     22.00
2            2         2014     NULL
2            3         2014     NULL 

任何关于如何解决这一问题的建议都将不胜感激！

Answer 1

试试这个：

;with a as
(
select dateadd(m, datediff(m, 0, min(TransactionTimestamp))+1,0) minTransactionTimestamp, 
max(TransactionTimestamp) maxTransactionTimestamp from tblSource
), b as
(
select minTransactionTimestamp TT, maxTransactionTimestamp
from a
union all
select dateadd(m, 1, TT), maxTransactionTimestamp
from b
where tt < maxTransactionTimestamp
), c as
(
select distinct t.ReportId, b.TT from tblSource t
cross apply b
)
select c.ReportId, 
       month(dateadd(m, -1, c.TT)) Month, 
       year(dateadd(m, -1, c.TT)) Year, 
       x.CurrentProductionHours 
from c
cross apply
(select top 1 CurrentProductionHours from tblSource 
where TransactionTimestamp < c.TT 
and ReportId = c.ReportId
order by TransactionTimestamp desc) x

Answer 2

一种类似的方法，但使用笛卡尔来获得所有报表ids/月的组合。在第一步。第二步将源表中的最大时间戳添加到笛卡尔中，其中月份小于或等于当前行中的月份。最后，它通过报告id/时间戳将源表连接到临时表，以获得每个报表id/月的最新源表行。

    ;
WITH    allcombinations -- Cartesian (reportid X yearmonth) 
      AS ( SELECT   reportid ,
                    yearmonth
           FROM     ( SELECT DISTINCT
                                reportid
                      FROM      tblSource
                    ) a
                    JOIN ( SELECT DISTINCT
                                    DATEPART(yy, transactionTimestamp)
                                    * 100 + DATEPART(MM,
                                                     transactionTimestamp) yearmonth
                           FROM     tblSource
                         ) b ON 1 = 1
         ),
    maxdates --add correlated max timestamp where the month is less or equal to the month in current record
      AS ( SELECT   a.* ,
                    ( SELECT    MAX(transactionTimestamp)
                      FROM      tblSource t
                      WHERE     t.reportid = a.reportid
                                AND DATEPART(yy, t.transactionTimestamp)
                                * 100 + DATEPART(MM,
                                                 t.transactionTimestamp) <= a.yearmonth
                    ) maxtstamp
           FROM     allcombinations a
         )
-- join previous data to the source table by reportid and timestamp 
SELECT  distinct m.reportid ,
        m.yearmonth ,
        t.CurrentProductionHours
FROM    maxdates m
        JOIN tblSource t ON t.transactionTimestamp = m.maxtstamp and t.reportid=m.reportid
ORDER BY m.reportid ,
        m.yearmonth