Logic for designing SQL query
Please see the table below:
Sub From To Time
A C E 1
A E C 2:30
A c E 3
A E C 4:25
B C E 1
B E C 2:15
B C E 3
B E C 4:20
I want to retrieve data from this table in the following format:
Sub From To Turnaround time
A E C 1:30
A E C 1:25
B E C 1:15
B E C 1:20
It means that I want to have every second row and value in Turnaround Time is the difference between the time in every 2nd row and the time in every 1st row.
I am not able to think of a way to design such a SQL query. Any help?
Last edited by mahajanakhil198; 04-19-2011 at 08:29 AM.
Could you please post desc of affected table?
How do you ensure rows are retrived in the proper order? is it the first character plus the time?
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Yes, your understanding is right - Sub then Time. I don't have description yet, the data will be loaded from excel to Oracle and then I will have to work on it.
Originally Posted by PAVB
Has it gone unnoticed or can it not be done?
You've been waiting for free help for the past 14 days? It must not be that important. What have *you* tried on your own so far?
Exactly, it is not important.
Originally Posted by stecal
What I have done is that I can arrange the rows in sorted order so that they can be read with ease. But this is not acceptable. I wonder what's the logic using SQL to merge the data in two rows and convert the data which is in one column in two adjacent rows into two columns in one row for display purpose.
I will appreciate if you can write that logic only rather than letting me know the SQL.
Click Here to Expand Forum to Full Width