How to get the sum of a field based on a date field

Question

Sorry, if the title does not do this question justice, but please recommend a new title, if you have a better one.

What i need to accomplish is i need to take QtyOnHand (for a given AffectedDate) / sum(IssQty) based on a range between RecDates. So if the AffectedDate = '2015-2' then i want to sum all of the IssQty where RecDate is between '2014-2' and '2015-1'. I want this type of formula for every AffectedDate row. So it can calculate on the fly, meaning it will always take the AffectedDates year and month and - 12 and then sum together the IssQty, based on the RecDate. Here are some Examples.

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' so take the QtyOnHand for that AffectedDate [530.054] / Sum(IssQty) between the RecDate of '2014-03' and '2015-02'

2. AffectedDate = '2015-5' so take the QtyOnHand for that AffectedDate [159.113] / Sum(IssQty) between the RecDate of '2014-05' and '2015-04'

These are examples of how i want this formula to work.

Answer 1

Ok, let's start at the basics first. Like Mr Bertrand said 2014-1 is not a date, it's a varchar that looks like you're saying "2014 minus 1" (which is 2013). I assume that what it's really meant to represent is the 1st month of 2014, however, if you want to store dates, store them as a date. you'll note that CONVERT(date,'2014-1) will fail.

I'm not going to cover converting these values to dates, but i have HAD to do it in my sample data; maybe that'll help you.

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);

Next, we're missing some months here, which is a problem, as you want the last 12 months of data. Thus we need to fill those in. I'll be using a Calendar table (you'll need to Google this, mine his called DimDate here).

Then, because we have a record for every row, we can use ROWS BETWEEN resulting in:

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

You could solve your issue with only one sum window function if you did not have gaps in your dates. So you need self join. Here is one option using 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