Home » SQL & PL/SQL » SQL & PL/SQL » Unique SQL Requirement -- Urgent
Unique SQL Requirement -- Urgent [message #192925] Thu, 14 September 2006 04:37 Go to next message
satyauvce
Messages: 6
Registered: August 2005
Junior Member
Hi All,
The requirement may take some time to explain and understood.
It is interesting as well.
Here it is

Lets say there are 5 columns in a table test_table.
cal_date date,
emp_no varchar2,
time_in varchar2, (24 hr format) 0800 means 8am 1300 means 1 pm.
time_out varchar2, (24 hr format)
Status varchar2 (Can be either Available or Unavailable)

Now say there are following records as follows

cal_date emp_no time_in time_out status
01-Sep-06 111 0800 1000 Unavailable
01-Sep-06 111 1000 1100 Unavailable
01-Sep-06 111 1100 1200 Available
01-Sep-06 111 1200 1300 Unavailable
02-Sep-06 111 0800 1000 Unavailable
02-Sep-06 111 1000 1100 Available
02-Sep-06 111 1100 1200 Unavailable
02-Sep-06 111 1200 1300 Unavailable

I want the all the continous Status for a day employee in a single row.
The output of the query should look like this
01-Sep-06 111 0800 1100 Unavailable
01-Sep-06 111 1100 1200 Available
01-Sep-06 111 1200 1300 Unavailable
02-Sep-06 111 0800 1000 Unavailable
02-Sep-06 111 1000 1100 Available
02-Sep-06 111 1100 1300 Unvailable

First two rows are combined together since the time was continous 0800 - 1000 and 1000 - 1100 is Unavailable.
Similarly last two rows are combined together.

Using group by will not serve the purpose here.

Thanks in Advance.
Satya.
Re: Unique SQL Requirement -- Urgent [message #192935 is a reply to message #192925] Thu, 14 September 2006 05:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have a look here
A very similar problem was solved.
Re: Unique SQL Requirement -- Urgent [message #192985 is a reply to message #192935] Thu, 14 September 2006 08:45 Go to previous message
satyauvce
Messages: 6
Registered: August 2005
Junior Member
Hi
Thanks a million.
I think the same approach will work when we want to have continous range over two columns.(Just as we had for column "id" in the example).
Frankly, I did not anticipate a reply.
And this early was such a delight.

Thanks again.
Satya.
Previous Topic: Materialized Views and Fast Refresh
Next Topic: need help in NOT IN subquery
Goto Forum:
  


Current Time: Sat Dec 10 22:17:16 CST 2016

Total time taken to generate the page: 0.09353 seconds