Account balance SQL

Database
Enthusiast

Account balance SQL


A_date A_bal

1/1/2014 10,000

1/15/2014 15,000

1/25/2014 13,000


I have sample account balance information as shown above. i.e. 01-Jan 10K, 15th Jan 15K and 25th Jan 13K.

Now I want to show the balance for all the 31 days of Jan month. like below 
A_date         A_bal
1/1/2014 10,000
1/2/2014 10,000
. .
. .
. .
1/14/2014 10,000
1/15/2014 15,000
1/16/2014 15,000
. .
. .
. .
1/24/2014 15,000
1/25/2014 13,000
1/26/2014 13,000
. .
. .
1/31/2014 13,000

Can you please suggest what can be the possible SQL.

Regards,

Koushik

7 REPLIES

Re: Account balance SQL

Hi Kaushik,

Are you expecting to display the result date order..?

Enthusiast

Re: Account balance SQL

Hi Koushik,

What I get from your question, you can use the following simple select.

SELECT A_DATE, A_BAL

FROM TABLE NAME

WHERE A_DATE BETWEEN DATE '01-01-2014 AND DATE '31-01-2014'

ORDER BY A_DATE;
Khurram
Enthusiast

Re: Account balance SQL

Hi Saeed,

Your query will give below output :

A_date         A_bal
1/1/2014 10,000
1/15/2014 15,000
1/25/2014 13,000

But I want the output in the below way where all the 31 days of January month will be displayed :

A_date          A_bal
1/1/2014 10,000
1/2/2014 10,000
. .
. .
. .
1/14/2014 10,000
1/15/2014 15,000
1/16/2014 15,000
. .
. .
. .
1/24/2014 15,000
1/25/2014 13,000
1/26/2014 13,000
. .
. .
1/31/2014 13,000

Regards,

Koushik

Enthusiast

Re: Account balance SQL

Ok, You want to repeat these values for all dates, is there any specific criteria for repitition OR we can repeat in random fashion like for first 10 days it can 10k and then for next 10 days 15k and for next 11 days 13k?

Khurram
Enthusiast

Re: Account balance SQL

try this:

SEL  cdate, MIN(BAL) FROM TABLENAME, Sys_Calendar.CALDATES
WHERE cdate BETWEEN DATE '2014-01-01' AND DATE '2014-01-10'
GROUP BY cdate
UNION
SEL cdate, MAX(BAL) FROM TABLENAME, Sys_Calendar.CALDATES
WHERE cdate BETWEEN DATE '2014-01-11' AND DATE '2014-01-20'
GROUP BY cdate
UNION
SEL cdate, BAL FROM Sys_Calendar.CALDATES,
(SELECT BAL, RANK() OVER(ORDER BY BAL DESC) AS RNK
FROM TABLENAME
QUALIFY RNK = 2
) DRV
WHERE cdate BETWEEN DATE '2014-01-21' AND DATE '2014-01-31'
ORDER BY 1
Khurram
Senior Apprentice

Re: Account balance SQL

Do you need this for a single account or multiple accounts?

What's your TD release?

KVB
Enthusiast

Re: Account balance SQL

Are there only two columns  in the table.What are the primary indexes.Please mention with PI so that it will be easy to code.