how to add loop in date column in sql through query? If ID = 01234 and this ID has done transaction in 3 time in a month like this:

***DAY_no              Balance*** 
   1/1/2018              5000
   10/1/2018             10000
   15/1/2018             12000


I want these data like this:

***DAY_no              Balance*** 
   1/1/2018              5000
   2/1/2018              5000
   3/1/2018              5000
   4/1/2018              5000
   5/1/2018              5000
   6/1/2018              5000
   7/1/2018              5000
   8/1/2018              5000
   9/1/2018              5000
   10/1/2018             10000
   11/1/2018             10000
   12/1/2018             10000
   13/1/2018             10000
   14/1/2018             10000
   15/1/2018             12000
   16/1/2018             12000
   17/1/2018             12000
   18/1/2018             12000
   19/1/2018             12000
   20/1/2018             12000
   21/1/2018             12000
   22/1/2018             12000
   23/1/2018             12000
   24/1/2018             12000
   25/1/2018             12000
   26/1/2018             12000
   27/1/2018             12000
   28/1/2018             12000
   29/1/2018             12000
   30/1/2018             12000
   31/1/2018             12000
You can use a calendar table:




CREATE TABLE Calendar(cDate datetime, cDay int, cDayOfWeek int, cDayName varchar(20), cMonth int);

DECLARE @date date = '20180101';
WHILE @date &lt;= '20180131'
BEGIN
    INSERT INTO Calendar VALUES (@date, 
                                 DAY(@date), 
                                 DATEPART(weekday, @date), 
                                 DATENAME(weekday, @date),
                                 MONTH (@date));
    SET @date = DATEADD(day, 1, @date);
END


CREATE TABLE Mov (Day_no date, Balance int);
INSERT INTO Mov VALUES
('20180101', 5000),
('20180110', 10000),
('20180115', 12000);
GO



34 rows affected






SELECT cDAte, (SELECT TOP 1 Balance
               FROM   Mov
               WHERE  Day_no &lt;= cDate
               ORDER BY Day_no DESC) Balance
FROM   Calendar
WHERE cMonth = 1;
GO



cDAte               | Balance
:------------------ | ------:
01/01/2018 00:00:00 |    5000
02/01/2018 00:00:00 |    5000
03/01/2018 00:00:00 |    5000
04/01/2018 00:00:00 |    5000
05/01/2018 00:00:00 |    5000
06/01/2018 00:00:00 |    5000
07/01/2018 00:00:00 |    5000
08/01/2018 00:00:00 |    5000
09/01/2018 00:00:00 |    5000
10/01/2018 00:00:00 |   10000
11/01/2018 00:00:00 |   10000
12/01/2018 00:00:00 |   10000
13/01/2018 00:00:00 |   10000
14/01/2018 00:00:00 |   10000
15/01/2018 00:00:00 |   12000
16/01/2018 00:00:00 |   12000
17/01/2018 00:00:00 |   12000
18/01/2018 00:00:00 |   12000
19/01/2018 00:00:00 |   12000
20/01/2018 00:00:00 |   12000
21/01/2018 00:00:00 |   12000
22/01/2018 00:00:00 |   12000
23/01/2018 00:00:00 |   12000
24/01/2018 00:00:00 |   12000
25/01/2018 00:00:00 |   12000
26/01/2018 00:00:00 |   12000
27/01/2018 00:00:00 |   12000
28/01/2018 00:00:00 |   12000
29/01/2018 00:00:00 |   12000
30/01/2018 00:00:00 |   12000
31/01/2018 00:00:00 |   12000



dbfiddle here