15,117,549 members
0.00/5 (No votes)
See more:
Hello Coders,

I have table in which i have two fields date & balance, I have records only for working days but i want records for each date for missing date previous day balance should be displayed, kindly help me with this query

What I have

Date Bal
02-04-2020 200
03-04-2020 100
05-04-2020 300
08-04-2020 400

What I want

02-04-2020 200
03-04-2020 100
04-04-2020 100
05-04-2020 300
06-04-2020 300
07-04-2020 300
08-04-2020 400

What I have tried:

Nothing I tried I am clueless on this requirement
Posted
Updated 12-Oct-20 11:37am
Comments
Gerry Schmitz 12-Oct-20 14:07pm

In the absence of "why", one doesn't store "calculated values"; you produce them when necessary.

If the query was for the balance of say 07-04-2020, then you find the TOP 1 balance record with a date 'equal or less than' the target date.

## Solution 1

You can use recursive query (CTE)[^]. See:

SQL
```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.
Comments
Sandeep Mewara 13-Oct-20 8:48am

5
Maciej Los 13-Oct-20 14:38pm

Thank you, Sandeep.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Richard Deeming 140 OriginalGriff 130 CHill60 110 phil.o 55 Richard MacCutchan 45
 Richard Deeming 140 CHill60 110 OriginalGriff 95 phil.o 55 Richard MacCutchan 40

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900