Re: Query help required please.

From: Dirk Tschentscher <dirk.tschentscher_at_volkswagen.de>
Date: Wed, 30 Oct 2002 11:57:41 +0100
Message-ID: <apofof$2oe5@doiweb4.volkswagen.de>

Hi Steve,
when I understand you right then
b) and to_char(hirestart,'yyyymm') =

             (select
decode(to_char(sysdate,'mm'),'01',to_char(sysdate,'yyyy') -1 ||'12', to_char(sysdate,'yyyymm')-1)

                from dual)

c) and to_char(hirestart,'yyyymm') = to_char(sysdate,'yyyymm')

  maybe not the best, but it should work for smaller tables... or you add an indexed column with only the 'yyyymm' of the hirestart column

I had a similar problem some time ago, has any "guru" a good idea ??

regards

    Dirk

"Steve" <hunt.s_at_blueyonder.co.uk> schrieb im Newsbeitrag news:0cVu9.37062$862.19867_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 - 04:57:41 CST