how to add loop in date column in sql through query?

Question

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

@RDFozz It's similar, but not quite the same. The twist here is that all the missing rows should contain the balance from the last populated row. — Aaron Bertrand, Feb 14 '18 at 18:25

Aaron Bertrand · Answer 1 · 2018-02-14T15:35:04.570

You should always have a calendar table to help solve this kind of problem (and you should try to avoid thinking about anything in SQL Server as a "loop" - it is optimized to work on sets).

-- this produces all the days from 2000-01-01 through 2099-12-31
CREATE TABLE dbo.Calendar(d date PRIMARY KEY);

DECLARE @s date = '20000101', @e date = '20991231';

INSERT dbo.Calendar(d) 
  SELECT DATEADD(DAY, r-1, @s)
  FROM ( SELECT TOP (DATEDIFF(DAY, @s, @e)+1)
           r = ROW_NUMBER() OVER (ORDER BY o.[object_id]) 
         FROM sys.all_objects AS o
         CROSS JOIN sys.all_objects AS c
       ) AS x;

Once you have a calendar table set up, queries can easily use LEFT JOIN to fill in gaps like this. Assuming you have this data:

CREATE TABLE #sample
(
  DAY_no  date, 
  Balance int
);

INSERT #sample(DAY_no,Balance)
VALUES('20180101', 5000 ),
      ('20180110', 10000),
      ('20180115', 12000);

The query is (and I've even used a variable to define the month you're after):

DECLARE @month date = DATEFROMPARTS(2018,1,1);

SELECT DAY_no = c.d,
       MAX(s.Balance)
    FROM dbo.Calendar AS c
    LEFT OUTER JOIN #sample AS s
    ON s.DAY_no <= c.d
    WHERE c.d >= @month
      AND c.d <  DATEADD(MONTH, 1, @month)
    GROUP BY c.d
    ORDER BY c.d;

You'll have to make the query more complicated if your first data point is after the first of the month, of course, since you'll need to pull the last known balance from the previous month.

For more info on calendar tables, see this tip.

score 6 · Answer 2 · answered Feb 14 '18 at 14:16

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 <= '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 <= 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

how to add loop in date column in sql through query?

2 Answers2