如何根据日期字段求出字段的和

Question

对不起，如果标题不能公正地回答这个问题，但是如果您有更好的标题，请推荐一个新的标题。

我需要完成的是根据QtyOnHand之间的范围(对于给定的AffectedDate) /和(IssQty)。所以，如果AffectedDate = '2015-2‘，那么我想把所有的IssQty之和，RecDate在'2014-2’到'2015-1‘之间。对于每个AffectedDate行，我想要这种类型的公式。因此，它可以实时计算，这意味着它总是以AffectedDates年、月和- 12为计算值，然后根据RecDate将IssQty相加。以下是一些例子。

ItemKey RecDate IssQty  AffectedDate    QtyOnHand
20406   2014-1   751.898       2014-1           842.132
20406   2014-2   744.102       2014-2           539.03
20406   2014-5   493.847       2014-5           486.183
20406   2014-7   494.834      2014-7            1314.209
20406   2014-8   494.217       2014-8           819.992
20406   2014-9   741.017       2014-9          1401.975
20406   2014-10  889.714       2014-10         512.261
20406   2014-12  740.647       2014-12        640.19
20406   2015-2   496.068       2015-2         144.122
20406   2015-3   496.068       2015-3         530.054
20406   2015-5   370.941       2015-5        159.113
20406   2015-7   989.668       2015-7        492.19
20406   2015-8   792.228       2015-8        890.662
20406   2015-9   744.102       2015-9        1028.56

1. AffectedDate = '2015-3‘因此，以QtyOnHand为例，AffectedDate 530.054 / Sum(IssQty)介于“2014-03”和“2015-02”之间
2. AffectedDate = '2015-5‘，那么就拿QtyOnHand作为AffectedDate 159.113 / Sum(IssQty)，在RecDate '2014-05’和'2015-04‘之间

这些是我希望这个公式起作用的例子。

Answer 1

好的，让我们先从基础开始。正如伯特兰先生所说的，2014-1不是一个约会，而是一个看起来像你在说"2014减去1“(即2013年)。我认为它真正代表的是2014年的第一个月，但是，如果你想存储日期，把它们作为一个date来存储。您会注意到，CONVERT(date,'2014-1)将失败。

我不打算将这些值转换为日期，但我必须在我的示例数据中这样做；也许这会对您有所帮助。

CREATE TABLE #Sample (ItemKey int,
                      RecDate date, --The source data is not a date
                      IssQty decimal(10,3),
                      AffectedDate date, --The sourse data is not a date
                      QtyOnHand decimal(10,3));
INSERT INTO #Sample
SELECT ItemKey,
       CONVERT(date,CASE LEN(RecDate) WHEN 6 THEN STUFF(RecDate,5,1,'0') + '01'
                                      WHEN 7 THEN REPLACE(RecDate,'-','') + '01' END),
       IssQty,
       CONVERT(date,CASE LEN(AffectedDate) WHEN 6 THEN STUFF(AffectedDate,5,1,'0') + '01'
                                           WHEN 7 THEN REPLACE(AffectedDate,'-','') + '01' END),
       QtyOnHand
FROM (VALUES (20406,'2014-1',751.898,'2014-1',842.132  ),
             (20406,'2014-2',744.102,'2014-2',539.03   ),
             (20406,'2014-5',493.847,'2014-5',486.183  ),
             (20406,'2014-7',494.834,'2014-7',1314.209 ),
             (20406,'2014-8',494.217,'2014-8',819.992  ),
             (20406,'2014-9',741.017,'2014-9',1401.975 ),
             (20406,'2014-10',889.714,'2014-10',512.261),
             (20406,'2014-12',740.647,'2014-12',640.19 ),
             (20406,'2015-2',496.068,'2015-2',144.122  ),
             (20406,'2015-3',496.068,'2015-3',530.054  ),
             (20406,'2015-5',370.941,'2015-5',159.113  ),
             (20406,'2015-7',989.668,'2015-7',492.19   ),
             (20406,'2015-8',792.228,'2015-8',890.662  ),
             (20406,'2015-9',744.102,'2015-9',1028.56  )) V(ItemKey, RecDate, IssQty, AffectedDate, QtyOnHand);

接下来，我们在这里少了几个月，这是一个问题，因为您需要最后12个月的数据。因此，我们需要填补这些问题。我将使用一个日历表(您需要搜索这个，在这里挖掘他的DimDate )。

然后，因为我们对每一行都有记录，所以我们可以使用ROWS BETWEEN，结果是：

WITH MaxMin AS(
    SELECT ItemKey, MIN(AffectedDate) AS MinDate, MAX(AffectedDate) AS MaxDate
    FROM #Sample
    GROUP BY ItemKey),
WithNewColumn AS(
    SELECT MM.ItemKey,
           S.RecDate,
           S.IssQty,
           DD.[Date] AS AffectedDate,
           S.QtyOnHand,
           S.QtyOnHand / SUM(S.IssQty) OVER (PARTITION By MM.ItemKey ORDER BY DD.[Date]
                                             ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING) AS YourNewColumn
    FROM MaxMin MM
         JOIN DimDate DD ON DD.[Date] BETWEEN MM.MinDate AND MM.MaxDate AND DD.[Calendar Day] = 1
         LEFT JOIN #Sample S ON MM.ItemKey = S.ItemKey
                            AND DD.[Date] = S.AffectedDate)
SELECT *
FROM WithNewColumn
WHERE RecDate IS NOT NULL --eliminate the NULLs (if you want to). if not, this isn't needed, nor the CTE above
ORDER BY ItemKey, AffectedDate;

Answer 2

如果您的日期中没有空白，您可以只使用一个sum窗口函数来解决问题。所以你需要自己加入。这里有一个使用apply的选项

select 
    *, a.QtyOnHand / c.total
from 
    myTable a
    outer apply (
        select
            total = sum(IssQty)
        from
            myTable b
        where
            b.RecDate between dateadd(mm, -12, a.AffectedDate) and dateadd(mm, -1, a.AffectedDate)
    ) c