Improve query...

Hello,
Ive got this query:

SELECT  T.jobid,T.taskid,T.description,J.Job,Tk.Task FROM timesheet as T
LEFT JOIN Jobs as J ON T.JobID = J.ID
LEFT JOIN Tasks as Tk ON T.TaskID = Tk.ID
WHERE CONVERT(NVARCHAR,StartTime,101) >=  '10/19/2009'
 AND  CONVERT(NVARCHAR,StartTime,101) <=  '10/25/2009'
GROUP BY t.jobid,t.taskid,t.description,j.job,tk.task,T.TenderID

What i want to do is, if the restult of JobID is 0 or less, bring the ID from another table and place it in the same column T.jobID and the same for the column J.Job

This is what ive got and it gives me what im after but i was wondering if there is another way to do it

SELECT
      CASE
            WHEN T.JobID <=0 THEN T.TenderID
            ELSE T.JobID
      END as JobID,
      CASE
            WHEN T.JobID <=0 THEN (SELECT TenderNumber FROM Tender WHERE ID = T.TenderID)
            ELSE J.Job
      END,
 T.jobid,T.taskid,T.description,J.Job,Tk.Task FROM timesheet as T
LEFT JOIN Jobs as J ON T.JobID = J.ID
LEFT JOIN Tasks as Tk ON T.TaskID = Tk.ID
WHERE CONVERT(NVARCHAR,StartTime,101) >=  '10/19/2009'
 AND  CONVERT(NVARCHAR,StartTime,101) <=  '10/25/2009'
GROUP BY t.jobid,t.taskid,t.description,j.job,tk.task,T.TenderID


Thanks!
LVL 8
arcrossAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FVERCommented:
Your solution is OK but :
1 - GROUP BY by clause could be replaced by DISTINCT KEYWORD
2 - You could also consider a left join with Tender table instead of inline select
3 - The way you compare dates is wrong : you should convert strings to dates, not the other way. Alphabetical order is not the same as date chronology with date format 101. At least you shoul use a Year-month-date format, but converting strings to dates is better.
SELECT DISTINCT
      CASE
            WHEN T.JobID <=0 THEN T.TenderID
            ELSE T.JobID
      END as JobID,
      CASE 
            WHEN T.JobID <=0 THEN Te.TenderNumber
            ELSE J.Job
      END,
 T.jobid,T.taskid,T.description,J.Job,Tk.Task
FROM timesheet as T
LEFT JOIN Jobs as J ON T.JobID = J.ID 
LEFT JOIN Tasks as Tk ON T.TaskID = Tk.ID
LEFT JOIN Tender as Te ON Te.ID = T.TenderID
WHERE StartTime BETWEEN CONVERT(Datetime,'10/19/2009',101) AND CONVERT(Datetime,'10/25/2009',101)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.