Would like to know why my query is not providing the number of units that are "developer units total" and the number of "is_future_residents". These are the numbers that are not in my data set and that's the number I am trying to create with the counts and the case statements. The Data set is not getting calculated by my query and that's the number I need to verify. If I try to remove "Unit_Number" it could work, this column needs to remain because it provides the individual entity unit number.
Created query with SQL Server Management Studio, my output to display correctly has parameters, and I have tried the following with my query. Query does not include the total number of developer units nor the number of future residents.
Currently my SQL SSMS query output displays the following:
Entity_Number Association Name Readable_Building_ID Unit_Number Number_Units Developer_Unit_Total Is_Future_Resident
0660 Services Association 0004 0010 24 1 No
0660 Services Association 0006 0002 13 1 No
0660 Services Association 0006 0003 13 1 Yes
0660 Services Association 0006 0004 13 1 Yes
0660 Services Association 0006 0005 13 1 No
I'm looking for a query pivot that will provide this output:
Entity_Number Association Name Readable_Building_ID Unit_Number Number_Units Developer_Unit_Total Is_Future_Resident
0660 Services Association 0004 0010 24 1 0
0660 Services Association 0006 0002 13 1 0
0660 Services Association 0006 0003 13 1 1
0660 Services Association 0006 0004 13 1 1
0660 Services Association 0006 0005 13 1 0
[Query]
SELECT
ge.Entity_Number,
ge.Name AS 'Association Name',
bld.Readable_Building_ID,
u.Unit_Number,
bld.Number_Units,
COUNT(DISTINCT u.Unit_ID) AS 'Developer_Unit_Total',
(CASE WHEN res.Resident_Status_ID = 3 THEN 'Yes' ELSE 'No'
END) AS Is_Future_Resident
FROM Residents AS res
INNER JOIN units AS u
ON res.Unit_ID = u.Unit_ID
INNER JOIN buildings AS bld
ON u.Building_ID = bld.Building_ID
INNER JOIN gl_entities AS ge
ON bld.Entity_ID = ge.Entity_ID
INNER JOIN resident_status AS rest
ON res.Status_ID = rest.Status_ID
WHERE
u.Is_Developer_Unit = 1
AND ge.Active = 1
AND bld.Building_Active = 1
GROUP BY
ge.Entity_Number,
ge.Name,
bld.Readable_Building_ID,
u.Unit_Number,
bld.Number_Units,
res.Resident_Status_ID