Help with complex(?) query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with complex(?) query

Author

Topic

freelancer
Starting Member

2 Posts

Posted - 2013-12-14 : 17:55:33

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
Aged Yak Warrior

545 Posts

Posted - 2013-12-14 : 22:52:40

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

freelancer
Starting Member

2 Posts

Posted - 2013-12-15 : 03:25:30

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?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-12-15 : 08:05:08

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources