hi,
I have a small issue.
We have a contract table that contains live ,terminated contracts and customer table has got customer information. My requirement is I have to display the
customer details which has the terminated contracts with the below condition.
If the customer has got both live contract and terminated contract then don't display it in the terminated contract list.
note : one customer can have more than one live or terminated contracts.
contract table
=========
contract_number,
customer_id
start_date
status
customer_table
=======
customer_id
first_name,
last_name
I am really not getting how to create it in report studio.
I have created one query called 'LIVE' for the live contracts using status=live and created one more query called 'TERMINATED' for the terminated contracts
using the status=terminated
I can't use the union,intersect and excepts to get the details.
I have joined both of them like this live.customer_id <> terminated.customer_id in the report studio to get all the customer details from the terminated
query.
It seems the query looks expensive.
Here is sql query.There are small mistakes in the query.please ignore them.
select terminated.customer_id,terminated.customer_name from ((select * from customer where status='live') terminated
(select * from customer where status='terminated') live
)
live.customer_id <> terminate.customer_id
I am expecting the below SQL in the report to achieve the terminated contract list
select t.customer_id,t.customer_name from customer t,contract c where c.staus='terminated' and c.customer_id=t.customer_id and t.customer_id not in (
select l.customer_id from customer l,contract c1 where c1.status='live' and l.customer_id=c1.customer_id)
Please provide your suggestions
Thanks,
mano
Quote
I can't use the union,intersect and excepts to get the details.
Why not?
Pretty simple exercise if you can use them
select t.customer_id,t.customer_name from customer t,contract c where c.staus='terminated' and c.customer_id=t.customer_id and t.customer_id not in (
select l.customer_id from customer l,contract c1 where c1.status='live' and l.customer_id=c1.customer_id)
Should this not suffice:
select t.customer_id,t.customer_name from customer t inner join contract c on
c.customer_id=t.customer_id where c.staus='terminated' and
t.customer_id not in ( select c1.customer_id from contract c1 where c1.status='live')
Thank you :) . Oh. it is very simple .I don't know why it has not come to my mind. I just need to use this simple filter Terminated Query.customer_id <> Live Query.Customer_id in the Terminated Query.