August 20, 2010 at 1:42 am
Hi All, I have a problem extracting the latest job from a table of jobs ...
The table structure is as follows:
Col1 JobID (Key)
Col2 CustID
Col3 Job Type
Col4 DateWorkDone
My SQL code is as follows ...
"SELECT TOP 100 PERCENT CustID, MAX(DateWorkDone) AS DateWorkDone
FROM dbo.Jobs
GROUP BY CustID
ORDER BY CustID"
I have used Max(DateWorkDone) instead of the JobID due to the fact that updates of historic jobs are done retrospectively, therefor the job with the highest JobID for each CustID may not be the last job.
This forces me to do another select using the CustID and the DateWorkDone to determine the appropriate JobID and this works except where more than 1 job is performed on the same day.
Is there a way to determine the JobID of the last job based on the DateWorkDone for each CustID in 1 script?
eg something like ...
"SELECT TOP 100 PERCENT CustID, MAX(DateWorkDone) AS DateWorkDone, JobID
FROM dbo.Jobs
GROUP BY CustID
ORDER BY CustID"
Any help is most appreciated.
August 20, 2010 at 2:01 am
You need to join back to your original table to get this. Something like this (not tested because you didn't supply any table DDL or sample data):
WITH LatestJob AS (
SELECT CustID, MAX(DateWorkDone) AS DateWorkDone
FROM dbo.Jobs
GROUP BY CustID
)
SELECT l.CustID, l.DateWorkDone, j.JobID
FROM LatestJob l JOIN Jobs j
ON l.CustID = j.CustID AND l.DateWorkDone = j.DateWorkDone
ORDER BY l.CustID
If there can be two equal values of DateWorkDone for the same CustID then you will also need to decide which one the query returns and tweak the query accordingly.
John
August 26, 2010 at 6:56 am
Is there a way to determine the JobID of the last job based on the DateWorkDone for each CustID in 1 script?
Untestest
SELECT *
FROM
(SELECT RANK() OVER(PARTITION BY CustID ORDER BY DateWorkDone DESC) AS job_seq, JobID, CustID, DateWorkDone
FROM Jobs)
WHERE job_seq =1;
brgds
Philipp Post
brgds
Philipp Post
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply