Home » SQL & PL/SQL » SQL & PL/SQL » Generate Report
Generate Report [message #214953] Thu, 18 January 2007 13:45 Go to next message
jvenky
Messages: 3
Registered: January 2007
Location: WA
Junior Member
CUSTOMER_DATA
PHONE_NO ACTIVE_DATE INACTIVE_DATE
1231234123 10/9/2006 13:32 10/11/2006 23:22
1231234124 10/9/2006 19:35 10/16/2006 22:06
1231234126 10/1/2006 13:10 10/1/2006 13:12

ACTIVE_CUST_DATA
PHONE_NO ACTIVE_DATE
1231234123 10/9/2006 13:32
1231234123 10/12/2006 1:32
1231234123 10/13/2006 1:32
1231234124 10/9/2006 19:35
1231234124 10/19/2006 19:35
1231234125 10/1/2006 13:10
1231234126 10/1/2006 13:10

INACTIVE_CUST_DATA
PHONE_NO INACTIVE_DATE
1231234123 10/11/2006 23:22
1231234123 10/12/2006 13:32
1231234124 10/16/2006 22:06
1231234126 10/1/2006 13:12

Based on the above three tables I need to generate a report as shown below
OUTPUT:
PHONE_NO ACTIVE_DATE INACTIVE_DATE
1231234123 10/9/2006 13:32 10/11/2006 23:22
1231234123 10/12/2006 1:32 10/12/2006 13:32
1231234123 10/13/2006 1:32
1231234124 10/9/2006 19:35 10/16/2006 22:06
1231234124 10/19/2006 19:35
1231234125 10/1/2006 13:10
1231234126 10/1/2006 13:10 10/1/2006 13:12

Re: Generate Report [message #214954 is a reply to message #214953] Thu, 18 January 2007 13:50 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Based on the above three tables I need to generate a report as shown below
Proceed to create the report.
The homework tutor is down the hall, the 3rd door on the left.
Re: Generate Report [message #214962 is a reply to message #214954] Thu, 18 January 2007 14:57 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
anacedent
... the 3rd door on the left

It is the main reason for not having the report done - tutor has moved. Last week, if I remember well, he was in the 2nd room.

Just kidding! So, what did you do with the query so far, jvenky?
Re: Generate Report [message #214968 is a reply to message #214962] Thu, 18 January 2007 15:42 Go to previous messageGo to next message
jvenky
Messages: 3
Registered: January 2007
Location: WA
Junior Member
SELECT act.phone_no,act.active_date
FROM (
SELECT a.phone_no,MAX(a.active_date) active_date
FROM customer_data a
WHERE NOT EXISTS(SELECT phone_no
FROM customer_data
WHERE inactive_date IS NULL
AND customer_data.phone_no=a.phone_no)
AND a.inactive_date <= (SELECT MAX(b.active_date)
FROM active_cust_data b
WHERE b.phone_no = a.phone_no)
AND a.active_date < (SELECT MAX(c.active_date)
FROM active_cust_data c
WHERE c.phone_no = a.phone_no)
GROUP BY a.phone_no) missing_rec, active_cust_data act
WHERE act.phone_no = missing_rec.phone_no
AND act.active_date > missing_rec.active_date;
Re: Generate Report [message #214971 is a reply to message #214968] Thu, 18 January 2007 15:48 Go to previous message
jvenky
Messages: 3
Registered: January 2007
Location: WA
Junior Member
select * from
(select phone_no,max(inactive_date)
inactive_date,max(active_date) active_date
from CUSTOMER_DATA group by msisdn) a,
(select c.phone_no,max(c.inactive_date) inactive_date,
max(d.active_date) active_date
from inactive_cust_data c,active_cust_data d
where c.phone_no = d.phone_no group by c.phone_no) b
where a.phone_no = b.phone_no
and ((a.inactive_date > a.active_date
and a.inactive_date < b.active_date) or (a.active_date > a.inactive_date and a.active_date < b.inactive_date))


Previous Topic: SET DEFAULT in a trigger
Next Topic: Select Query for Column Name of Largest Value
Goto Forum:
  


Current Time: Sun Dec 04 19:09:02 CST 2016

Total time taken to generate the page: 0.10978 seconds