Need Help Writing SQL Query in Postgresql

Question

I've been trying to write this query but can't seem to get it right for some reason or another..

What I need to do is:

Change the status of a question to 'closed' if there has not been an update associated with this question inserted into the qUpdateTable in the last 24 hours.

I only want it to be closed if a staff member has replied to it at least once.

You can determine if a staff member or a user has replied to the question by checking the qUpdateTable and seeing if a the StaffID field is empty or has a value for that particular tickets updates. If there is a staffID then it has been updated by a staff member, however if it does not then the qUpdate was done by a user.

Essentialy the way this works is a user posts a question by inserting into the Question table, and replies are made by inserting into the qUpdate table and linked to the original question using the foreign key - "QuestionID".

The tables:

CREATE TABLE Staff
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Name        VARCHAR(40) NOT NULL
);

CREATE TABLE Customer
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Name        VARCHAR(40) NOT NULL,
    Email       VARCHAR(40) NOT NULL
);

CREATE TABLE Product
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Name    TEXT NOT NULL
);

CREATE TABLE Question
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Problem VARCHAR(1000),
    Status  VARCHAR(20) NOT NULL DEFAULT 'open', 
    Priority    INTEGER NOT NULL,
    LoggedTime  TIMESTAMP NOT NULL,
    CustomerID  INTEGER NOT NULL,
    ProductID   INTEGER NOT NULL,
    FOREIGN KEY (ProductID) REFERENCES Product(ID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(ID),
    CHECK (Status IN ('open','closed') AND Priority IN (1,2,3))

);

CREATE TABLE qUpdate
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Message VARCHAR(1000) NOT NULL,
    UpdateTime  TIMESTAMP NOT NULL,
    QuestionID  INTEGER NOT NULL,
    StaffID INTEGER,
    FOREIGN KEY (StaffID) REFERENCES Staff(ID),
    FOREIGN KEY (QuestionID) REFERENCES Question(ID)
);

Some sample inserts:

INSERT INTO Customer (ID, Name, Email) VALUES (1, 'testname1', 'testemail1');

INSERT INTO Customer (ID, Name, Email) VALUES (2, 'testname2', 'testemail2');

INSERT INTO Staff (ID, Name) VALUES (1, 'Don Keigh');

INSERT INTO Product (ID, Name) VALUES (1, 'Xbox');

INSERT INTO Question (ID, Problem, Status, Priority, LoggedTime, CustomerID, ProductID) 
VALUES  (1, 'testproblem1', 'open', 3, '2012-04-14 09:30', 2, 1);

INSERT INTO Question (ID, Problem, Status, Priority, LoggedTime, CustomerID, ProductID) 
VALUES  (2, 'testproblem2', 'open', 3, '2012-04-14 09:30', 2, 1);

INSERT INTO qUpdate (ID, Message, UpdateTime, StaffID, QuestionID) VALUES (2, 'testmessage1','2012-07-12 14:27', 1, 1);

INSERT INTO qUpdate (ID, Message, UpdateTime, QuestionID) VALUES (3, 'testmessage1','2012-06-18 19:42', 2);

What I've done so far (which obviously doesn't work)

UPDATE Question
SET Status = 'closed' 
WHERE EXISTS
(SELECT qUpdate.QuestionID  
MAX(qUpdate.UpdateTime - Now() = INTERVAL '1 day') FROM qUpdate
LEFT JOIN Question ON qUpdate.QuestionID = Question.ID
WHERE qUpdate.StaffID IS NOT NULL);

I realise my explanation may be a bit confusing so if you need more info post and I'll reply ASAP

+1 for supplying all the SQL for a test case (although you missed the create table for product) — user330315
– user330315, Commented Apr 8, 2012 at 22:35
Someone gave a great answer but since deleted it.. If anyone could shine any light on what the tU part of the query means then it would really help in the future. set Status = 'closed' from ( select Question, max (UpdateTime) LastUpdateTime from qUpdate group by QuestionID having count(StaffID) > 0 ) tU where Question.ID = tU.TicketID and Question.LoggedTime < tU.LastUpdateTime + INTERVAL '-1 DAY'; — Jimmy
– Jimmy, Commented Apr 8, 2012 at 22:44

dbenhur · Accepted Answer · 2012-04-09 03:06:29Z

3

UPDATE Question
SET Status = 'closed' 
where
-- this clause asserts there's at least one staff answer
exists (
  select null from qUpdate
  where qUpdate.QuestionID = Question.ID
  and StaffID is not null
)
-- this clause asserts there's been no update in the last 24 hours
and not exists (
  select null from qUpdate
  where qUpdate.QuestionID = Question.ID
  and qUpdate.UpdateTime > (now() - interval '24 hours') 
)
and Status = 'open';

You'll almost certainly want an index on qUpdate(QuestionID) or possibly qUpdate(QuestionID,UpdateTime) or qUpdate(QuestionID,StaffID) to get good performance on the subselects in the exists() tests.

edited Apr 9, 2012 at 3:06

answered Apr 8, 2012 at 22:15

dbenhur

20.5k4 gold badges51 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jimmy Over a year ago

Thanks a lot for the quick reply! Although I just ran it based on and no fields were updated Also will that check for the latest UpdateTime? There may be more than one qUpdate and therefore the qUpdate associated with a question that has the latest date is needed to be checked.

Jimmy Over a year ago

These are the inserts I just ran it off: INSERT INTO Staff (ID, Name) VALUES (1, 'Don Keigh');

INSERT INTO Question (ID, Problem, Status, Priority, LoggedTime, CustomerID, ProductID)  VALUES 	(1, 'testproblem1', 'open', 3, '2012-08-14 09:30', 2, 1);

INSERT INTO qUpdate(ID, Message, UpdateTime, StaffID, QuestionID) VALUES (2, 'testmessage1','2012-06-10 09:30', 1, 1);

dbenhur Over a year ago

@Jimmy, the UpdateTime dates in your sample are in the future, so they will be more recent than now()-24hours.

Jimmy Over a year ago

I've tested it since and there still doesn't seem to be any change in the tables, which is strange. sqlfiddle.com/#!1/a10dc/3

dbenhur Over a year ago

@kgrittn that's not a reverse logic bug on the time comparison. The question wants to close if no update within the last 24 hours the test is NOT exists(updates in last 24 hours)

|

Collectives™ on Stack Overflow

Need Help Writing SQL Query in Postgresql

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related