Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

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!
0
arcross
Asked:
arcross
1 Solution
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now