SQLTeam.com | Weblogs | Forums

Add rows


#1

Hi

i have this table,
this example of one customer.for each customer and product i have in the end of moth a discount
the problem that i do not have for each month,customer,product a discount.
so i need to take the privoius of the missing month the row and create with a new month,

for example

Date                       Customer                  Product          Discount
31.01.2016                 KOLIN                       A                   10
28.02.2016                 KOLIN                       A                   10
31.03.2016                 KOLIN                       A                   30
30.06.2016                 KOLIN                       A                   10

this rows that i need to create
i fount that 4,5 is miising, the last month before 4 is 3(march) and multiple month-3 for 4,5

    Date                       Customer                  Product          Discount
    30.04.2016                 KOLIN                       A                   30
    31.05.2016                 KOLIN                       A                   30

thanks


#2

A calendar table would be the best option - if you don't have one then you can dynamically build one using a numbers or tally table. With that you would have a row for each date - then outer join your table on the date column - where missing you would have NULL values that you can then populate.

To get the previous value you can use the LAG function (SQL Server 2012 or higher).