The statement in question is working correctly, I just need to understand why and how!
I have encountered the following Syntax in a statement which gets information for employee analysis.
Never seen anything like it and cannot find anything about the behavior using google. Hope you people can help me understand how this works and how to use it.
Table 1 Employees (EmployeeID, Name, Birthdate, ...)
Table 2 Contracts (ContractID, EmployeeID, Startdate, Enddate, ...)
Table 3 Time-models (TimeID, ContractID, EmployeeID, MonthlyDate, ...)
Table 4 Insurance (InsuranceID, ContractID, EmployeeID, JobType, ...)
The subselect in the code later on outputs:
SELECT E.EmployeeID+C.ContractID, D.Date, E.Name
FROM Employees AS E
LEFT OUTER JOIN Contract AS C ON E.EmployeeID = C.Employee ID
--This is where the SQL I was talking about starts
LEFT OUTER JOIN (
WHERE MonthlyDate >= Convert(DATETIME, '2016-01-01', 102)) AS D
ON (D.MonthlyDate >= C.ContractStartDate AND D.MonthlyDate <= C.ContractEndDate)
/*I can not explain this On-Condition*/
LEFT OUTER JOIN Insurance as I
ON (I.EmployeeID = E.EmployeeID AND I.ContractID = C.ContractID)
WHERE I.JobType = 'Clerk'
AND (I.InsuranceStartDate <= D.MonthlyDate AND I.InsuranceEndDate >= D.MonthlyDate)
/* I can not understand which compare values are used in D.MonthlyDate
because of the the vague ON-Condition */
WHERE condition of your query is turning the
LEFT JOIN into an
INNER JOIN. (If a row doesn't match then the
I columns will be
NULL and fail the
So, you might as well re-write the query as:
SELECT E.EmployeeID+C.ContractID, D.Date, E.Name FROM Employees E JOIN Contract C ON E.EmployeeID = C.Employee ID JOI [Time-models] d ON D.MonthlyDate >= C.ContractStartDate AND D.MonthlyDate <= C.ContractEndDate AND MonthlyDate >= '2016-01-01' JOIN Insurance as I ON I.EmployeeID = E.EmployeeID AND I.ContractID = C.ContractID WHERE I.JobType = 'Clerk' AND I.InsuranceStartDate <= D.MonthlyDate AND I.InsuranceEndDate >= D.MonthlyDate;
I'm not sure if this answers your question, though.