I am trying to solve a problem with my inner joins to sub query. Simply just seeking to have my query do is return MAXDATE from (Monthly_Charge_Date), I tried the following and I keep getting error
Invalid object name
Really what I would like see is the most recent MAXDATE.
Sharing some history, if I remove the sub query the error goes away, however, for historical purposes I would like to see the sub query list the MAXDATE with residents table, perhaps, I need to partition my tables. My data observation is not completed unless I am able to see the sub query partition output from res.Accept_Checks, Active Property tables. What could be the syntax issue?
SELECT res.Accept_Checks, (SELECT MAX(bld.Monthly_Charge_Date) maxDate FROM (SELECT DISTINCT, bld.Monthly_Charge_Date, bld.Building_Name, unt.Unit_Number, res.Accept_Checks, unt.Occupied_Status, enti.Active, enti.Entity_Number FROM dbo.units AS unt INNER JOIN dbo.residents AS res ON unt.Unit_ID = res.Unit_ID INNER JOIN dbo.buildings AS bld ON unt.Building_ID = bld.Building_ID INNER JOIN dbo.gl_entities AS enti ON bld.GL_Entity_ID = enti.GL_Entity_ID INNER JOIN dbo.resident_credit_history AS rsch ON res.Resident_ID = rsch.Resident_ID INNER JOIN dbo.credit_codes AS crhc ON rsch.History_Code_ID = crhc.History_Code_ID WHERE bld.Monthly_Charge_Date >= DATEADD(MONTH, -12, GETDATE()) AND enti.active = 1 AND crhc.active = 1 AND bld.building_Active = 1 AND unt.Occupied_Status = 1 AND CHECKS = 0) res) MaxDate, res.Accept_Checks FROM dbo.residents ORDER BY bld.Monthly_Charge_Date DESC, bld.Building_Name