Comments by Sambit_Sahoo (Top 9 by date)

Below is the Stored Procedure
CREATE PROC [dbo].[Reporting_TrainingHistory](

@CompanyIds varchar(500),

@RequestingContactId INT,

@GroupIds varchar(max) = NULL,

@ContactIds varchar(max) = NULL,

@AllEmployees BIT = 0,

@FromDate datetime = NULL,

@ToDate datetime = NULL ,

@Passed bit = 0 ,

@Failed bit = 0,

@Disabled bit = 0,--@ShowInactiveDocuments

@ShowInactiveContacts BIT = 0,

@IncludeGroupsInResultSet BIT = 0,

@TestStatusFilter VARCHAR(MAX) = NULL,

@LastTestStatusFilter VARCHAR(MAX) = NULL,

@SortOrder1 VARCHAR(MAX) = 'CompanyDescriptor',

@SortOrder2 VARCHAR(MAX) = 'ContactDecriptor',

@SortOrder3 VARCHAR(MAX) = 'DocumentDescriptor',

@SortOrder4 VARCHAR(MAX) = 'TestingDateDesc',

@DocumentIds VARCHAR(MAX) = null

) AS SET NOCOUNT ON



DECLARE @CDP TABLE (

ContactID INT

,DocumentID INT

,DocumentAssignedDate DATETIME

,[Type] VARCHAR(12)

,NextTestDate DATETIME

,LastTestDate DATETIME

,DeliveryDate DATETIME

,TestFrequency INT

,LastContactDocumentTestAttemptID INT

,HasTest BIT

,Active BIT

,ContactDocumentTestAttemptStatusId INT

)



IF @AllEmployees = 1 --use create a group of lists for the companies

BEGIN

SET @GroupIds = NULL

SELECT @GroupIds = COALESCE(@GroupIds+',','') + LTRIM(GroupId)

FROM [Group] g

JOIN fnSplit(@CompanyIds,',') l ON g.CompanyId = l.item

END



IF @ContactIds IS NULL OR @ContactIds = ''

BEGIN

SET @ContactIds = NULL

SELECT @ContactIds = COALESCE(@ContactIds+',','') + LTRIM(ContactId)

FROM [Contact] g

JOIN fnSplit(@CompanyIds,',') l ON g.CompanyId = l.item

END



--filter the contact ids if the requesting contact isn't an admin but is a hierarchial user manager

IF (SELECT .dbo.fnContact_IsHiearchialManager(@RequestingContactId)) = 1 AND (SELECT .dbo.fnContact_IsAdmin(@RequestingContactId)) = 0

BEGIN

DECLARE @tempContactIds VARCHAR(MAX)

SELECT @tempContactIds = COALESCE(@tempContactIds+',','') + LTRIM(s.ContactId)

FROM .dbo.fnContact_Subordinates(@RequestingContactId) s

JOIN .dbo.fnSplit(@ContactIds, ',') s1 ON s1.item = s.ContactId

SET @ContactIds = @tempContactIds

END



INSERT INTO @CDP

SELECT cdp.*

FROM ContactDocumentPermissions cdp

JOIN (SELECT ContactId = item FROM .dbo.fnSplit(@ContactIds, ',')) ids ON cdp.ContactId = ids.ContactId

WHERE HasTest = 1





SELECT DISTINCT

c.ContactId

,ContactDecriptor = c._Descriptor

,ContactActive = c.Active

,d.DocumentId

,DocumentDescriptor = d._Descriptor

,DocumentActive = d.Active

,d.MinPassingPercent

,d.PastDue

,com.CompanyId

,CompanyDescriptor = com._Descriptor

,CompanyActive = com.Active

,GroupId = CASE @IncludeGroupsInResultSet

WHEN 1 THEN cg.GroupId

ELSE NULL END

,GroupDescriptor = CASE @IncludeGroupsInResultSet

WHEN 1 THEN gda._Descriptor

ELSE NULL END

,ReportDate = cdta._EditDate

,LastTestStatus = .dbo.fn_TestStatus(cdta.ContactDocumentTestAttemptStatusId,FinalScore,PastDue,LastContactDocumentTestAttemptId,NextTestDate,MinPassingPercent)

--Cimmented by ASHISH MOTGHARE

--,ReportStatus = .dbo.fn_ReportStatus(NbrOfDeliveryDelayDays,DocumentAssignedDate,cdta.ContactDocumentTestAttemptStatusId,LastTestDate,NextTestDate,PastDue,d.HasTest,ShowOnCurriculum)

,ReportStatus = CASE

WHEN 0 = 1

THEN 'Cur

Yes bro,thanks a lot

Yes bro,thanks a lot

Documentid = DocumentIds.Substring(0, DocumentIds.LastIndexOf(','));