SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with complex(?) query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

freelancer
Starting Member

2 Posts

Posted - 12/14/2013 :  17:55:33  Show Profile  Reply with Quote
Hello I've been stuck with one SQL query for my assignment and was hoping for some help.

I need to get the Project ID for the best executed project -the project where (VERY_GOOD record count + GOOD record count) - (VERY_BAD record count + BAD record count) is greatest

My schema and test records in database (HSQLDB)

CREATE TABLE
PROJECT
(
ID IDENTITY NOT NULL PRIMARY KEY,
PROJECT_NAME VARCHAR(255) NOT NULL
);

CREATE TABLE
RECORD
(
ID IDENTITY NOT NULL PRIMARY KEY,
RESULT VARCHAR(255) NOT NULL,
);

CREATE TABLE
RECORD_PROJECT
(
PROJECT_ID INTEGER NOT NULL,
RECORD_ID INTEGER NOT NULL,
PRIMARY KEY(PROJECT_ID, RECORD_ID),
FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(ID),
FOREIGN KEY (RECORD_ID) REFERENCES RECORD(ID)
);
And test data:

INSERT INTO PROJECT (PROJECT_NAME) VALUES ('Bake a cake');
INSERT INTO PROJECT (PROJECT_NAME) VALUES ('Clean the house');

INSERT INTO RECORD (RESULT) VALUES ('GOOD');
INSERT INTO RECORD (RESULT) VALUES ('VERY_GOOD');
INSERT INTO RECORD (RESULT) VALUES ('VERY_GOOD');
INSERT INTO RECORD (RESULT) VALUES ('BAD');
INSERT INTO RECORD (RESULT) VALUES ('VERY_BAD');

INSERT INTO RECORD_PROJECT (PROJECT_ID, RECORD_ID) VALUES (0,0);
INSERT INTO RECORD_PROJECT (PROJECT_ID, RECORD_ID) VALUES (1,1);
INSERT INTO RECORD_PROJECT (PROJECT_ID, RECORD_ID) VALUES (1,2);
INSERT INTO RECORD_PROJECT (PROJECT_ID, RECORD_ID) VALUES (0,3);
INSERT INTO RECORD_PROJECT (PROJECT_ID, RECORD_ID) VALUES (1,4);
(I removed unrelated fields from tables)

So with this data I have 3 good records and 2 bad, I would need to get the project which has the highest 'rating', which according to this right now would be Clean the house with 3 good ratings over 2 negative for other project.

Maybe someone would figure this out, thanks!

bitsmed
Constraint Violating Yak Guru

406 Posts

Posted - 12/14/2013 :  22:52:40  Show Profile  Reply with Quote
Maybe this:

select a.project_name
      ,sum(case c.result
              when 'GOOD'      then  1
              when 'VERY_GOOD' then  1
              when 'BAD'       then -1
              when 'VERY_BAD'  then -1
              else                   0
           end
          ) as score
  from project as a
       left outer join record_project as b
                    on b.project_id=a.id
       left outer join record as c
                    on c.id=b.record_id
 group by a.project_name
Go to Top of Page

freelancer
Starting Member

2 Posts

Posted - 12/15/2013 :  03:25:30  Show Profile  Reply with Quote
Wow that seems to be the right way to go, but here's a problem. I get same summed result for all projects, would need to be separate for every one and then I could select the one with highest score. Anyway to get it display like that?
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

406 Posts

Posted - 12/15/2013 :  08:05:08  Show Profile  Reply with Quote
quote:
Originally posted by freelancer

Wow that seems to be the right way to go, but here's a problem. I get same summed result for all projects, would need to be separate for every one and then I could select the one with highest score. Anyway to get it display like that?


Thats odd - I get the correct result (after correcting the record project values - see below).
Bake a cake = 0
Clean the house = 1

Having used your create statements, I get error on project and record table, as column id is missing a field type. Also I get constraint error, using your insert for the record_project table - seems like your sample data assumes the id fields start with 0.

This leads me to question if you are using mssql engine?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000