You can use
recursive query (CTE)[
^]. See:
SET DATEFORMAT dmy;
CREATE TABLE Balance
(
[Date] date,
Bal int
);
INSERT INTO Balance ([Date], Bal)
VALUES('02-04-2020', 200),
('03-04-2020', 100),
('05-04-2020', 300),
('08-04-2020', 400)
;WITH CTE AS
(
SELECT MIN([Date]) MinDate, MIN([Date]) CurrDate, Max([Date]) MaxDate
FROM Balance
UNION ALL
SELECT MinDate, DATEADD(DD, 1, CurrDate) CurrDate, MaxDate
FROM CTE
WHERE DATEADD(DD, 1, CurrDate)<= MaxDate
)
SELECT c.CurrDate, b.Bal
FROM CTE c LEFT JOIN Balance b ON c.CurrDate = b.[Date]
Link to
db<>fiddle[
^]
Above query produces this:
CurrDate Bal
2020-04-02 200
2020-04-03 100
2020-04-04
2020-04-05 300
2020-04-06
2020-04-07
2020-04-08 400
Now, it's your turn. Improve this to your needs.
Submit an article or tip