Adding the scripts to get data from prod DB.

with CTE(PersonID,SignedDocuments) AS(
select pr.ID as PersonID, count(dc.LatestStatus) as SignedDocuments from company as cmp
left join person as pr on pr.CompanyId= cmp.ID
left join document as dc on dc.RowRefID=pr.ID and dc.TableRefID=1 and dc.LatestStatus=6
Group BY pr.ID)
select cmp.Name as CompanyName, pr.ID as PersonID, pr.GivenName as PersonName, Count(dc.ID) as TotalDocuments, CTE.SignedDocuments
from company as cmp
left join person as pr on pr.CompanyId= cmp.ID
left join document as dc on dc.RowRefID=pr.ID and dc.TableRefID=1
Left join CTE on CTE.PersonID=dc.RowRefID
Group BY pr.ID;

select * from company;

Hi Samir

Sharing you prod data. Please have a look at this.
prod_data_17-02-2020.csv

Add Account did in the result.

Hi Samir

Here is the latest query for getting the prod data along with the AccoundDID.

SELECT cmp.ID CompanyId, cmp.Name CompanyName, cmp.AccountDID,SELECT cmp.ID CompanyId, cmp.Name CompanyName, cmp.AccountDID, pr.ID PersonId, pr.GivenName FirstName, pr.Middle MiddleName, pr.Family LastName, e.Email,        dc.ID DocumentId, dt.TemplateName DocumentName, cl.Description LatestStatus, dc.Created_dateTime SentDateTimefrom company as cmpleft join person as pr on pr.CompanyId= cmp.IDleft join communicationdetails cd on cd.CommunicationId = pr.CommunicationId AND cd.TableRefID = 5left join email e on e.ID = cd.RowRefID left join document as dc on dc.RowRefID=pr.ID and  dc.TableRefID=1left join documenttemplate dt on dc.documenttemplateid = dt.IDleft join codelist cl on cl.Id = dc.LatestStatus;

cc: Amrendra Kumar, Patrick Phillips, Ravi Banda

Hi Samir

I have added the new result data along with the AccountDID. 

prod_data_19-02-2020.csv

Hi Samir

Here is the updated query.

SELECT cmp.ID CompanyId, cmp.Name CompanyName, cmp.AccountDID,SELECT cmp.ID CompanyId, cmp.Name CompanyName, cmp.AccountDID, pr.ID PersonId, pr.GivenName FirstName, pr.Middle MiddleName, pr.Family LastName, e.Email, u.UserTypeID,         (CASE u.UserTypeID        WHEN 34 then 'Candidate'        WHEN 35 THEN 'Admin'        WHEN 68 THEN 'Super Admin'        ELSE 'Guest'        END) UserType,        dc.ID DocumentId, dt.TemplateName DocumentName, cl.Description LatestStatus, dc.Created_dateTime SentDateTimefrom company cmp left join person pr on pr.CompanyId= cmp.ID        left join users u on u.ID = pr.UserLoginId left join communicationdetails cd on cd.CommunicationId = pr.CommunicationId AND cd.TableRefID = 5 left join email e on e.ID = cd.RowRefID  left join document dc on dc.RowRefID=pr.ID and  dc.TableRefID=1 left join documenttemplate dt on dc.documenttemplateid = dt.ID left join codelist cl on cl.Id = dc.LatestStatus;

and here is the latest result

prod_data_27-02-2020.csv

Hi Ravi Banda, Patrick Phillips

We have extracted the data from the production environment. Please have a look at the attached file.

CBAT Client Data - DMS 27-Feb-20.xls

Also, As we are done with this so closing this ticket.

cc: Samir

Thanks

Narendra