Let´s imagine a situation that you have a small market in which you have the inventory stored in a database table. In this table we have the date, the product code and the quantity available, but we don´t have the information for all days. The information in the tables refers only to the days the information changed, as you can see in the picture below.
As you can see in the pcture above, we have some gaps between dates, I mean we don´t have the information for the dates between ‘2021-01-01’ and ‘2021-01-10’ (‘2021-01-02′,’2021-01-03′,’2021-01-04′,’2021-01-05′,’2021-01-06′,’2021-01-07′,’2021-01-08′,’2021-01-09’). So we have gaps between information and we want to fill these gaps.
We will create a codification in SQL to fill these gaps, in order to make them as shown in the picture below.
We will see the step-by-step of a solution for this case using a transact SQL (T-SQL).
For the solution we will implement the algorithm below.
Lets consider the steps below for implementation:
– DECLARE VARIABLES
– DECLARE CURSOR WITH DATES
– OPEN CURSOR
– FETCH CURSOR INTO DAY N
– FETCH CURSOR INTO DAY N+1
– ENTER LOOP TO INSERT DATES BETWEEN N AND N+1
– FETCH CURSOR INTO N+1
DECLARE VARIABLES
Initially we will declare the variables we need to implement the solution. We will explain the purpose of each of them.
DECLARE CURSOR WITH DATES
We need to declare a cursor which will store the dates in order ascendent
OPEN CURSOR
FETCH CURSOR INTO DAY N
In this FETCH we will store the content of the date into the variable @DATE_INVENT_PREV
FETCH CURSOR INTO DAY N+1
In this FETCH We will store the content of the date into the variable @DATE_INVENT
ENTER LOOP TO INSERT DATES BETWEEN N AND N+1
If the difference between the dates is bigger than 1, the process will get into the loop.
FETCH CURSOR INTO N+1
After inserting the lines to fill the gap, the loop will run again until the list of dates is finished.
RESULTS
The code for this solution is on the link below