need help in creating report in Report studio

[hari5]

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

[blom0344]

I can't use the union,intersect and excepts to get the details.

Why not?

Pretty simple exercise if you can use them

Code Select Expand


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:

Code Select Expand



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')



[hari5]

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.