Ndtyjky

I have a discrete time series which looks as follows:

product_id  date    sales_per_day

VSG19   2018-05-19  1.00000000000000

VSG19   2018-05-23  1.00000000000000

VSG19   2018-05-24  2.00000000000000

VSG19   2018-06-25  1.00000000000000

VSG19   2018-07-26  1.00000000000000

VSG19   2018-07-28  1.00000000000000

VSG19   2018-08-01  1.00000000000000

VSG19   2018-08-11  1.00000000000000

VSG19   2018-08-29  1.00000000000000

VSG19   2018-09-11  1.00000000000000

VSG19   2018-09-29  1.00000000000000

VSG19   2018-10-16  1.00000000000000

VSG19   2018-10-25  1.00000000000000

VSG19   2018-11-02  1.00000000000000

I'd like to calculate the linear weighted average for this, but my data does not contain days where no sale occured.

I have solved it by joining a calendar table, but I don't like this solution.
Do you know an elegant way to solve this?

Thanks in advance!

PS - Here's the formula for the LWMA: https://en.wikipedia.org/wiki/Moving_average#Weighted_moving_average

I'm not overly familiar with that particular calculation but from what I just read, you should be able to use "window frames" to calculate the "rolling information need to assign the weights to past sales.
Without seeing the actual formula you're applying, I can't say for sure weather or not it will work or not.

The following is just an example off the top of my head...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 

DROP TABLE #TestData;



CREATE TABLE #TestData (

    product_id CHAR(5) NOT NULL,

    [date] DATE NOT NULL,

    sales_per_day DECIMAL(19,14) NOT NULL 

    );

INSERT #TestData (product_id, date, sales_per_day) VALUES

    ('VSG19', '2018-05-19', 1.00000000000000),

    ('VSG19', '2018-05-23', 1.00000000000000),

    ('VSG19', '2018-05-24', 2.00000000000000),

    ('VSG19', '2018-06-25', 1.00000000000000),

    ('VSG19', '2018-07-26', 1.00000000000000),

    ('VSG19', '2018-07-28', 1.00000000000000),

    ('VSG19', '2018-08-01', 1.00000000000000),

    ('VSG19', '2018-08-11', 1.00000000000000),

    ('VSG19', '2018-08-29', 1.00000000000000),

    ('VSG19', '2018-09-11', 1.00000000000000),

    ('VSG19', '2018-09-29', 1.00000000000000),

    ('VSG19', '2018-10-16', 1.00000000000000),

    ('VSG19', '2018-10-25', 1.00000000000000),

    ('VSG19', '2018-11-02', 1.00000000000000);



--===============================================================



SELECT 

    *,

    days_since_last_sale = ISNULL(DATEDIFF(DAY, MAX(td.date) OVER (ORDER BY td.date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), td.date), 0),

    days_from_first_sale = ISNULL(DATEDIFF(DAY, MIN(td.date) OVER (ORDER BY td.date), td.date), 0)

FROM

    #TestData td;