Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query to create a report (Oracle 9.2.0.8)
SQL Query to create a report [message #308055] Thu, 20 March 2008 21:31 Go to next message
decci_7
Messages: 68
Registered: March 2006
Member
I have a table which is like below:

Rseq	REQUEST		Month	STARTDATE		SubRequest
---------------------------------------------------------------------
13775	Request XYZ	Feb	03/20/2008 05:40:44	SubRequest2
13775	Request XYZ	Feb	03/20/2008 00:28:01	SubRequest1
13775	Request XYZ	Jan	03/20/2008 00:11:01	SubRequest2
13775	Request XYZ	Jan	03/19/2008 20:11:48	SubRequest1
13766	Request XYZ	Mar	03/19/2008 10:09:02	SubRequest2
13766	Request XYZ	Mar	03/19/2008 07:51:52	SubRequest1
13753	Request MNO	Feb	03/19/2008 02:11:24	SubRequest2
13753	Request MNO	Feb	03/18/2008 22:35:44	SubRequest1
13753	Request MNO	Jan	03/18/2008 22:23:03	SubRequest2
13753	Request MNO	Jan	03/18/2008 18:04:23	SubRequest1
13731	Request DEF	Mar	03/16/2008 21:06:13	SubRequest2
13731	Request DEF	Mar	03/16/2008 18:02:34	SubRequest1
13721	Request ABC	Feb	03/15/2008 23:37:15	SubRequest2
13721	Request ABC	Feb	03/15/2008 19:43:04	SubRequest1
13720	Request ABC	Jan	03/15/2008 19:28:54	SubRequest2
13720	Request ABC	Jan	03/15/2008 15:38:20	SubRequest1

This is how the data goes into the above table.

Let us take an example from above table to understand it better.

Rseq	REQUEST		Month	STARTDATE		SubRequest
---------------------------------------------------------------------
13766	Request XYZ	Mar	03/19/2008 10:09:02	SubRequest2
13766	Request XYZ	Mar	03/19/2008 07:51:52	SubRequest1

Each Request has 5 Subrequests under it including the SubRequest1 and SubRequest2.
SubRequest1 represents the first request that executes under the Main Request (Request XYZ) and captures the START time of the Main Request.
SubRequest2 represents the last request that executes under the Main Request (Request XYZ) and captures the END time of the Main Request.

So Basically when we run a Main Request for a Particular Month, it creates 2 entries in this table.
One for the SubRequest1 to capture start time and another for SubRequest2 to capture the end time.

Now My Problem is that I want to create a report out of this table which should combine these 2 rows into 1 row and desired outbut as below:

Rseq	REQUEST		Month	STARTDATE		ENDDATE  		STARTREQUEST ENDREQUEST
----------------------------------------------------------------------------------------------------------
13775	Request XYZ	Feb	03/20/2008 00:28:01	03/20/2008 05:40:44	SubRequest1   SubRequest2
13775	Request XYZ	Jan	03/19/2008 20:11:48	03/20/2008 00:11:01	SubRequest1   SubRequest2
13766	Request XYZ	Mar	03/19/2008 07:51:52	03/19/2008 10:09:02	SubRequest1   SubRequest2

Is it possible to achive such a report using SQL? If Yes, then How?

Any help is much appreciated.

Thanks,

[Updated on: Thu, 20 March 2008 21:35]

Report message to a moderator

Re: SQL Query to create a report [message #308078 is a reply to message #308055] Fri, 21 March 2008 01:27 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
This should get you close
SELECT rseq
      ,request
      ,month
      ,MIN(startdate) startdate
      ,MAX(startdate) enddate
      ,MIN(subrequest) startrequest
      ,MAX(subrequest) endrequest
FROM t
GROUP BY rseq, request, month
Re: SQL Query to create a report [message #308116 is a reply to message #308055] Fri, 21 March 2008 04:53 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
And maybe you should read this
Re: SQL Query to create a report [message #308159 is a reply to message #308116] Fri, 21 March 2008 12:13 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Thanks !!

I used the Pivots and decode function to solve it. Actually I solved it earlier when one of the senior member pointed out to use pivots. My Bad .. I forgot to post a reply on this.

Thanks again.
Re: SQL Query to create a report [message #308199 is a reply to message #308159] Fri, 21 March 2008 23:39 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
You can use this query!!

 
select rseq,request,month,min(startdate) STRTDT,max(startdate) ENDDT,case when min(subrequest)='SubRequest1' then min(subrequest) end ,
case when max(subrequest)='SubRequest2' then max(subrequest) end  from Request group by rseq,REQUEST,MONTH
Re: SQL Query to create a report [message #308293 is a reply to message #308199] Sun, 23 March 2008 17:51 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
12 hours after the issue is resolved and wrong hardly seems like it merits two exclamation points.
Previous Topic: how to seperate a sentance into array by a seperator ';' using pl/sql ?
Next Topic: To see inserted data at every step in a pipeline report
Goto Forum:
  


Current Time: Sun Dec 04 10:26:13 CST 2016

Total time taken to generate the page: 0.12196 seconds