CODEWITHSUNDEEP
sqlsql-server
CoolLife
CoolLife

Reputation: 1479

CROSS JOIN DATA inside a INNER JOIN (select ) problem of multi-part

I need to use the "CROSS JOIN" to combine 2 tables, the problem is that when doing the "INNER JOIN" with a third it no longer recognizes the cd.WorkDate

error that I have is

the multi-part identifier "cd.WorkDate" could not be found

UPDATE
    Emps
SET
    CanWork1 = CASE WHEN cd.WeekDay = 1 THEN 0 ELSE CanWork1 END,
    CanWork2 = CASE WHEN cd.WeekDay = 2 THEN 0 ELSE CanWork2 END,
    CanWork3 = CASE WHEN cd.WeekDay = 3 THEN 0 ELSE CanWork3 END,
    CanWork4 = CASE WHEN cd.WeekDay = 4 THEN 0 ELSE CanWork4 END,
    CanWork5 = CASE WHEN cd.WeekDay = 5 THEN 0 ELSE CanWork5 END,
    CanWork6 = CASE WHEN cd.WeekDay = 6 THEN 0 ELSE CanWork6 END,
    CanWork7 = CASE WHEN cd.WeekDay = 7 THEN 0 ELSE CanWork7 END
FROM
    #PS_EMPLOYEES AS Emps

    CROSS JOIN (SELECT WorkDate, WeekDay
    FROM #checkDays) AS cd

    LEFT JOIN
    (SELECT EmployeeNumber, NewStatus
    FROM dbo.tblPAY_Employees_StatusHistory
    WHERE cd.WorkDate BETWEEN StartDate AND EndDate) AS Stat
    ON Emps.EmployeeNumber = Stat.EmployeeNumber
WHERE
    IsNull(Stat.NewStatus,2) <> 1

Upvotes: 0

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

It is not obvious to me why you are using any subqueries in the from clause. This is simpler and equivalent:

FROM #PS_EMPLOYEES Emps CROSS JOIN
     #checkDays cd LEFT JOIN
     dbo.tblPAY_Employees_StatusHistory stat
     ON cd.WorkDate BETWEEN stat.StartDate AND stat.EndDate AND
        Emps.EmployeeNumber = stat.EmployeeNumber

In SQL Server (and most databases) subqueries to select just a few columns are superfluous. The optimizer ignores them -- actually the optimizer only processes the columns it needs from the table anyway.

As I note in the comment, though, this probably does not do what you really want. In particular, if the FROM clause results in multiple rows for any row in emps, the update will be to a single arbitrary row.

Upvotes: 0

zealous
zealous

Reputation: 7503

The reason you are getting that error because you are accessing cd.WorkDate in your sub-query, If you take that where condition out of it, query should be good. Also your query should work without sub-query on left join. Try the following.

UPDATE
    Emps
SET
    CanWork1 = CASE WHEN cd.WeekDay = 1 THEN 0 ELSE CanWork1 END,
    CanWork2 = CASE WHEN cd.WeekDay = 2 THEN 0 ELSE CanWork2 END,
    CanWork3 = CASE WHEN cd.WeekDay = 3 THEN 0 ELSE CanWork3 END,
    CanWork4 = CASE WHEN cd.WeekDay = 4 THEN 0 ELSE CanWork4 END,
    CanWork5 = CASE WHEN cd.WeekDay = 5 THEN 0 ELSE CanWork5 END,
    CanWork6 = CASE WHEN cd.WeekDay = 6 THEN 0 ELSE CanWork6 END,
    CanWork7 = CASE WHEN cd.WeekDay = 7 THEN 0 ELSE CanWork7 END
FROM
    #PS_EMPLOYEES AS Emps

    CROSS JOIN 
    (
        SELECT 
            WorkDate, 
            WeekDay
        FROM #checkDays
    ) AS cd

    LEFT JOIN dbo.tblPAY_Employees_StatusHistory stat
    ON Emps.EmployeeNumber = Stat.EmployeeNumber
    WHERE cd.WorkDate BETWEEN stat.StartDate AND stat.EndDate
    AND IsNull(Stat.NewStatus,2) <> 1

Upvotes: 1

Related Questions