Forum Stats

  • 3,851,970 Users
  • 2,264,055 Discussions
  • 7,904,920 Comments

Discussions

simple select?

396476
396476 Member Posts: 6
edited Aug 29, 2007 11:22AM in SQL & PL/SQL
Hi,

I've been attempting to select (for example) the people of which all projects are older than 1 year and of which all projects also have an inactive state (state 3 and 4). I'm pretty sure this can be done in one select, but how?????? Can someone assist?

CREATE TABLE PROJECT (
id_project NUMBER(1),
person VARCHAR2(10),
id_state NUMBER(1),
last_update DATE
);

INSERT INTO PROJECT VALUES (1, 'Joe', 3, TO_DATE('08-08-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (2, 'Mark', 2, TO_DATE('07-05-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (3, 'Mark', 3, TO_DATE('06-27-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (4, 'Mark', 4, TO_DATE('03-14-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (5, 'Carl', 3, TO_DATE('12-08-2004','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (6, 'Carl', 4, TO_DATE('01-22-2005','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (7, 'Sam', 2, TO_DATE('06-15-2006','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (8, 'Paul', 1, TO_DATE('07-30-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (9, 'Paul', 2, TO_DATE('06-02-2006','MM-DD-YYYY'));

So in this case the select should only return the name Carl as both his projects are inactive and both are older than one year.

All I got this far is a statement which retrieves the people of which all projects are older than one year. How do I extend this so it will limit the result to the people of which all projects are also inactive (state 3 and 4)? Being only Carl, as Sams project is still active.

SQL> SELECT person, COUNT(id_project), MAX(last_update)
2 FROM PROJECT
3 GROUP BY person
4 HAVING MAX(last_update) < ADD_MONTHS(SYSDATE,-12)
5 ORDER BY 1;

PERSON COUNT(ID_PROJECT) MAX(LAST_
---------- --------------------------------- -----------------
Carl 2 22-JAN-05
Sam 1 15-JUN-06

Comments

  • 441344
    441344 Member Posts: 53
    Try this

    SELECT *
    FROM project
    WHERE
    last_update < sysdate - 365
    AND id_state IN (3, 4)

    ID_PROJECT PERSON ID_STATE LAST_UPDATE
    ---------- ---------- ---------- --------------------
    5 Carl 3 08-DEC-2004
    6 Carl 4 22-JAN-2005

    Regards,
    Muthu
  • jeneesh
    jeneesh Member Posts: 7,168
    One method..
    SQL> SELECT person, COUNT(id_project), MAX(last_update)
    2 FROM PROJECT p
    3 where not exists(select null
    4 from project
    5 where id_state not in (3,4)
    6 and person = p.person)
    7 GROUP BY person
    8 HAVING MAX(last_update) < ADD_MONTHS(SYSDATE,-12)
    9 ORDER BY 1;

    PERSON COUNT(ID_PROJECT) MAX(LAST_
    ---------- ----------------- ---------
    Carl 2 22-JAN-05
  • 438877
    438877 Member Posts: 1,849 Green Ribbon
    edited Aug 29, 2007 6:32AM
    SQL> SELECT person, COUNT(id_project), MAX(last_update)
    2 FROM PROJECT
    3 GROUP BY person
    4 HAVING MAX(last_update) < ADD_MONTHS(trunc(SYSDATE),-12)
    5 AND COUNT(id_project) = COUNT(CASE WHEN id_state IN (3,4) THEN id_project END)
    6 ORDER BY 1;

    PERSON COUNT(ID_PROJECT) MAX(LAST_
    ---------- ----------------- ---------
    Carl 2 22-JAN-05
    Rgds.
  • RAMJANE
    RAMJANE Member Posts: 317 Blue Ribbon
    Hi,

    I've been attempting to select (for example) the
    people of which all projects are older than 1 year
    and of which all projects also have an inactive state
    (state 3 and 4). I'm pretty sure this can be done in
    one select, but how?????? Can someone assist?

    CREATE TABLE PROJECT (
    id_project NUMBER(1),
    person VARCHAR2(10),
    id_state NUMBER(1),
    last_update DATE
    );

    INSERT INTO PROJECT VALUES (1, 'Joe', 3,
    TO_DATE('08-08-2007','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (2, 'Mark', 2,
    TO_DATE('07-05-2007','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (3, 'Mark', 3,
    TO_DATE('06-27-2007','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (4, 'Mark', 4,
    TO_DATE('03-14-2007','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (5, 'Carl', 3,
    TO_DATE('12-08-2004','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (6, 'Carl', 4,
    TO_DATE('01-22-2005','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (7, 'Sam', 2,
    TO_DATE('06-15-2006','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (8, 'Paul', 1,
    TO_DATE('07-30-2007','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (9, 'Paul', 2,
    TO_DATE('06-02-2006','MM-DD-YYYY'));

    So in this case the select should only return the
    name Carl as both his projects are inactive and both
    are older than one year.

    All I got this far is a statement which retrieves the
    people of which all projects are older than one year.
    How do I extend this so it will limit the result to
    the people of which all projects are also inactive
    (state 3 and 4)? Being only Carl, as Sams project is
    still active.

    SQL> SELECT person, COUNT(id_project),
    MAX(last_update)
    2 FROM PROJECT
    3 GROUP BY person
    4 HAVING MAX(last_update) <
    ADD_MONTHS(SYSDATE,-12)
    5 ORDER BY 1;
    PERSON COUNT(ID_PROJECT) MAX(LAST_
    ---------- ---------------------------------
    -----------------
    arl 2
    22-JAN-05
    15-JUN-06
    Add condition for active or inactive states in your query then it will give you your desired result. You had done this much work so i m leaving this up to you how and where you put this condition.
  • 396476
    396476 Member Posts: 6
    edited Aug 29, 2007 6:45AM
    That works for the sample table filling above, but not for all possible scenarios. For instance, when I set one of Marks inactive projects to a date older than a year, than that record is also retrieved while Mark still has other projects not older than a year of which one is even still active.

    new test set:
    INSERT INTO PROJECT VALUES (1, 'Joe', 3, TO_DATE('08-08-2007','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (2, 'Mark', 2, TO_DATE('07-05-2007','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (3, 'Mark', 3, TO_DATE('06-27-2007','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (4, 'Mark', 4, TO_DATE('03-14-2006','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (5, 'Carl', 3, TO_DATE('12-08-2004','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (6, 'Carl', 4, TO_DATE('01-22-2005','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (7, 'Sam', 2, TO_DATE('06-15-2006','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (8, 'Paul', 1, TO_DATE('07-30-2007','MM-DD-YYYY'));
    INSERT INTO PROJECT VALUES (9, 'Paul', 2, TO_DATE('06-02-2006','MM-DD-YYYY'));

    SQL> SELECT *
    2 FROM project
    3 WHERE
    4 last_update < sysdate - 365
    5 AND id_state IN (3, 4);

    ID_PROJECT PERSON ID_STATE LAST_UPDA
    ---------- ---------- ---------- ---------
    4 Mark 4 14-MAR-06
    5 Carl 3 08-DEC-04
    6 Carl 4 22-JAN-05

    Carl should be the only one selected as he is the only one with all projects inactive and all projects older than one year.

    Edit: Man, you people are fast, this was in reply to the first answer. Give me time to read the rest ;-)

    Message was edited by:
    gebruiker

    Message was edited by:
    gebruiker
  • 396476
    396476 Member Posts: 6
    I understand it's just about adding one more condition. The whole problem for me was were to put it......
  • 396476
    396476 Member Posts: 6
    jeneesh and dnikiforov, thank you.
    I'll study both solutions.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Aug 29, 2007 11:22AM
    Is this right?
    SQL> select id_project,person,id_state
    2 from (select id_project,person,id_state,
    3 max(last_update) over(partition by person) as maxDate,
    4 min(case when id_state in(3,4) then 1 else 0 end) over(partition by person)as "3-4Only"
    5 from PROJECT)
    6 where maxDate < add_months(sysdate,-12)
    7 and "3-4Only" = 1;
    [pre]
    ID_PROJECT PERSON ID_STATE
    ---------- ------ --------
    5 Carl 3
    6 Carl 4


    http://en.wikipedia.org/wiki/First-order_logic
    for instance
    "max(case when P(X) then 1 else 0 end) = 1"  is for some X:P(X)
    "min(case when P(X) then 1 else 0 end) = 1" is for all X:P(X)
    "max(case when P(X) then 0 else 1 end) = 1" is for some X:not(P(X))
    "min(case when P(X) then 0 else 1 end) = 1" is for all X:not(P(X))
This discussion has been closed.