I have the following query that was graciously provided here.

SELECT recorddate, sum(case when h.description = 'First Floor' then 1 else 0 end) as Floor1 , sum(case when h.description = 'Second Floor' then 1 else 0 end) as Floor2 , sum(case when h.description = 'Third Floor' then 1 else 0 end) as Floor3 , sum(case when h.description = 'Fourth Floor' then 1 else 0 end) as Floor4 , sum(case when h.description = 'Fifth Floor' then 1 else 0 end) as Floor5 , sum(case when h.description = 'Sixth Floor' then 1 else 0 end) as Floor6 , sum(case when h.description = 'Seventh Floor' then 1 else 0 end) as Floor7 , sum(case when h.description = 'Eighth Floor' then 1 else 0 end) as Floor8 , sum(case when h.description = 'Ninth Floor' then 1 else 0 end) as Floor9 , sum(case when h.description = 'Tenth Floor' then 1 else 0 end) as Floor10 , sum(case when h.description IS null then 1 else 0 end) as UnAssignedFROM tblMainHistory v, tblLocationCode hWHERE h.LOCATIONCODE =* v.LOCATIONGROUP BY RecordDate

In the source table (tblMainHistory) there may be multiple instances of the same record. These are distinguished by a 'RECORDIDNUMBER'. So, somehow I need to COUNT(DISTINCT RECORDIDNUMBER) with that query.

For instance, example data from tblMainHistory might look like this:
In that example the result of the query would be:

The technicolor spreadsheet image was a sincere effort to facilitate your understanding - I hope it didn't confuse you more than help you. My mind works in STRANGE ways! :)

Why you dont first select distinct rows from tblMainHistory like this ?

SELECT recorddate, sum(case when h.description = 'First Floor' then 1 else 0 end) as Floor1 , sum(case when h.description = 'Second Floor' then 1 else 0 end) as Floor2 , sum(case when h.description = 'Third Floor' then 1 else 0 end) as Floor3 , sum(case when h.description = 'Fourth Floor' then 1 else 0 end) as Floor4 , sum(case when h.description = 'Fifth Floor' then 1 else 0 end) as Floor5 , sum(case when h.description = 'Sixth Floor' then 1 else 0 end) as Floor6 , sum(case when h.description = 'Seventh Floor' then 1 else 0 end) as Floor7 , sum(case when h.description = 'Eighth Floor' then 1 else 0 end) as Floor8 , sum(case when h.description = 'Ninth Floor' then 1 else 0 end) as Floor9 , sum(case when h.description = 'Tenth Floor' then 1 else 0 end) as Floor10 , sum(case when h.description IS null then 1 else 0 end) as UnAssignedFROM (SELECT DISTINCT LOCATION,RECORDDATE FROM tblMainHistory) v, tblLocationCode hWHERE h.LOCATIONCODE =* v.LOCATIONGROUP BY RecordDate

SELECT recorddate, sum(case when h.description = 'First Floor' then 1 else 0 end) as Floor1 , sum(case when h.description = 'Second Floor' then 1 else 0 end) as Floor2 , sum(case when h.description = 'Third Floor' then 1 else 0 end) as Floor3 , sum(case when h.description = 'Fourth Floor' then 1 else 0 end) as Floor4 , sum(case when h.description = 'Fifth Floor' then 1 else 0 end) as Floor5 , sum(case when h.description = 'Sixth Floor' then 1 else 0 end) as Floor6 , sum(case when h.description = 'Seventh Floor' then 1 else 0 end) as Floor7 , sum(case when h.description = 'Eighth Floor' then 1 else 0 end) as Floor8 , sum(case when h.description = 'Ninth Floor' then 1 else 0 end) as Floor9 , sum(case when h.description = 'Tenth Floor' then 1 else 0 end) as Floor10 , sum(case when h.description IS null then 1 else 0 end) as UnAssignedFROM (SELECT DISTINCT RECORDIDNUMBER, LOCATION,RECORDDATE FROM tblMainHistory) v, tblLocationCode hWHERE h.LOCATIONCODE =* v.LOCATIONGROUP BY RecordDate

SELECT recorddate, sum(case when h.description = 'First Floor' then 1 else 0 end) as Floor1
, sum(case when h.description = 'Second Floor' then 1 else 0 end) as Floor2
, sum(case when h.description = 'Third Floor' then 1 else 0 end) as Floor3
, sum(case when h.description = 'Fourth Floor' then 1 else 0 end) as Floor4
, sum(case when h.description = 'Fifth Floor' then 1 else 0 end) as Floor5
, sum(case when h.description = 'Sixth Floor' then 1 else 0 end) as Floor6
, sum(case when h.description = 'Seventh Floor' then 1 else 0 end) as Floor7
, sum(case when h.description = 'Eighth Floor' then 1 else 0 end) as Floor8
, sum(case when h.description = 'Ninth Floor' then 1 else 0 end) as Floor9
, sum(case when h.description = 'Tenth Floor' then 1 else 0 end) as Floor10
, sum(case when h.description IS null then 1 else 0 end) as UnAssigned
FROM (Select Distinct Fields From tblMainHistory) v Left Outer Join tblLocationCode h
on v.LOCATION =h.LOCATIONCODE
GROUP BY RecordDate

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.