query help 1

[FALCONSEYE]

i have the following

CREATE TABLE R_TEST
(
   PROJECT_ID  NUMBER,
   VERSION     NUMBER,
   READY_DATE  DATE,
   ESTATE_NO   VARCHAR2(1 BYTE)
  )
  TABLESPACE vvvvvvvvv
  PCTUSED    0
  PCTFREE    10
  INITRANS   1
  MAXTRANS   255
  STORAGE    (
        INITIAL          64K
        NEXT             1M
        MINEXTENTS       1
        MAXEXTENTS       UNLIMITED
        PCTINCREASE      0
        BUFFER_POOL      DEFAULT
       )
 LOGGING 
 NOCOMPRESS 
 NOCACHE
 NOPARALLEL
 MONITORING;
 SET DEFINE OFF;

 Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
    (1345, 1, NULL, 'a');
 Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
    (1345, 2, NULL, 'a');
 Insert into R_TEST
   (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
   (1345, 3, TO_DATE('07/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'a');
 Insert into R_TEST
   (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
   (1345, 4, TO_DATE('07/29/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'a');
 Insert into R_TEST
   (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
  Values
  (1059, 1, NULL, 'b');
 Insert into R_TEST
   (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
  Values
   (1059, 2, TO_DATE('06/27/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'b');
  Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
    (2326, 1, NULL, 'b');
 Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
  Values
    (2326, 2, NULL, 'b');
 Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
    (2326, 3, TO_DATE('08/29/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'b');
 Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
 Values
    (998, 1, NULL, 'c');
  Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
  Values
   (998, 2, TO_DATE('07/27/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'c');
  Insert into R_TEST
    (PROJECT_ID, VERSION, READY_DATE, ESTATE_NO)
  Values
   (998, 1, NULL, 'c');
  COMMIT;

I am trying to get how many active vs completed projects exist per estate_no. According to above data: I should have

one completed project on 7/27/2011 and one active project for estate_no C. two completed projects on 8/29/2011 and 6/27/2011 for estate_no B. one completed project on 7/29/2011 for estate_no A.

the problem i am having is that estate_no A has two dates for the same project_id with 4 different versions. They marked it ready but realized that it wasn't actually ready, did some more work and then marked it ready one more time.

any help will be appreciated.

ColdFusion Ninja for hire.

 

[FALCONSEYE]

 select  project_id, ready_date, version
  from r_test r
  where r.estate_no =  'c'
  and ready_date is not null
  and r.version = (select max(r2.version) from r_test r2 where r.project_id = r2.project_id )
  group by project_id, version, estate_no, ready_date

works for the ones that are completed ( has a ready date ) , i will use another select with union to get the ones that are in progress. thanks

ColdFusion Ninja for hire.

 

[Thargy]

Falcon,

a purple pointy thingy for posting a question with a create table statement, and insert statements giving sample data.

This is unheard of.

Are you feeling ill?

Regards

T

 

[FALCONSEYE]

ColdFusion Ninja for hire.