Posts - Consolidation

For Discussion

#	Comment	Owner
1
2

Hicom and HEE have completed the consolidation of 9 separate Intrepid DBs, into a single DB. As part of the consolidation, Hicom have created the table vwLocalOffice to trace the consolidated data back to the DB origin;

Posts can only be linked to the vwLocalOffice table using ItemType = 'Site' and UniqueID = MainSiteID

Posts by LocalOfficeName and Status;

select ISNULL(LocalOfficeName, 'NULL') LocalOfficeName, Status, count(*) as rc
from [HEELIVE].[vwPost] as p
left join [HEELIVE].[vwLocalOffice] as lo on (lo.ItemType = 'Site' and lo.UniqueID = p.MainSiteID)
group by LocalOfficeName, Status
order by 2, 3 desc,1;

As of 06/10/2017 the are 270,180 posts in Intrepid, 117,565 Current, and 1512,615 Inactive

Posts by LocalOfficeName and Status

The following LocalOffices have shared Sites within them, with 166,325 posts;

select ISNULL(LocalOfficeName, 'NULL') LocalOfficeName, Status, count(*) as rc
from [HEELIVE].[vwPost] as p
join [HEELIVE].[vwLocalOffice] as lo on (lo.ItemType = 'Site' and lo.UniqueID = p.MainSiteID)
where p.MainSiteID in (select UniqueID
                       from [HEELIVE].[vwLocalOffice] as lo
                       where lo.ItemType = 'Site'
                       group by UniqueID
                       having count(*) > 1)
group by LocalOfficeName, Status
order by 2, 3 desc,1;

LocalOffices with shared Sites and Posts