Multi Part Identifier Can Not be Bound

I’m having trouble getting past this error. I’m using SQL Server 2017 (v14):

The multi-part identifier “PS_FFC.Date” could not be bound. The multi-part identifier “PS_FFC.Date: could not be bound. The multi-part identifier “PS_FFC.Employee ID” could not be bound.

Code:

SELECT 
    [Employee ID],
    (SELECT SUM(Total) AS Expr1
     FROM dbo.PS_FFC
     WHERE (PS_FFC.Date >= DATEADD(DAY, 1, EOMONTH(GETDATE(), - 1)))
       AND (PS_FFC.Date < GETDATE())
     GROUP BY PS_FFC.[Employee ID]) AS Current_Month_FFCs,
    (SELECT SUM(Total) AS Expr2
     FROM dbo.PS_FFC AS PS_FFC_2
     WHERE (PS_FFC.Date >= DATEADD(DAY, 1, EOMONTH(GETDATE(), - 2))) 
       AND (PS_FFC.Date < EOMONTH(GETDATE(), - 1))
     GROUP BY PS_FFC.[Employee ID]) AS Prior_Month_FFCs
FROM 
    dbo.PS_FFC AS PS_FFC_1

The table I’m pulling from looks like:

Employee ID|    Date|         FFC
Emp000          1/1/2019      1
Emp000          1/2/2019      2
Emp000          12/31/2019    1
Emp001          1/3/2019      1
Emp003          1/20/2019     2
Emp004          2/5/2019      4

This what I’m hoping for the result to look like:

Employee ID|    Current_Month_FFCs|    Prior_Month_FFCs
Emp000          0                      3
Emp001          0                      1  
Emp003          0                      2
Emp004          4                      0

Answer

Your second subquery is using the alias PS_FFC_2 for the table and you’re referencing as PS_FFC. The good thing is that you don’t need the subqueries for this and you can simply use a CASE expression to aggregate only the rows you need.

SELECT PS_FFC.[Employee ID],
    SUM( CASE WHEN PS_FFC.Date >= DATEADD(DAY, 1, EOMONTH(GETDATE(), - 1)) AND PS_FFC.Date < GETDATE()
                    THEN PS_FFC.Total ELSE 0 END) AS Current_Month_FFCs,
    SUM( CASE WHEN PS_FFC.Date >= DATEADD(DAY, 1, EOMONTH(GETDATE(), - 2)) AND PS_FFC.Date < DATEADD(DAY, 1, EOMONTH(GETDATE(), - 1))
                    THEN PS_FFC.Total ELSE 0 END) AS Prior_Month_FFCs
FROM  dbo.PS_FFC AS PS_FFC
WHERE  PS_FFC.Date >= DATEADD(DAY, 1, EOMONTH(GETDATE(), - 2)) 
AND PS_FFC.Date < GETDATE()
GROUP BY Employee_ID;