Home » SQL & PL/SQL » SQL & PL/SQL » SQL
SQL [message #221257] Mon, 26 February 2007 00:40 Go to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Hi,

Please help for the below output

ENT_DATE TOTAL_BALANCE
2/23/2007 50
2/24/2007 100
2/25/2007 150

OUTPUT
Date Opening_Bal Closing_Bal
2/24/2007 50 100
2/25/2007 100 150


Arvind
Re: SQL [message #221265 is a reply to message #221257] Mon, 26 February 2007 01:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
select 'ENT_DATE TOTAL_BALANCE
2/23/2007 50
2/24/2007 100
2/25/2007 150

OUTPUT
Date Opening_Bal Closing_Bal
2/24/2007 50 100
2/25/2007 100 150'
from dual;


Just kidding, but please, explain a little bit more, show what you tried so far, etc.
(e.g. why would the 2/23/2007 suddenly become a 2/24/2007?)
Re: SQL [message #221279 is a reply to message #221265] Mon, 26 February 2007 03:03 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
User input the date range 24/02/2007 to 25/02/2007

But I need the closing balance of 23/02/2007 of the opening balance of 24/02/2007 ……..


ENT_DATE TOTAL_BALANCE
2/23/2007 50
2/24/2007 100
2/25/2007 150


Need output as per below format of opening balance and closing balance

Date Opening_Bal Closing_Bal
2/24/2007 50 100
2/25/2007 100 150
Re: SQL [message #221359 is a reply to message #221279] Mon, 26 February 2007 08:00 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
You can do this using a lag function.
Re: SQL [message #221381 is a reply to message #221359] Mon, 26 February 2007 08:49 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
please help for the lag function
Re: SQL [message #221382 is a reply to message #221381] Mon, 26 February 2007 09:12 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Sure: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions070.htm#sthref1488
Re: SQL [message #221480 is a reply to message #221382] Tue, 27 February 2007 02:18 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming you have one (and only one) record for each day, it will look like this:
SELECT en_Date
      ,lag(total_balance) over (order by en_date) opening_bal
      ,total_balance closing_bal
from   your_table
Previous Topic: Adjust to local time
Next Topic: Where to go from here?
Goto Forum:
  


Current Time: Wed Dec 07 06:43:07 CST 2016

Total time taken to generate the page: 0.13289 seconds