Home » SQL & PL/SQL » SQL & PL/SQL » Need Help on query (oracle 10g)
Need Help on query [message #637080] Sun, 10 May 2015 03:58 Go to next message
samyms
Messages: 18
Registered: May 2015
Location: Chennai
Junior Member
please help me to write a query

input table;(yr,qtr not in order)

Yr qtr amt
2015 2 4300
2015 1 3000
2014 4 1270
2014 3 1250
2014 2 1100
2014 1 1400

output table:
Yr qtr amt preamt
2015 2 4300 3000
2015 1 3000 1270
2014 4 1270 1250
2014 3 1250 1100
2014 2 1100 1400
2014 1 1400

[Updated on: Sun, 10 May 2015 03:59]

Report message to a moderator

Re: Need Help on query [message #637082 is a reply to message #637080] Sun, 10 May 2015 04:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You need to explain the rules for the transformation; you need to provide the one CREATE TABLE statement and the six INSERT statements so that people can set up the problem; and you need to show what SQL you have tried so far. Remember that no-one is going to do your college homework for you, and that you have to try first.
Re: Need Help on query [message #637083 is a reply to message #637082] Sun, 10 May 2015 04:12 Go to previous messageGo to next message
samyms
Messages: 18
Registered: May 2015
Location: Chennai
Junior Member
select tb1.yr,tb1.qtr,tb1.amt,
case when tb1.qtr ='4' then (select amt from tb2 where tb2.yr=tb1.yr and tb2.qtr=tb1.qtr-1)
else
(select amt from tb2 where tb2.yr=tb1.yr and tb2.qtr=tb1.qtr-1) END
as preamt
from tb1 order by tb1.yr,tb1.qtr desc

this is what i have written
Re: Need Help on query [message #637084 is a reply to message #637083] Sun, 10 May 2015 04:15 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I have no idea if your code is correct, because you have not shown what it has produced, and I certainly can't run it. Furthermore, you have ignored all my suggestions and clearly you have not read the docs to which I directed you.

Not a very good way to introduce yourself to a professional forum. Do you want to try again?
Re: Need Help on query [message #637086 is a reply to message #637080] Sun, 10 May 2015 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
(yr,qtr not in order)


And what is the order?
If "preamt" means "previous amt" then we need you define what is the order otherwise "previous" is meaningless.

Re: Need Help on query [message #637089 is a reply to message #637086] Sun, 10 May 2015 06:09 Go to previous messageGo to next message
samyms
Messages: 18
Registered: May 2015
Location: Chennai
Junior Member
What i mean is , the order of the record. Never mind about order.we can sort it.

2015 Q2 2500
2015 Q3 3000
2015 Q1 1000

I want show the previous amount for every record based on qtr and year.


Re: Need Help on query [message #637090 is a reply to message #637089] Sun, 10 May 2015 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at LAG function.

[Updated on: Sun, 10 May 2015 06:14]

Report message to a moderator

Re: Need Help on query [message #637092 is a reply to message #637090] Sun, 10 May 2015 06:24 Go to previous messageGo to next message
samyms
Messages: 18
Registered: May 2015
Location: Chennai
Junior Member
Thank you So much Mike.
Re: Need Help on query [message #637723 is a reply to message #637092] Fri, 22 May 2015 06:33 Go to previous messageGo to next message
samyms
Messages: 18
Registered: May 2015
Location: Chennai
Junior Member
got the ans

[Updated on: Fri, 22 May 2015 07:33]

Report message to a moderator

Re: Need Help on query [message #637725 is a reply to message #637723] Fri, 22 May 2015 08:19 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
samyms wrote on Fri, 22 May 2015 04:33
got the ans


Please post it here so others may benefit from it.
Previous Topic: Select max date in row of columns. . .
Next Topic: Problem with calculating hours
Goto Forum:
  


Current Time: Thu Mar 28 19:28:38 CDT 2024