Click here to Skip to main content
15,117,549 members
Please Sign up or sign in to vote.
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.

1 solution

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)




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