Re: Query help needed please.

a.)

select DISTINCT B.Name
  from HIRECONTRACTS A,

       CLIENTS B
 where A.ClientId = B.ClientId;

b.)

select DISTINCT B.Name
  from HIRECONTRACTS A,

       CLIENTS B
 where A.ClientId = B.ClientId
   and A.HireStart BETWEEN ADD MONTHS(TRUNC(SYSDATE,'MONTH'),-1) and LAST DAY(ADD MONTHS(TRUNC(SYSDATE,'MONTH'),-1)) ; c.)

select DISTINCT B.Name
  from HIRECONTRACTS A,

       CLIENTS B
 where A.ClientId = B.ClientId
   and A.HireStart BETWEEN TRUNC(SYSDATE,'MONTH') and LAST DAY(SYSDATE) ;

"Steve" <hunt.s_at_blueyonder.co.uk> wrote in message news:mSSu9.36706$862.35865_at_news-binary.blueyonder.co.uk...
> I am attempting to find which Customers (Names) have commenced a hire
> contract as follows:-
>
> a) Ever.
> b) Anytime last month and,
> c) Anytime this month.
>
> My tables are as follows:
>
> ClientId Name Parent
 ClientType
> -------- ------------------------------ ------ ----------
> 1500 Sydney Symphony Orchestra T
> 1501 Melbourne symphony Orchestra T
> 1502 Daves Didjeridoo Duo P
> 1503 Southern Cross String Quartet 1501 P
> 1504 Barcaldine Barbershop Quartet 1501 P
> 1505 Blue Room Quintet 1500 P
> 1506 Wallerawang Whistlers P
>
> ContractId ClientId HireStart HireReturn
> ---------- -------- --------- ----------
> MU15 1505 10-AUG-02 16-SEP-02
> MU16 1504 21-AUG-02 27-SEP-02
> MU17 1503 04-SEP-02 30-SEP-02
> MU18 1501 01-OCT-02 15-OCT-02
> MU19 1500 07-OCT-02 31-OCT-02
> MU20 1506 18-NOV-02 30-NOV-02
> MU21 1503 20-NOV-02 01-DEC-02
> MU22 1505 15-NOV-02 05-DEC-02
> MU23 1506 01-DEC-02 14-DEC-02
> MU24 1500 02-DEC-02 21-DEC-02
> MU25 1503 16-DEC-02 02-JAN-03
> MU26 1501 17-DEC-02 10-JAN-03
> MU27 1503 03-JAN-03 31-JAN-03
> MU28 1504 10-JAN-03 24-JAN-03
>
> I have tried the query below but it lists all matches. Is there a way
 of
> avoiding displaying duplicates?
>
> Select Clients.Name,
> HireContracts.ClientId
> from Clients, HireContracts
> where Clients.ClientId = HireContracts.ClientId;
>
> Any help with b) and c) very much welcome.
>
> Many thanks in advance.
>
> Mailto: hunt.s_at_blueyonder.co.uk
>
>

 -- Received on Wed Oct 30 2002 - 06:31:27 CET