All Forums Database
Koushik Chandra 10 posts Joined 08/12
14 Jan 2014
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

 

rvvs999 3 posts Joined 11/13
15 Jan 2014

Hi Kaushik,
Are you expecting to display the result date order..?
 

M.Saeed Khurram 544 posts Joined 09/12
15 Jan 2014

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

Koushik Chandra 10 posts Joined 08/12
15 Jan 2014

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

M.Saeed Khurram 544 posts Joined 09/12
15 Jan 2014

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

M.Saeed Khurram 544 posts Joined 09/12
15 Jan 2014

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

dnoeth 4628 posts Joined 11/04
15 Jan 2014

Do you need this for a single account or multiple accounts?
What's your TD release?
 

Dieter

KVB 124 posts Joined 09/12
16 Jan 2014

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.

You must sign in to leave a comment.