How to list the data when an account = 0 for the last 3 years?

advertisements

I am trying to produce a list of branches that haven't made any sales in the last 3 years. I have been able to produce a list of sales that are older than 3 years but not with the added condition of 0 sales in the 3 years prior.

My task is as follows: List all of the branches that have not rented out any tools for more than 3 years.

I think that I have to do a nested subquery but I cannot work out what should go where. Here are the two relevant tables with their descriptions and data values. The only value that should be returned is that for branch 70.

SQL> desc toolorder
Name                           Null?    Type
--------------------------------------------------------- -------- ---
ORDERID                        NOT NULL VARCHAR2(6)
CUST                           NOT NULL VARCHAR2(6)
SNAME                          NOT NULL VARCHAR2(20)
BRANCHID                       NOT NULL VARCHAR2(6)
TYPE                           NOT NULL    VARCHAR2(15)
TOOLID                         NOT NULL VARCHAR2(6)
DATEOUT                        NOT NULL DATE
DUEDATE                        NOT NULL DATE

SQL> desc branch
Name                           Null?    Type
--------------------------------------------------------------
BRANCHID                          NOT NULL VARCHAR2(6)
BNAME                             NOT NULL VARCHAR2(15)
ADDRESS                           NOT NULL VARCHAR2(25)
TELEPHONE                                  VARCHAR2(11)
MANAGERID                                  VARCHAR2(6)

SQL> select * from toolorder;

ORDERI CUSTOM SNAME                BRANCH TYPE            TOOLID DATEOUT   DUEDATE
------ ------ -------------------- ------ --------------- ------ --------- ---------
000001 000100 smith                10     Adhesive        00042  20-OCT-13 27-NOV-12
000002 000101 jones                10     Guage           00050  13-OCT-12 30-OCT-12
000003 000103 may                  10     Generic         00023  21-NOV-12 28-NOV-12
000004 000100 smith                10     Generic         00023  19-NOV-13 28-NOV-13
000005 000104 circus               10     Generic         00023  05-JAN-09 28-JAN-09
000006 000106 hanks                10     Wood            00062  11-APR-10 01-MAY-10
000007 000102 bond                 20     Cutting         00073  13-DEC-11 27-DEC-11
000008 000102 bond                 20     Guage           00053  13-DEC-11 27-DEC-11
000009 000104 circus               30     Generic         00025  13-DEC-06 28-DEC-06
000010 000104 circus               30     Brickwork       00035  13-DEC-06 28-DEC-06
000011 000105 harris               30     Cutting         00075  13-OCT-13 25-OCT-13
000012 000105 harris               40     Brickwork       00036  13-DEC-11 27-DEC-11
000013 000105 harris               40     Generic         00027  13-DEC-11 27-DEC-11
000014 000105 harris               40     Electric        00006  13-DEC-11 27-DEC-11
000015 000106 hanks                40     Adhesive        00046  13-MAY-11 27-MAY-11
000016 000107 head                 50     Adhesive        00047  13-MAR-13 27-MAR-13
000017 000107 head                 50     Wood            00018  13-MAR-13 27-MAR-13
000018 000101 jones                50     Guage           00055  06-JAN-13 20-JAN-13
000019 000103 may                  60     Brickwork       00039  06-APR-13 20-APR-13
000020 000101 jones                60     Cutting         00080  24-DEC-12 07-JAN-13
000021 000101 circus               70     Cutting         00081  13-AUG-08 27-AUG-08                                                        

21 rows selected.

SQL> select * from branch;

BRANCH BNAME           ADDRESS                   TELEPHONE   MANAGE
------ --------------- ------------------------- ----------- ------
10     Oxford          18 Oxford Estate          08456325312
20     Greenwood       21 Greenwood Lane         02380282185
30     Weston          36 Weston Road            02380282635
40     Highstreet      12-15 Stafford Highstreet 02380865963
50     Meadow          16 The Meadow Yard        07974296353
60     Port Down       168 Port Down Av          08953164826
70     Red Rd          12-15 Red Road            07948247384                                                                                

7 rows selected.

Now, running the following query returns the orders that are 3 years old. I need to adjust it (I think) using a nested subqueries, so that it checks there are no sales in the 3 years, but cannot work out how.

SQL> select count(toolorder.orderid) as rentalcount, branch.branchid, branch.bname,
branch.address from toolorder left outer join branch on toolorder.branchid =
branch.branchid where MONTHS_BETWEEN(sysdate, dateout) > 36 group by branch.branchid,
branch.bname, branch.address order by 1 desc;

RENTALCOUNT BRANCH BNAME           ADDRESS
----------- ------ --------------- -------------------------
      2 30     Weston          36 Weston Road
      2 10     Oxford          18 Oxford Estate
      1 70     Red Rd          12-15 Red Road


The easiest way to do this is to get the maximum dateout for each branchid and check that it is more than 36 months in the bast:

select b.*
from branch b join
     (select branchid, max(dateout) as maxd
      from toolorder
      group by branchid
     ) tob
     on b.branchid = tob.branchid
where MONTHS_BETWEEN(sysdate, tob.maxd) > 36;