I am asked to provide a date in a query from a table that may or may not have a record. Thus the outer join. The issue is the joined table may have many records for the ID (foreign key) being joined, and the query is returning the first result not the last. There is an additional foreign key that each record would have in them. how would I do that join?
Here is the current query.
SELECT
A.UserID,
A.Completed,
A.FolderID,
ISNULL(A.AppDateTimeEnd,AppDateTimeStart) AS AppDate,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
A.MentalAcuity,
A.Score,
J.JobTitle,
J.TestType,
L.AreaName,
L.State
FROM
dbo.Jobs J INNER JOIN
dbo.Application A ON J.JobID = A.JobID INNER JOIN (
SELECT UserID,MIN(LocationID) AS LocationID
FROM dbo.Application_Locations
GROUP BY UserID ) AL ON AL.UserID = A.UserID INNER JOIN
dbo.Locations L ON AL.LocationID = L.LocationID
I want to add the matching disposition date for the UserID when it is the same FolderID as in the Application table
Here is what I currently have for a query where it is returning the wrong record's date.
SELECT
A.UserID,
A.Completed,
A.FolderID,
ISNULL(A.AppDateTimeEnd,AppDateTimeStart) AS AppDate,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
A.MentalAcuity,
A.Score,
J.JobTitle,
J.TestType,
L.AreaName,
L.State,
D.DispositionDate
FROM
dbo.Jobs J INNER JOIN
dbo.Application A ON J.JobID = A.JobID INNER JOIN (
SELECT UserID,MIN(LocationID) AS LocationID
FROM dbo.Application_Locations
GROUP BY UserID ) AL ON AL.UserID = A.UserID INNER JOIN
dbo.Locations L ON AL.LocationID = L.LocationID LEFT OUTER JOIN
dbo.Applicant_Disposition D ON A.UserID = D.UserID
There are times when there is no record in the disposition table. For example when the applicant has not completed the application or when Manager/HR have not viewed or made a decision on the application. When they do decide, they move the application through the hiring process, so there will be a record each time the applicant is moved forward until either hired or they are rejected. Either way a record with the FolderID in the Applicant_Disposition table will match what shows in the Application table and that is the DispositionDate that I want.
Something like this:
FROM
...
dbo.Locations L ON AL.LocationID = L.LocationID OUTER APPLY (
SELECT TOP (1) D.*
FROM dbo.Applicant_Disposition D
WHERE A.UserID = D.UserID
ORDER BY ID DESC
) AS D
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 29, 2024 at 6:10 pm
Thank You!!!
March 29, 2024 at 6:23 pm
You're welcome! I had to make one correction to the query above, btw.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply