>> Need some help in filling up gaps between 2 dates.
Scenario: I have (product_name, location_code, balance) snapshot for specific dates as shown below and want to produce query to show carry forward the balance between two dates. <<

Your attempted DDL had a lot of problems. By definition, a table must have a key. You also don’t know that there’s only one standard display format allowed in ANSI/ISO standard SQL. So first thing let’s go ahead and correct what you did post:

CREATE TABLE Product_History
(product_name CHAR(1) NOT NULL CHECK (product_name LIKE ‘[A-Z]’),
location_code CHAR(1) NOT NULL CHECK (location_code LIKE ‘[0-9]’)
trans_date DATE,
trans_amt DECIMAL (5,2) NOT NULL,
PRIMARY KEY (product_name, location_code, trans_date));

you might want to take some time to read the ISO 11179 naming rules.

Example: product_name, a location_code and a balance on 2018-03-01 was 25 and there is not activity between 2018-03-01 and 2018-03-06 so the balance between these 2 dates should remain 25 and on 2018-03-06 it will change to 50 and then again after that it carry forward balance.

But more than that, you don’t seem to appreciate the fact that, SQL is a declarative language, and you not supposed to be writing procedural code in it. We hate loops. We hate if-then-else statements. We hate local variables. We treat virtual tables (views, derived tables, etc.) as equal to materialized base tables. In fact, they have an advantage in that since they are recomputed each time there materialized, were sure that there’d information is current.

I’m also curious; why did you have an integer for a balance?

CREATE TABLE Product_History
(product_name CHAR(1) NOT NULL CHECK (product_name LIKE ‘[A-Z’]),
location_code CHAR(1) NOT NULL CHECK (location_code LIKE ‘[0-9]’)
trans_date DATE,
trans_balance_amt DECIMAL (5,2) NOT NULL,
PRIMARY KEY (product_name, location_code, trans_date));

INSERT INTO Product_History
VALUES
('A', ‘1’, '2018-02-01', 50.00),
('A', ‘1’, '2018-02-15', 75.00),
('A', ‘2’, '2018-03-01', 25.00),
('A', ‘2’, '2018-03-06', 50.00),
('B', ‘1’, '2018-03-01', 25.00);

I’d like you to stop and take some time to Google what a “calendar table†is and how to use it. It’s basically just what the name says; a list of dates, that uses a calendar date as its primary key and might include other information (we don’t care about the other stuff for now).

What we want to do is get all of the dates in the calendar table in the range that comes in his parameters. From this, if a particular calendar date has a balance in the history, then replicate the amount forward within partitions.

Here is a deliberately bad answer. Look at the results, make some corrections and get it to work (this is really a good exercise and will get you away from thinking in terms of loops).

CREATE PROCEDURE Daily_Product_Balances
 (@in_start_date DATE, @in_end_date DATE)
AS
SELECT H.product_name, H.location_code, C.cal_date,
   FIRST_VALUE(trans_balance_amt)
   OVER (PARTITION BY H.product_name, H.location_code
     ORDER BY C.cal_date)
FROM Calendar AS C
   LEFT OUTER JOIN
   Product_History AS H
   ON C.cal_date BETWEEN @in_start_date AND @in_end_date
 AND H.trans_date BETWEEN @in_start_date AND @in_end_date;