Nsryjdtyk

I need to create a Table of date,product and inventory count only for the days inventory 0 , something like this

Date Product store Inv

Jan1  1       1      0

Feb4  1       1      0

From the inventory table that only has a record whenever inventory changes
Like this

 Store Product start_date end_date Inv

 1      1      Jan 4      Jan10    5

 1      1      Jan10      jan 15   4

I know I can create a master table by cross joining all store,product and calendar days in a year and then join only with days where date falls between start and end date of the inventory table. Is there a better way than this ? Can cross join be avoided ? Thanks

Are you looking for lag():

select t.*

from (select t.*,

             lag(inventory) over (partition by product, store order by date) as prev_inventory

      from t

     ) t

where prev_inventory is null or prev_inventory <> inventory;

Create a table with dates (which can be handy to have around for a ton of reasons) then left join from the inventory table and use BETWEEN against your date columns.