1

There's two columns dateRequested and dateCompleted in DATE type and I want to select only 4 of the rows with the least time between the two.

SELECT *, DATEDIFF(dateCompleted,dateRequested) AS Time
FROM ticket
ORDER BY Time;

With the above statement I get what I want but since there are nulls and in the case of the question for the sake of a challenge I guess there is negative time between the two so I really only want those where Time >= 0

Example of what I get with above statement

I've tried with a CASE statement but I don't fully grasp that yet so any help would be appreciated.

Thanks

  • You mean you want:? WHERE dateCompleted >= dateRequested – ypercubeᵀᴹ Nov 18 '15 at 7:30
0

In this case I would use a where clause to further qualify the data you are getting back in this case something like:

SELECT *, DATEDIFF(dateCompleted,dateRequested) AS Time
FROM ticket
WHERE ((dateCompleted IS NOT NULL) AND (dateRequested IS NOT NULL)) 
ORDER BY Time;

Since you said you are also trying to return only 4 rows you should use LIMIT 4 in your query, however I'm not sure if that would go before or after your ORDER BY.

If you have tried some things, let us know what you have already tried and what the errors are, this can help prevent people from reinventing the wheel and get your problem fixed faster.

  • Hi, thanks for the suggestion. That and a couple variations I tried seems to give an error about syntax but if it worked it still wouldn't get rid of the negative "time"...all I need is those first 4 that have a "0" time so the ticket was completed in the same day. – Hohn Nov 17 '15 at 21:51
  • Hey it worked I just needed to change it to the following: code SELECT *, DATEDIFF(dateCompleted,dateRequested) AS Time FROM ticket WHERE dateCompleted IS NOT NULL ORDER BY Time; code – Hohn Nov 18 '15 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.