none
Query to get sum of Weightage/Points

    Question

  • Hi Team,

    Need to get the sum of weightage  for  questions. some questions will also have subquestion.  in that case need to get the sum of weightage of question and subquestion.

    Attached the screenshots for reference 

    questiondetail table

    subquestiondetail table

    weightage table

    I have tried as  below but my query is not fetching the right details.  please correct me and Need inputs.


      select  sum (wgt.weightage), sqd.SubQuestionId from tblWeightage WGT  
      join 
      tblquestiondetail qts on WGT.QuestionDetailId = qts.Id  
      join
      tblquestion q on qts.questionid = q.id
      join
      tblsubquestiondetail sqd on sqd.SubQuestionId = qts.questionid
      where wgt.AttributeListItemId in (1,2)
      group by
      sqd.SubQuestionId

    Thanks


    Vamshi Janagama

    Wednesday, March 08, 2017 5:07 AM

Answers

  • Check if this what you are expecting:

    ;WITH QuestionLvlWeightage AS
    (
    	SELECT	QuestionDetailId, SUM(Weightage) Weightage
    	FROM	tblWeightage
    	GROUP BY	QuestionDetailId
    )
    SELECT	Q.ShortDescription
    ,		MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    		/* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    		SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    		*/
    FROM	QuestionLvlWeightage W
    JOIN		tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    JOIN		tblQuestion Q ON QD.QuestionId = Q.Id
    LEFT JOIN	tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    LEFT JOIN	tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    LEFT JOIN	QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    GROUP BY	Q.ShortDescription

    Thanks!

    Thursday, March 09, 2017 8:25 AM
  • Try adding this to the ORDER BY clause of the same query you posted and see if its what you are looking for.

    ORDER BY	GroupLeaderName, RIGHT(ReviewPeriod, 4) DESC, LEFT(ReviewPeriod, 2) DESC

    Thanks!

    Tuesday, March 21, 2017 5:29 AM
  • DECLARE @quartName nvarchar(10)
    -- return previous four quarters, each quarter is three months
    DECLARE @Quarter Table (quar nvarchar(10))
    DECLARE @Count int = 0
    DECLARE @Date date 
    
    SET @Date = getdate();
    WHILE @Count < 5
    BEGIN
    set @quartName = (SELECT
        CASE
            WHEN MONTH(@Date) BETWEEN 1  AND 3  THEN  '4Q' + convert(char(4), YEAR(@Date) - 1)
            WHEN MONTH(@Date) BETWEEN 4  AND 6  THEN  '1Q' + convert(char(4), YEAR(@Date) - 0)
            WHEN MONTH(@Date) BETWEEN 7  AND 9  THEN  '2Q'+ convert(char(4), YEAR(@Date) - 0) 
            WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN  '3Q' + convert(char(4), YEAR(@Date) - 0) 
        END AS Quarter )
    
    INSERT INTO @Quarter 
    VALUES 
    (@quartName)
    
    SET @Count = @Count + 1
        --previous quarter
    SET @Date = DATEADD(month, -3, @Date);
    END
    
    ;WITH QuestionLvlWeightage AS
    (
    	SELECT	QuestionDetailId, SUM(Weightage) Weightage
    	FROM	tblWeightage
    	GROUP BY	QuestionDetailId
    ), WeightageCalculatedTemp AS
    (
    	SELECT	Q.Id QuestionId, Q.ShortDescription
    	,		MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    			/* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    			SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    			*/
    	FROM	QuestionLvlWeightage W
    	JOIN		tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    	JOIN		tblQuestion Q ON QD.QuestionId = Q.Id
    	LEFT JOIN	tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    	LEFT JOIN	tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    	LEFT JOIN	QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    	GROUP BY	Q.Id, Q.ShortDescription
    ), WeightageCalculated AS
    (
    	SELECT	WC.*
    	FROM	WeightageCalculatedTemp WC
    	LEFT JOIN	(
    					SELECT	DISTINCT SubQuestionId 
    					FROM	tblSubQuestionDetail SubQn
    					JOIN	WeightageCalculatedTemp WCT
    					ON		SubQn.MainQuestionId	=	WCT.QuestionId
    				) SubQn		
    	ON			WC.QuestionId =	SubQn.SubQuestionId
    	WHERE	SubQn.SubQuestionId IS NULL	--	Exclude SubQuestions if they are included as part of Main Question
    ), FullCompletedSurveyIDs AS
    (
    	SELECT	H.Id
    	FROM	tblSRRHistorical H
    	JOIN	(
    				SELECT	SurveyUserId, ReviewPeriod
    				FROM	tblSRRHistorical
    				WHERE	SurveyID IN (1, 2)
    				AND		SurveyStatusID = 1	--	Survey Complete
    				GROUP BY	SurveyUserId, ReviewPeriod
    				HAVING	COUNT(1) = 2
    			) completedSurvey
    	ON		H.SurveyUserId	=	completedSurvey.SurveyUserId
    	AND		H.ReviewPeriod	=	completedSurvey.ReviewPeriod
    ), CombinedSurveyData AS
    (
    	--	AM QUESTIONNAIRE SURVEY
    	SELECT SH.ReviewPeriod AS ReviewPeriod,  
    	SU.EmployeeId as GLEmployeeId,
    	SU.FirstName + ' ' + SU.LastName  as GroupLeaderName,
    	S.Name	AS SurveyName,
    	SU.CostCenter AS CostCenter,
    				DP.BUDepartmentName AS Department,
    		SE.Name as SectionName, 
    	Q.ShortDescription AS ShortDescription, 
    	--AN.Answer AS  Answer,
    	SUM(WC.TotalWeightage) TotalWeightage,
    	CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
    		FROM tblSRRHistorical SH 
    		JOIN FullCompletedSurveyIDs CS ON SH.Id = CS.Id	--	this limits to only full completed surveys (for respective user / review period)
    		JOIN tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
    		JOIN tbltemplate T ON  T.Id = S.TemplateId
    		JOIN tblTemplateDetail TD ON  TD.TemplateId = T.Id
    		JOIN tblSection SE ON  TD.SectionId = SE.Id
    		JOIN tblSectionDetail SED ON  SE.Id = SED.SectionId
    		JOIN tblQuestion Q ON  Q.Id = SED.QuestionId
    		JOIN tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    		JOIN tblAttribute AT ON  AT.Id = QD.AttributeId
    		JOIN tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    		JOIN tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
    		--join tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
    		join tblDepartment DP on SU.DepartmentId = DP.Id
    		JOIN tblStatus ST ON  ST.Id = SH.SurveyStatusId
    		JOIN WeightageCalculated WC ON  Q.Id = WC.QuestionId
    		WHERE S.Name = 'AM QUESTIONNAIRE SURVEY' 
    
    		--and SH.ReviewPeriod = '4Q2016' 
    		--and SU.EmployeeId = '24147' 
    		and       SH.ReviewPeriod in( SELECT quar FROM @Quarter)
    		--and       SH.SurveyStatusId =
    		and SE.Name != 'Process' 
    		and Q.ShortDescription != 'Null' 
    		group by SH.ReviewPeriod,  
    	SU.EmployeeId,
    	SU.FirstName+' '+SU.LastName,
    	S.Name,
    	SU.CostCenter,
    	DP.BUDepartmentName,  
    	SE.Name, 
    	Q.ShortDescription
    	--AN.Answer
    	UNION ALL
    	--	FMDS QUESTIONNAIRE SURVEY
    	SELECT SH.ReviewPeriod AS ReviewPeriod,  
    			SU.EmployeeId AS GLEmployeeId,	
    			(SELECT SU.FirstName+' '+SU.LastName ) AS GroupLeaderName,	
    			S.Name SurveyName,     
    			SU.CostCenter AS CostCenter,
    			DP.BUDepartmentName AS Department,  	        
    			SE.Name AS  SectionName,	
    			substring(SML.LabelDescription, 0, patindex('%=%', SML.LabelDescription )) AS ShortDescription,	  	 
    			(SML.Points) AS TotalWeightage,
    			cast(sum(AN.Weightage) AS INT) AS ActualPoints   
    	FROM tblSRRHistorical SH 
    		JOIN FullCompletedSurveyIDs CS ON SH.Id = CS.Id
    		Join tblSurvey S ON SH.SurveyId = S.Id
    		Join tbltemplate T ON  T.Id = S.TemplateId
    		Join tblTemplateDetail TD ON  TD.TemplateId = T.Id
    		Join tblSection SE ON  TD.SectionId = SE.Id
    		Join tblSectionDetail SED ON  SE.Id = SED.SectionId
    		Join tblQuestion Q ON  Q.Id = SED.QuestionId	  
    		Join tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    		Join tblAttribute AT ON  AT.Id = QD.AttributeId
    		join tblSRRMasterLabel SML ON SE.Id = SML.SectionId  and SML.AttributeId = AT.Id 
    		Join tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    		join tblSurveyUser SU ON  SU.Id = SH.SurveyUserId	
    		join tblDepartment DP ON SU.DepartmentId = DP.Id
    		join tblStatus ST ON  ST.Id = SH.SurveyStatusId	
    	WHERE S.Name = 'FMDS QUESTIONNAIRE SURVEY' 
    	and   SH.ReviewPeriod in( SELECT quar FROM @Quarter) 
    	GROUP BY  SH.ReviewPeriod ,  
    			SU.EmployeeId ,	
    			SU.FirstName,SU.LastName ,	
    			S.Name,	     
    			SU.CostCenter,
    			DP.BUDepartmentName,           
    			SE.Name,
    			SML.LabelDescription,
    			SML.Points
    )
    SELECT	CSD.*
    FROM	CombinedSurveyData CSD
    JOIN	(
    			SELECT	GroupLeaderName, ReviewPeriod
    			FROM	CombinedSurveyData
    			GROUP BY	GroupLeaderName, ReviewPeriod
    			HAVING	COUNT(DISTINCT SurveyName) = 2
    		) BothSurveyDone
    ON		CSD.GroupLeaderName	=	BothSurveyDone.GroupLeaderName
    AND		CSD.ReviewPeriod	=	BothSurveyDone.ReviewPeriod
    ORDER BY GroupLeaderName, SurveyName, RIGHT(CSD.ReviewPeriod, 4) DESC, LEFT(CSD.ReviewPeriod, 2) DESC

    • Proposed as answer by José Diz Saturday, March 25, 2017 11:19 AM
    • Marked as answer by Vamshi Janagama Monday, March 27, 2017 1:16 AM
    Saturday, March 25, 2017 6:56 AM
  • See if the below one is what you are looking for.

    ;WITH QuestionLvlWeightage AS
    (
    	SELECT	QuestionDetailId, SUM(Weightage) Weightage
    	FROM	tblWeightage
    	GROUP BY	QuestionDetailId
    ), WeightageCalculatedTemp AS
    (
    	SELECT	Q.Id QuestionId, Q.ShortDescription
    	,		MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    			/* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    			SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    			*/
    	FROM	QuestionLvlWeightage W
    	JOIN		tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    	JOIN		tblQuestion Q ON QD.QuestionId = Q.Id
    	LEFT JOIN	tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    	LEFT JOIN	tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    	LEFT JOIN	QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    	GROUP BY	Q.Id, Q.ShortDescription
    ), WeightageCalculated AS
    (
    	SELECT	WC.*
    	FROM	WeightageCalculatedTemp WC
    	LEFT JOIN	(
    					SELECT	DISTINCT SubQuestionId 
    					FROM	tblSubQuestionDetail SubQn
    					JOIN	WeightageCalculatedTemp WCT
    					ON		SubQn.MainQuestionId	=	WCT.QuestionId
    				) SubQn		
    	ON			WC.QuestionId =	SubQn.SubQuestionId
    	WHERE	SubQn.SubQuestionId IS NULL	--	Exclude SubQuestions if they are included as part of Main Question
    )
    SELECT	SH.ReviewPeriod AS ReviewPeriod,  
    		SU.EmployeeId as GLEmployeeId,
    		SU.FirstName + ' ' + SU.LastName  as GroupLeaderName,
    		SU.CostCenter AS CostCenter,
            DP.BUDepartmentName AS Department,
    --		SE.Name as SectionName, 
    		Q.ShortDescription AS ShortDescription,
     		AN.Answer AS  Answer,
    		SUM(WC.TotalWeightage) TotalWeightage,
    		CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
    FROM	tblSRRHistorical SH 
    JOIN	tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
    JOIN	tbltemplate T ON  T.Id = S.TemplateId
    JOIN	tblTemplateDetail TD ON  TD.TemplateId = T.Id
    JOIN	tblSection SE ON  TD.SectionId = SE.Id
    JOIN	tblSectionDetail SED ON  SE.Id = SED.SectionId
    JOIN	tblQuestion Q ON  Q.Id = SED.QuestionId
    JOIN	tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    JOIN	tblAttribute AT ON  AT.Id = QD.AttributeId
    JOIN	tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    JOIN	tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
    --join	tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
    join	tblDepartment DP on SU.DepartmentId = DP.Id
    JOIN	tblStatus ST ON  ST.Id = SH.SurveyStatusId
    JOIN	WeightageCalculated WC ON  Q.Id = WC.QuestionId
    WHERE	S.Name = 'AM QUESTIONNAIRE SURVEY' 
    and		SH.ReviewPeriod = '4Q2016' 
    and		SU.EmployeeId = '24147' 
    and		SE.Name != 'Process' 
    and		Q.ShortDescription != 'Null' 
    group by	SH.ReviewPeriod,  
    			SU.EmployeeId,
    			SU.FirstName+' '+SU.LastName,
    			--SU.ReportsToName,
    			SU.CostCenter,
    			DP.BUDepartmentName,   
    			Q.ShortDescription,
    			AN.Answer

    Thanks!

    Monday, March 20, 2017 7:04 AM
  • As it looks like one Survey historical id already point to respective user / review period, I tried to first bring all the required survey historical ids that are related to fully completed surveys (meaning both survey status ids to be 1 for respective user / review period). Then used those list of ids directly into the JOIN of MAIN query. This doesn't need to again explicitly check for user / review period as it gets indirectly mapped to survey historical id.

    See if this returns the expected results.

    DECLARE @quartName nvarchar(10)
     -- return previous four quarters, each quarter is three months
     DECLARE @Quarter Table (quar nvarchar(10))
     DECLARE @Count int = 0
     DECLARE @Date date 
    
     SET @Date = getdate();
     WHILE @Count < 5
     BEGIN
      set @quartName = (SELECT
            CASE
             WHEN MONTH(@Date) BETWEEN 1  AND 3  THEN  '4Q' + convert(char(4), YEAR(@Date) - 1)
             WHEN MONTH(@Date) BETWEEN 4  AND 6  THEN  '1Q' + convert(char(4), YEAR(@Date) - 0)
             WHEN MONTH(@Date) BETWEEN 7  AND 9  THEN  '2Q'+ convert(char(4), YEAR(@Date) - 0) 
             WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN  '3Q' + convert(char(4), YEAR(@Date) - 0) 
            END AS Quarter )
    
      INSERT INTO @Quarter 
       VALUES 
      (@quartName)
    
       SET @Count = @Count + 1
         --previous quarter
       SET @Date = DATEADD(month, -3, @Date);
     END 
    
    
     ;WITH QuestionLvlWeightage AS
     (
    SELECT
    QuestionDetailId, SUM(Weightage) Weightage
    FROM
    tblWeightage
    GROUP BY
    QuestionDetailId
     ), WeightageCalculatedTemp AS
     (
    SELECT
    Q.Id QuestionId, Q.ShortDescription,
    
    
            cast(MAX(W.Weightage) AS INT) + cast(ISNULL(SUM(WThruSQ.Weightage), 0) AS INT) TotalWeightage
    
    --MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    /* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    
    */
    FROM
    QuestionLvlWeightage W
    JOIN
    tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    JOIN
    tblQuestion Q ON QD.QuestionId = Q.Id
    LEFT JOIN
    tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    LEFT JOIN
    tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    LEFT JOIN
    QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    GROUP BY
    Q.Id, Q.ShortDescription
     ), WeightageCalculated AS
     (
    SELECT
    WC.*
    FROM
    WeightageCalculatedTemp WC
    LEFT JOIN
    (
    SELECT
    DISTINCT SubQuestionId 
    FROM
    tblSubQuestionDetail SubQn
    JOIN
    WeightageCalculatedTemp WCT
    ON
    SubQn.MainQuestionId
    = WCT.QuestionId
    ) SubQn
    
    ON
    WC.QuestionId =  SubQn.SubQuestionId
    WHERE
    SubQn.SubQuestionId IS NULL
    -- Exclude SubQuestions if they are included as part of Main Question
     ), FullCompletedSurveyIDs AS
    (
    	SELECT	H.Id
    	FROM	tblSRRHistorical H
    	JOIN	(
    				SELECT	SurveyUserId, ReviewPeriod
    				FROM	tblSRRHistorical
    				WHERE	SurveyID IN (1, 2)
    				AND		SurveyStatusID = 1	--	Survey Complete
    				GROUP BY	SurveyUserId, ReviewPeriod
    				HAVING	COUNT(1) = 2
    			) completedSurvey
    	ON		H.SurveyUserId	=	completedSurvey.SurveyUserId
    	AND		H.ReviewPeriod	=	completedSurvey.ReviewPeriod
    )
     SELECT SH.ReviewPeriod AS ReviewPeriod,  
    SU.EmployeeId as GLEmployeeId,
    SU.FirstName + ' ' + SU.LastName  as GroupLeaderName,
    SU.CostCenter AS CostCenter,
             DP.BUDepartmentName AS Department,
       SE.Name as SectionName, 
    Q.ShortDescription AS ShortDescription, 
    --AN.Answer AS  Answer,
    SUM(WC.TotalWeightage) TotalWeightage,
    CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
     FROM tblSRRHistorical SH 
     JOIN FullCompletedSurveyIDs CS ON SH.Id = CS.Id	--	this limits to only full completed surveys (for respective user / review period)
     JOIN tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
     JOIN tbltemplate T ON  T.Id = S.TemplateId
     JOIN tblTemplateDetail TD ON  TD.TemplateId = T.Id
     JOIN tblSection SE ON  TD.SectionId = SE.Id
     JOIN tblSectionDetail SED ON  SE.Id = SED.SectionId
     JOIN tblQuestion Q ON  Q.Id = SED.QuestionId
     JOIN tblQuestionDetail  QD ON QD.QuestionId = Q.Id
     JOIN tblAttribute AT ON  AT.Id = QD.AttributeId
     JOIN tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
     JOIN tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
     --join tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
     join tblDepartment DP on SU.DepartmentId = DP.Id
     JOIN tblStatus ST ON  ST.Id = SH.SurveyStatusId
     JOIN WeightageCalculated WC ON  Q.Id = WC.QuestionId
     WHERE S.Name = 'AM QUESTIONNAIRE SURVEY' 
    
     --and SH.ReviewPeriod = '4Q2016' 
     --and SU.EmployeeId = '24147' 
       and       SH.ReviewPeriod in( SELECT quar FROM @Quarter)
      --and       SH.SurveyStatusId =
       and SE.Name != 'Process' 
       and Q.ShortDescription != 'Null' 
     group by SH.ReviewPeriod,  
    SU.EmployeeId,
    SU.FirstName+' '+SU.LastName,
    
    SU.CostCenter,
    DP.BUDepartmentName,  
    SE.Name, 
    Q.ShortDescription
    --AN.Answer
    ORDER BY
    GroupLeaderName, RIGHT(ReviewPeriod, 4) DESC, LEFT(ReviewPeriod, 2) DESC

    Thanks!

    Wednesday, March 22, 2017 9:08 AM
  • Check if this works - tried to club all 3 queries into one using similar UNION ALL. 

    DECLARE @quartName nvarchar(10)
    -- return previous four quarters, each quarter is three months
    DECLARE @Quarter Table (quar nvarchar(10))
    DECLARE @Count int = 0
    DECLARE @Date date 
    
    SET @Date = getdate();
    WHILE @Count < 5
    BEGIN
    set @quartName = (SELECT
        CASE
            WHEN MONTH(@Date) BETWEEN 1  AND 3  THEN  '4Q' + convert(char(4), YEAR(@Date) - 1)
            WHEN MONTH(@Date) BETWEEN 4  AND 6  THEN  '1Q' + convert(char(4), YEAR(@Date) - 0)
            WHEN MONTH(@Date) BETWEEN 7  AND 9  THEN  '2Q'+ convert(char(4), YEAR(@Date) - 0) 
            WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN  '3Q' + convert(char(4), YEAR(@Date) - 0) 
        END AS Quarter )
    
    INSERT INTO @Quarter 
    VALUES 
    (@quartName)
    
    SET @Count = @Count + 1
        --previous quarter
    SET @Date = DATEADD(month, -3, @Date);
    END
    
    ;WITH QuestionLvlWeightage AS
    (
    	SELECT	QuestionDetailId, SUM(Weightage) Weightage
    	FROM	tblWeightage
    	GROUP BY	QuestionDetailId
    ), WeightageCalculatedTemp AS
    (
    	SELECT	Q.Id QuestionId, Q.ShortDescription
    	,		MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    			/* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    			SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    			*/
    	FROM	QuestionLvlWeightage W
    	JOIN		tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    	JOIN		tblQuestion Q ON QD.QuestionId = Q.Id
    	LEFT JOIN	tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    	LEFT JOIN	tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    	LEFT JOIN	QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    	GROUP BY	Q.Id, Q.ShortDescription
    ), WeightageCalculated AS
    (
    	SELECT	WC.*
    	FROM	WeightageCalculatedTemp WC
    	LEFT JOIN	(
    					SELECT	DISTINCT SubQuestionId 
    					FROM	tblSubQuestionDetail SubQn
    					JOIN	WeightageCalculatedTemp WCT
    					ON		SubQn.MainQuestionId	=	WCT.QuestionId
    				) SubQn		
    	ON			WC.QuestionId =	SubQn.SubQuestionId
    	WHERE	SubQn.SubQuestionId IS NULL	--	Exclude SubQuestions if they are included as part of Main Question
    ), FullCompletedSurveyIDs AS
    (
    	SELECT	H.Id
    	FROM	tblSRRHistorical H
    	JOIN	(
    				SELECT	SurveyUserId, ReviewPeriod
    				FROM	tblSRRHistorical
    				WHERE	SurveyID IN (1, 2)	--	SRRMasterId = 1--SurveyID IN (1, 2)
    				AND		SurveyStatusID = 1	--	Survey Complete
    				GROUP BY	SurveyUserId, ReviewPeriod
    				HAVING	COUNT(1) = 2
    			) completedSurvey
    	ON		H.SurveyUserId	=	completedSurvey.SurveyUserId
    	AND		H.ReviewPeriod	=	completedSurvey.ReviewPeriod
    ), CombinedSurveyData AS
    (
    	--	AM QUESTIONNAIRE SURVEY
    	SELECT SH.ReviewPeriod AS ReviewPeriod,  
    	SU.EmployeeId as GLEmployeeId,
    	SU.FirstName + ' ' + SU.LastName  as GroupLeaderName,
    	S.Name	AS SurveyName,
    	SU.CostCenter AS CostCenter,
    				DP.BUDepartmentName AS Department,
    		SE.Name as SectionName, 
    	Q.ShortDescription AS ShortDescription, 
    	--AN.Answer AS  Answer,
    	SUM(WC.TotalWeightage) TotalWeightage,
    	CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
    		FROM tblSRRHistorical SH 
    		JOIN FullCompletedSurveyIDs CS ON SH.Id = CS.Id	--	this limits to only full completed surveys (for respective user / review period)
    		JOIN tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
    		JOIN tbltemplate T ON  T.Id = S.TemplateId
    		JOIN tblTemplateDetail TD ON  TD.TemplateId = T.Id
    		JOIN tblSection SE ON  TD.SectionId = SE.Id
    		JOIN tblSectionDetail SED ON  SE.Id = SED.SectionId
    		JOIN tblQuestion Q ON  Q.Id = SED.QuestionId
    		JOIN tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    		JOIN tblAttribute AT ON  AT.Id = QD.AttributeId
    		JOIN tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    		JOIN tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
    		--join tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
    		join tblDepartment DP on SU.DepartmentId = DP.Id
    		JOIN tblStatus ST ON  ST.Id = SH.SurveyStatusId
    		JOIN WeightageCalculated WC ON  Q.Id = WC.QuestionId
    		WHERE S.Name = 'AM QUESTIONNAIRE SURVEY' 
    
    		--and SH.ReviewPeriod = '4Q2016' 
    		--and SU.EmployeeId = '24147' 
    		and       SH.ReviewPeriod in( SELECT quar FROM @Quarter)
    		--and       SH.SurveyStatusId =
    		and SE.Name != 'Process' 
    		and Q.ShortDescription != 'Null' 
    		group by SH.ReviewPeriod,  
    	SU.EmployeeId,
    	SU.FirstName+' '+SU.LastName,
    	S.Name,
    	SU.CostCenter,
    	DP.BUDepartmentName,  
    	SE.Name, 
    	Q.ShortDescription
    	--AN.Answer
    	UNION ALL
    	--	FMDS QUESTIONNAIRE SURVEY
    	SELECT SH.ReviewPeriod AS ReviewPeriod,  
    			SU.EmployeeId AS GLEmployeeId,	
    			(SELECT SU.FirstName+' '+SU.LastName ) AS GroupLeaderName,	
    			S.Name SurveyName,     
    			SU.CostCenter AS CostCenter,
    			DP.BUDepartmentName AS Department,  	        
    			SE.Name AS  SectionName,	
    			substring(SML.LabelDescription, 0, patindex('%=%', SML.LabelDescription )) AS ShortDescription,	  	 
    			(SML.Points) AS TotalWeightage,
    			cast(sum(AN.Weightage) AS INT) AS ActualPoints   
    	FROM tblSRRHistorical SH 
    		JOIN FullCompletedSurveyIDs CS ON SH.Id = CS.Id
    		Join tblSurvey S ON SH.SurveyId = S.Id
    		Join tbltemplate T ON  T.Id = S.TemplateId
    		Join tblTemplateDetail TD ON  TD.TemplateId = T.Id
    		Join tblSection SE ON  TD.SectionId = SE.Id
    		Join tblSectionDetail SED ON  SE.Id = SED.SectionId
    		Join tblQuestion Q ON  Q.Id = SED.QuestionId	  
    		Join tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    		Join tblAttribute AT ON  AT.Id = QD.AttributeId
    		join tblSRRMasterLabel SML ON SE.Id = SML.SectionId  and SML.AttributeId = AT.Id 
    		Join tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    		join tblSurveyUser SU ON  SU.Id = SH.SurveyUserId	
    		join tblDepartment DP ON SU.DepartmentId = DP.Id
    		join tblStatus ST ON  ST.Id = SH.SurveyStatusId	
    	WHERE S.Name = 'FMDS QUESTIONNAIRE SURVEY' 
    	and   SH.ReviewPeriod in( SELECT quar FROM @Quarter) 
    	GROUP BY  SH.ReviewPeriod ,  
    			SU.EmployeeId ,	
    			SU.FirstName,SU.LastName ,	
    			S.Name,	     
    			SU.CostCenter,
    			DP.BUDepartmentName,           
    			SE.Name,
    			SML.LabelDescription,
    			SML.Points
    UNION ALL
    	--	Course Data
    	SELECT  SH.ReviewPeriod AS ReviewPeriod,  
    	SU.EmployeeId AS GLEmployeeId,
    	(SELECT SU.FirstName+' '+SU.LastName ) AS GroupLeaderName,
    	'COURSEDATA' as SurveyName,
    	SU.CostCenter AS CostCenter,
    	DP.BUDepartmentName AS Department,  
    	NULL SectionName,
    	cl.Name as ShortDescription, 
    	cl.points as  TotalWeightage, 
    	sum( scr.CoursePoints) as ActualPoints
    	FROM		tblCourseScore scr
    	inner join	tblCourseandLabelMapping clm ON  scr.CourseandLabelMappingId = clm.Id
    	Inner join	tblCourseLabel cl ON cl.Id = clm.CourseLabelId 
    	inner join	tblSRRHistorical sh ON scr.SRRHistoricalId = sh.Id
    	inner join	FullCompletedSurveyIDs CS ON SH.Id = CS.Id
    	inner join	tblSurveyUser su ON sh.SurveyUserId = su.Id
    	inner join	tblDepartment DP ON SU.DepartmentId = DP.Id
    	WHERE	SH.ReviewPeriod in ( SELECT quar FROM @Quarter)
    	and		cl.srrmasterid = 1
    	GROUP BY  SH.ReviewPeriod ,  
    	SU.EmployeeId ,
    	SU.FirstName,
    	SU.LastName ,
    	SU.CostCenter,
    	DP.BUDepartmentName,  
    	cl.Name, 
    	cl.points
    )
    SELECT	CSD.*
    FROM	CombinedSurveyData CSD
    LEFT JOIN	(
    				SELECT	GroupLeaderName, ReviewPeriod
    				FROM	CombinedSurveyData
    				WHERE	SurveyName	<>	'COURSEDATA'
    				GROUP BY	GroupLeaderName, ReviewPeriod
    				HAVING	COUNT(DISTINCT SurveyName) = 2
    		) BothSurveyDone
    ON		CSD.GroupLeaderName	=	BothSurveyDone.GroupLeaderName
    AND		CSD.ReviewPeriod	=	BothSurveyDone.ReviewPeriod
    WHERE	(
    			SurveyName	=	'COURSEDATA'
    		OR	(
    				SurveyName	<>	'COURSEDATA'
    			AND	BothSurveyDone.ReviewPeriod IS NOT NULL
    			)
    		)
    ORDER BY GroupLeaderName, CASE WHEN SurveyName <> 'COURSEDATA' THEN 0 ELSE 1 END, SurveyName, RIGHT(CSD.ReviewPeriod, 4) DESC, LEFT(CSD.ReviewPeriod, 2)
    Thanks!

    Tuesday, March 28, 2017 7:09 AM
  • Deepak,  I have tested the query and it is working as expected.  Many Thanks

    But to get the results as per my need,  i changed the ORDER BY clause as like below

    ORDER BY 

    RIGHT(CSD.ReviewPeriod, 4) DESC, LEFT(CSD.ReviewPeriod, 2) DESC, GroupLeaderName, CASE WHEN SurveyName <> 'COURSEDATA' THEN 0 ELSE 1 END, SurveyName

    If need any changes needed in the query   or  if i need to add any other conditions, i will post questions for help.

    Thank you for your time and this is very useful and helping thread. 


    Vamshi Janagama

    Wednesday, March 29, 2017 5:33 PM

All replies

  • Missed to mention this point in the initial post, subquestion is also a question. Thanks

    Vamshi Janagama

    Wednesday, March 08, 2017 5:14 AM
  • Don't think I understood it clearly. Can you also provide the screenshot for expected result. It should align with the sample data you are providing from data screenshots. Thanks!
    Wednesday, March 08, 2017 8:16 AM
  • Hi Deepak,

    Thank you for response and time.

    Attaching the screenshots for better understanding. please let me know if you need any further information.

    question table - i missed to attach in the initial post.


    expected results - attached the screenshot

    I want to get the sum of the questions weightage based on Short Description. if there is a question and subquestion relation, the sum of both questions weightage should be displayed. avoid the questions whose Short Description is null.

    As per the question table screenshot, 22 and 23 questions have same short description,  24 and 25 questions have same short description.

    As per the subquestiondetail table screenshot  , 23 question is a subquestion to 22 question and 25 question is a subquestion to 24 question.    Note: not all questions have subquestions. 

    Below is my updated query

     select  sum (wgt.weightage),q.ShortDescription   from tblWeightage WGT  
      join 
      tblquestiondetail qts on WGT.QuestionDetailId = qts.Id  
      join
      tblquestion q on qts.questionid = q.id
      join
      tblsubquestiondetail sqd on  sqd.SubQuestionId = qts.questionid
      where   wgt.AttributeListItemId in (1,2)
      group by
      q.ShortDescription

    Thanks


    Vamshi Janagama

    Wednesday, March 08, 2017 5:10 PM
  • Hi Deepak,

    Thank you for response and time.

    Attaching the screenshots for better understanding. please let me know if you need any further information.

    question table - i missed to attach in the initial post.


    expected results - attached the screenshot

    I want to get the sum of the questions weightage based on Short Description. if there is a question and subquestion relation, the sum of both questions weightage should be displayed. avoid the questions whose Short Description is null.

    As per the question table screenshot, 22 and 23 questions have same short description,  24 and 25 questions have same short description.

    As per the subquestiondetail table screenshot  , 23 question is a subquestion to 22 question and 25 question is a subquestion to 24 question.    Note: not all questions have subquestions. 

    Below is my updated query

     select  sum (wgt.weightage),q.ShortDescription   from tblWeightage WGT  
      join 
      tblquestiondetail qts on WGT.QuestionDetailId = qts.Id  
      join
      tblquestion q on qts.questionid = q.id
      join
      tblsubquestiondetail sqd on  sqd.SubQuestionId = qts.questionid
      where   wgt.AttributeListItemId in (1,2)
      group by
      q.ShortDescription

    Thanks


    Vamshi Janagama

    Why can't provide DDL and sample data for the three tables it can avoid each one building/writing their own table structures based on assumptions. 

    Recursive CTE can be used to achieve query.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, March 08, 2017 5:43 PM
  • Hi Sarat/Deepak,

    Thank you for your time in reviewing my post.

    Attached the scripts

    1. table create script

    1.  table Create Script
    
    
    
    
    USE [XXX]
    GO
    /****** Object:  Table [dbo].[tblAttribute]    Script Date: 3/8/2017 5:03:17 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblAttribute](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[Name] [varchar](50) NOT NULL,	[Description] [varchar](150) NULL,	
     CONSTRAINT [PK_tblAttribute] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[tblAttributeListItem]    Script Date: 3/8/2017 5:04:03 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblAttributeListItem](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[Name] [varchar](50) NULL,	[AttributeId] [int] NOT NULL,
    	
     CONSTRAINT [PK_tblAttributeListItem] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[tblAttributeListItem]  WITH CHECK ADD  CONSTRAINT [FK_tblAttributeListItem_tblAttribute] FOREIGN KEY([AttributeId])
    REFERENCES [dbo].[tblAttribute] ([Id])
    GO
    ALTER TABLE [dbo].[tblAttributeListItem] CHECK CONSTRAINT [FK_tblAttributeListItem_tblAttribute]
    GO
    /****** Object:  Table [dbo].[tblQuestion]    Script Date: 3/8/2017 4:27:28 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblQuestion](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[Question] [varchar](2000) NOT NULL,	[ShortDescription] [varchar](100) NULL,	
     CONSTRAINT [PK_tblQuestion] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[tblSubQuestionDetail]    Script Date: 3/8/2017 4:53:07 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblSubQuestionDetail](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[MainQuestionId] [int] NOT NULL,	[SubQuestionId] [int] NOT NULL,
     CONSTRAINT [PK_tblSubQuestionDetail] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[tblSubQuestionDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblSubQuestionDetail_tblQuestion] FOREIGN KEY([MainQuestionId])
    REFERENCES [dbo].[tblQuestion] ([Id])
    GO
    ALTER TABLE [dbo].[tblSubQuestionDetail] CHECK CONSTRAINT [FK_tblSubQuestionDetail_tblQuestion]
    GO
    ALTER TABLE [dbo].[tblSubQuestionDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblSubQuestionDetail_tblQuestion1] FOREIGN KEY([SubQuestionId])
    REFERENCES [dbo].[tblQuestion] ([Id])
    GO
    ALTER TABLE [dbo].[tblSubQuestionDetail] CHECK CONSTRAINT [FK_tblSubQuestionDetail_tblQuestion1]
    GO
    /****** Object:  Table [dbo].[tblQuestionDetail]    Script Date: 3/8/2017 5:05:26 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblQuestionDetail](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[QuestionId] [int] NOT NULL,	[AttributeId] [int] NOT NULL,	[SequenceOrder] [int] NOT NULL,	
    
     CONSTRAINT [PK_tblQuestionDetail] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[tblQuestionDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblQuestionDetail_tblAttribute] FOREIGN KEY([AttributeId])
    REFERENCES [dbo].[tblAttribute] ([Id])
    GO
    ALTER TABLE [dbo].[tblQuestionDetail] CHECK CONSTRAINT [FK_tblQuestionDetail_tblAttribute]
    GO
    ALTER TABLE [dbo].[tblQuestionDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblQuestionDetail_tblQuestion] FOREIGN KEY([QuestionId])
    REFERENCES [dbo].[tblQuestion] ([Id])
    GO
    ALTER TABLE [dbo].[tblQuestionDetail] CHECK CONSTRAINT [FK_tblQuestionDetail_tblQuestion]
    GO
    /****** Object:  Table [dbo].[tblWeightage]    Script Date: 3/8/2017 5:02:25 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblWeightage](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[QuestionDetailId] [int] NOT NULL,	[AttributeListItemId] [int] NOT NULL,[Weightage] [decimal](5, 2) NOT NULL,
    	
     CONSTRAINT [PK_tblWeightage] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[tblWeightage]  WITH CHECK ADD  CONSTRAINT [FK_tblWeightage_tblAttributeListItem] FOREIGN KEY([AttributeListItemId])
    REFERENCES [dbo].[tblAttributeListItem] ([Id])
    GO
    ALTER TABLE [dbo].[tblWeightage] CHECK CONSTRAINT [FK_tblWeightage_tblAttributeListItem]
    GO
    ALTER TABLE [dbo].[tblWeightage]  WITH CHECK ADD  CONSTRAINT [FK_tblWeightage_tblQuestionDetail] FOREIGN KEY([QuestionDetailId])
    REFERENCES [dbo].[tblQuestionDetail] ([Id])
    GO
    ALTER TABLE [dbo].[tblWeightage] CHECK CONSTRAINT [FK_tblWeightage_tblQuestionDetail]
    GO
    
    
    
    

    2. insert script

    2.  insert script
    
    
    
    
    USE [XXX]
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblAttribute] ON 
    
    GO
    INSERT [dbo].[tblAttribute] ([Id], [Name], [Description]) VALUES (1, N'TEXT', N'Textbox')
    GO
    INSERT [dbo].[tblAttribute] ([Id], [Name], [Description]) VALUES (2, N'COMMENTS', N'Comments')
    GO
    INSERT [dbo].[tblAttribute] ([Id], [Name], [Description]) VALUES (3, N'ANSWER', N'Answer')
    GO
    
    SET IDENTITY_INSERT [dbo].[tblAttribute] OFF
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblAttributeListItem] ON 
    
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (1, N'Y', 3)
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (2, N'N', 3)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblAttributeListItem] OFF
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblQuestion] ON 
    
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (1, N'GL has advised a QC theme to completion', N'Advised a QCC' )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (2, N'GL has lead/coached a team jishuken to completion within group(problem solving tools)', N'Lead Jishuken Teams' )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (3, N'GL has lead/coached 2nd team jishuken to completion within group(problem solving tools)', N'Lead Jishuken Teams' )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (4, N'GL has lead/coached TBP', N'Lead / Coach TBPs' )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (5, N'GL has lead/coached 2nd TBP', N'Lead / Coach TBPs' )
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblQuestion] OFF
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblSubQuestionDetail] ON 
    
    GO
    INSERT [dbo].[tblSubQuestionDetail] ([Id], [MainQuestionId], [SubQuestionId]) VALUES (1, 2, 3)
    GO
    INSERT [dbo].[tblSubQuestionDetail] ([Id], [MainQuestionId], [SubQuestionId]) VALUES (2, 4, 5)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblSubQuestionDetail] OFF
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblQuestionDetail] ON 
    
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (1, 1, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (2, 2, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (3, 3, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (4, 4, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (5, 5, 3)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblQuestionDetail] OFF
    
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblWeightage] ON 
    
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (1, 1, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (2, 1, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (3, 2, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (4, 2, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (5, 3, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (6, 3, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (7, 4, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (8, 4, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (9, 5, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (10, 5, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    SET IDENTITY_INSERT [dbo].[tblWeightage] OFF
    
    GO

    expected results - attached the screenshot

    I want to get the sum of the questions weightage based on Short Description. if there is a question and subquestion relation, the sum of both questions weightage should be displayed. avoid the questions whose Short Description is null.

    As per the question table screenshot, 2 and 3 questions have same short description,  4 and 5 questions have same short description.

    As per the subquestiondetail table screenshot  , 3 question is a subquestion to 2 question and 5 question is a subquestion to 4 question.    Note: not all questions have subquestions. 

    Below is my updated query

     select  sum (wgt.weightage),q.ShortDescription   from tblWeightage WGT  
      join 
      tblquestiondetail qts on WGT.QuestionDetailId = qts.Id  
      join
      tblquestion q on qts.questionid = q.id
      join
      tblsubquestiondetail sqd on  sqd.SubQuestionId = qts.questionid
      where   wgt.AttributeListItemId in (1,2)
      group by
      q.ShortDescription

    please correct me and need inputs to get the expected results.


    Thanks


    Vamshi Janagama

    Wednesday, March 08, 2017 11:45 PM
  • Check if this what you are expecting:

    ;WITH QuestionLvlWeightage AS
    (
    	SELECT	QuestionDetailId, SUM(Weightage) Weightage
    	FROM	tblWeightage
    	GROUP BY	QuestionDetailId
    )
    SELECT	Q.ShortDescription
    ,		MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    		/* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    		SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    		*/
    FROM	QuestionLvlWeightage W
    JOIN		tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    JOIN		tblQuestion Q ON QD.QuestionId = Q.Id
    LEFT JOIN	tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    LEFT JOIN	tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    LEFT JOIN	QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    GROUP BY	Q.ShortDescription

    Thanks!

    Thursday, March 09, 2017 8:25 AM
  • Thank you Deepak,

    Query is fetching expected results and works  fine. 


    Vamshi Janagama

    Friday, March 10, 2017 9:27 PM
  • Thank you Deepak,

    Query is fetching expected results and works  fine. 


    Vamshi Janagama


    Please mark it as answered, if it solved your query in full. Thanks!
    Monday, March 13, 2017 4:30 AM
  • Hi Deepak,

    I have another question,

    i want to use the above query logic and integrate it to another query i wrote.  is it good idea to post the question here in this thread. Please advise?

    Thanks


    Vamshi Janagama

    Monday, March 13, 2017 6:15 PM
  • You could post it here, if you plan to reference the notes from this post while explaining your new question. Thanks!
    Tuesday, March 14, 2017 3:52 AM
  • Hi Deepak,

    I have tried to incorporate the Query to get the TotalWeightage provided by you in the below main query (sqlcode highlighted in bold) which returns the results for an employee, but i see that some of the ShortDescription TotalWeightage is not correct. Please advise and correct my mistakes in the query.  If require, i will send the DDL code as well. 

    Attached the query results for reference.  in the results, i have highlighted the TotalWeightage for short descriptions  TL & TM Versatility improving.  The TotalWeightage should be 2  for each but the query is returning 1.

    USE XXX

    ;WITH QuestionLvlWeightage AS
    (
    SELECT QuestionDetailId, SUM(Weightage) Weightage
    FROM tblWeightage
    GROUP BY QuestionDetailId
    )

    SELECT     
      SH.ReviewPeriod AS ReviewPeriod,  
          SU.EmployeeId as GLEmployeeId,
          SU.FirstName+' '+SU.LastName  as GroupLeaderName,    
      SU.CostCenter AS CostCenter,
           DP.BUDepartmentName AS Department,   
     -- SE.Name as SectionName, 
      Q.ShortDescription AS ShortDescription,  
      cast(sum(AN.Weightage) as int) AS ActualPoints,  
     MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage),0) as TotalWeightage,  
     AN.Answer AS  Answer 
    FROM tblSRRHistorical SH 
    JOIN tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
    JOIN tbltemplate T ON  T.Id = S.TemplateId
    JOIN tblTemplateDetail TD ON  TD.TemplateId = T.Id
    JOIN tblSection SE ON  TD.SectionId = SE.Id
    JOIN tblSectionDetail SED ON  SE.Id = SED.SectionId
    JOIN tblQuestion Q ON  Q.Id = SED.QuestionId  
    JOIN tblQuestionDetail  QD ON QD.QuestionId = Q.Id
     
    JOIN tblAttribute AT ON  AT.Id = QD.AttributeId
    JOIN tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    JOIN tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
    join tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
    join tblDepartment DP on SU.DepartmentId = DP.Id
    JOIN tblStatus ST ON  ST.Id = SH.SurveyStatusId  

    JOIN QuestionLvlWeightage W  on W.QuestionDetailId = QD.Id
            JOIN tblQuestion Qes ON QD.QuestionId = Qes.Id
            LEFT JOIN tblSubQuestionDetail SQD ON Qes.Id = SQD.MainQuestionId
            LEFT JOIN tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
            LEFT JOIN QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    WHERE S.Name = 'AM QUESTIONNAIRE SURVEY' 
    and  SH.ReviewPeriod = '4Q2016' and  SU.EmployeeId = '24147' 
    and  SE.Name != 'Process' and  Q.ShortDescription != 'Null' 
    group by  SH.ReviewPeriod,  
          SU.EmployeeId,
          SU.FirstName+' '+SU.LastName,
      SU.ReportsToName,
      SU.CostCenter,
           DP.BUDepartmentName,  
     Q.ShortDescription,
     AN.Answer,
     Qes.ShortDescription

    QUERY Results


    Vamshi Janagama

    Thursday, March 16, 2017 4:28 AM
  • Looks like you introduced more tables into your query now, so data from any of the tables might be causing to have the weightage reduced than expected.

    If the weightage calculation remain same as specified earlier and is independent of Survey, Answer and other new tables introduced, then try the below query which might work. Else, you have to explain how these new tables relate along with the data for all these tables, for me to try and see where the issue could be from.

    ;WITH QuestionLvlWeightage AS
    (
    	SELECT	QuestionDetailId, SUM(Weightage) Weightage
    	FROM	tblWeightage
    	GROUP BY	QuestionDetailId
    ), WeightageCalculated AS
    (
    	SELECT	Q.ShortDescription
    	,		MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    			/* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    			SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    			*/
    	FROM	QuestionLvlWeightage W
    	JOIN		tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    	JOIN		tblQuestion Q ON QD.QuestionId = Q.Id
    	LEFT JOIN	tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    	LEFT JOIN	tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    	LEFT JOIN	QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    	GROUP BY	Q.ShortDescription
    )
    SELECT	SH.ReviewPeriod AS ReviewPeriod,  
    		SU.EmployeeId as GLEmployeeId,
    		SU.FirstName + ' ' + SU.LastName  as GroupLeaderName,
    		SU.CostCenter AS CostCenter,
            DP.BUDepartmentName AS Department,
    --		SE.Name as SectionName, 
    		Q.ShortDescription AS ShortDescription,
     		AN.Answer AS  Answer,
    		WC.TotalWeightage,
    		CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
    FROM	tblSRRHistorical SH 
    JOIN	tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
    JOIN	tbltemplate T ON  T.Id = S.TemplateId
    JOIN	tblTemplateDetail TD ON  TD.TemplateId = T.Id
    JOIN	tblSection SE ON  TD.SectionId = SE.Id
    JOIN	tblSectionDetail SED ON  SE.Id = SED.SectionId
    JOIN	tblQuestion Q ON  Q.Id = SED.QuestionId
    JOIN	tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    JOIN	tblAttribute AT ON  AT.Id = QD.AttributeId
    JOIN	tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    JOIN	tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
    join	tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
    join	tblDepartment DP on SU.DepartmentId = DP.Id
    JOIN	tblStatus ST ON  ST.Id = SH.SurveyStatusId
    JOIN	WeightageCalculated WC ON  Q.ShortDescription = WC.ShortDescription
    WHERE	S.Name = 'AM QUESTIONNAIRE SURVEY' 
    and		SH.ReviewPeriod = '4Q2016' 
    and		SU.EmployeeId = '24147' 
    and		SE.Name != 'Process' 
    and		Q.ShortDescription != 'Null' 
    group by	SH.ReviewPeriod,  
    			SU.EmployeeId,
    			SU.FirstName+' '+SU.LastName,
    			--SU.ReportsToName,
    			SU.CostCenter,
    			DP.BUDepartmentName,   
    			Q.ShortDescription,
    			AN.Answer,
    			WC.TotalWeightage
    

    Thanks!

    Friday, March 17, 2017 3:55 AM
  • Hi Deepak,

    Thank you for your time in sending the query.

     I have created DDL  with data for two employees. I have executed your query from your previous post and trying to returns the results for an employee "Jonathan Risner" with EmployeeId = '24147' and i see the same issue with the new data  i.e. TotalWeightage for short descriptions  TL Versatility improving & TM Versatility improving is  1  instead of 2.  

    on anlaysis, I feel the relation is missing from tblSubQuestionDetail table. please refer  tblSubQuestionDetail table data for  MainQuestionId  6  and 9 which is related with short descriptions TL Versatility improving & TM Versatility improving.

    Note:

    1.  in the query please remove  " join tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id"  condition, because my DDL scripts does not have create table script for  tblMasBusinessUnit BU.

    2.   when i remove the below condition from the query, i need to return the results for the two employees. 

    and		SU.EmployeeId = '24147' 

    please run the attached the scripts. 

    1. table create script

    USE [XXX]
    GO
    
    /****** Object:  Table [dbo].[tblAttribute]    Script Date: 3/8/2017 5:03:17 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblAttribute](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[Name] [varchar](50) NOT NULL,	[Description] [varchar](150) NULL,	
     CONSTRAINT [PK_tblAttribute] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[tblAttributeListItem]    Script Date: 3/8/2017 5:04:03 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblAttributeListItem](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[Name] [varchar](50) NULL,	[AttributeId] [int] NOT NULL,
    	
     CONSTRAINT [PK_tblAttributeListItem] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[tblAttributeListItem]  WITH CHECK ADD  CONSTRAINT [FK_tblAttributeListItem_tblAttribute] FOREIGN KEY([AttributeId])
    REFERENCES [dbo].[tblAttribute] ([Id])
    GO
    ALTER TABLE [dbo].[tblAttributeListItem] CHECK CONSTRAINT [FK_tblAttributeListItem_tblAttribute]
    GO
    /****** Object:  Table [dbo].[tblQuestion]    Script Date: 3/8/2017 4:27:28 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblQuestion](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[Question] [varchar](2000) NOT NULL,	[ShortDescription] [varchar](100) NULL,	
     CONSTRAINT [PK_tblQuestion] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[tblSubQuestionDetail]    Script Date: 3/8/2017 4:53:07 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblSubQuestionDetail](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[MainQuestionId] [int] NOT NULL,	[SubQuestionId] [int] NOT NULL,
     CONSTRAINT [PK_tblSubQuestionDetail] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[tblSubQuestionDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblSubQuestionDetail_tblQuestion] FOREIGN KEY([MainQuestionId])
    REFERENCES [dbo].[tblQuestion] ([Id])
    GO
    ALTER TABLE [dbo].[tblSubQuestionDetail] CHECK CONSTRAINT [FK_tblSubQuestionDetail_tblQuestion]
    GO
    ALTER TABLE [dbo].[tblSubQuestionDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblSubQuestionDetail_tblQuestion1] FOREIGN KEY([SubQuestionId])
    REFERENCES [dbo].[tblQuestion] ([Id])
    GO
    ALTER TABLE [dbo].[tblSubQuestionDetail] CHECK CONSTRAINT [FK_tblSubQuestionDetail_tblQuestion1]
    GO
    /****** Object:  Table [dbo].[tblQuestionDetail]    Script Date: 3/8/2017 5:05:26 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblQuestionDetail](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[QuestionId] [int] NOT NULL,	[AttributeId] [int] NOT NULL,	[SequenceOrder] [int] NOT NULL,	
    
     CONSTRAINT [PK_tblQuestionDetail] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[tblQuestionDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblQuestionDetail_tblAttribute] FOREIGN KEY([AttributeId])
    REFERENCES [dbo].[tblAttribute] ([Id])
    GO
    ALTER TABLE [dbo].[tblQuestionDetail] CHECK CONSTRAINT [FK_tblQuestionDetail_tblAttribute]
    GO
    ALTER TABLE [dbo].[tblQuestionDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblQuestionDetail_tblQuestion] FOREIGN KEY([QuestionId])
    REFERENCES [dbo].[tblQuestion] ([Id])
    GO
    ALTER TABLE [dbo].[tblQuestionDetail] CHECK CONSTRAINT [FK_tblQuestionDetail_tblQuestion]
    GO
    /****** Object:  Table [dbo].[tblWeightage]    Script Date: 3/8/2017 5:02:25 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblWeightage](
    	[Id] [int] IDENTITY(1,1) NOT NULL,	[QuestionDetailId] [int] NOT NULL,	[AttributeListItemId] [int] NOT NULL,[Weightage] [decimal](5, 2) NOT NULL,
    	
     CONSTRAINT [PK_tblWeightage] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[tblWeightage]  WITH CHECK ADD  CONSTRAINT [FK_tblWeightage_tblAttributeListItem] FOREIGN KEY([AttributeListItemId])
    REFERENCES [dbo].[tblAttributeListItem] ([Id])
    GO
    ALTER TABLE [dbo].[tblWeightage] CHECK CONSTRAINT [FK_tblWeightage_tblAttributeListItem]
    GO
    ALTER TABLE [dbo].[tblWeightage]  WITH CHECK ADD  CONSTRAINT [FK_tblWeightage_tblQuestionDetail] FOREIGN KEY([QuestionDetailId])
    REFERENCES [dbo].[tblQuestionDetail] ([Id])
    GO
    ALTER TABLE [dbo].[tblWeightage] CHECK CONSTRAINT [FK_tblWeightage_tblQuestionDetail]
    GO
    
    
    
    
    /****** Object:  Table [dbo].[tblTemplate]    Script Date: 3/18/2017 9:53:34 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblTemplate](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](100) NOT NULL,	
    	[IsActive] [bit] NOT NULL,
    	
     CONSTRAINT [PK_tblTemplate] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    
    
    
    /****** Object:  Table [dbo].[tblSurvey]    Script Date: 3/18/2017 10:17:25 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblSurvey](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](100) NOT NULL,	
    	[TemplateId] [int] NOT NULL,	
    	[IsActive] [bit] NOT NULL,
    	
     CONSTRAINT [PK_tblSurvey] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    ALTER TABLE [dbo].[tblSurvey]  WITH CHECK ADD  CONSTRAINT [FK_tblSurvey_tblTemplate] FOREIGN KEY([TemplateId])
    REFERENCES [dbo].[tblTemplate] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblSurvey] CHECK CONSTRAINT [FK_tblSurvey_tblTemplate]
    GO
    
    
    
    
    
    /****** Object:  Table [dbo].[tblSection]    Script Date: 3/18/2017 10:28:18 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblSection](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](100) NOT NULL,
    	
     CONSTRAINT [PK_tblSection] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    
    
    /****** Object:  Table [dbo].[tblTemplateDetail]    Script Date: 3/18/2017 10:37:22 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblTemplateDetail](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[TemplateId] [int] NOT NULL,
    	[SectionId] [int] NOT NULL,	
    	[SurveyId] [int] NOT NULL,
     CONSTRAINT [PK_tblTemplateDetail] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[tblTemplateDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblTemplateDetail_tblSection] FOREIGN KEY([SectionId])
    REFERENCES [dbo].[tblSection] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblTemplateDetail] CHECK CONSTRAINT [FK_tblTemplateDetail_tblSection]
    GO
    
    ALTER TABLE [dbo].[tblTemplateDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblTemplateDetail_tblSurvey] FOREIGN KEY([SurveyId])
    REFERENCES [dbo].[tblSurvey] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblTemplateDetail] CHECK CONSTRAINT [FK_tblTemplateDetail_tblSurvey]
    GO
    
    ALTER TABLE [dbo].[tblTemplateDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblTemplateDetail_tblTemplate] FOREIGN KEY([TemplateId])
    REFERENCES [dbo].[tblTemplate] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblTemplateDetail] CHECK CONSTRAINT [FK_tblTemplateDetail_tblTemplate]
    GO
    
    
    
    
    /****** Object:  Table [dbo].[tblDepartment]    Script Date: 3/18/2017 11:08:26 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblDepartment](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[BUDepartmentName] [varchar](150) NOT NULL,
    	
     CONSTRAINT [PK_tblDepartment] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    
    /****** Object:  Table [dbo].[tblSurveyUser]    Script Date: 3/18/2017 11:18:44 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblSurveyUser](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	
    	[EmployeeId] [varchar](50) NULL,
    	[FirstName] [varchar](100) NOT NULL,
    	[LastName] [varchar](100) NOT NULL,
    	
    	[CostCenter] [varchar](50) NOT NULL,
    	
    	[DepartmentId] [int] NOT NULL,
     CONSTRAINT [PK_tblSurveyUser] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[tblSurveyUser]  WITH CHECK ADD  CONSTRAINT [FK_tblSurveyUser_tblDepartment] FOREIGN KEY([DepartmentId])
    REFERENCES [dbo].[tblDepartment] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblSurveyUser] CHECK CONSTRAINT [FK_tblSurveyUser_tblDepartment]
    GO
    
    
    
    
    /****** Object:  Table [dbo].[tblSectionDetail]    Script Date: 3/18/2017 11:37:23 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblSectionDetail](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[SectionId] [int] NOT NULL,
    	[QuestionId] [int] NULL,
    	
     CONSTRAINT [PK_tblSectionDetail] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[tblSectionDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblSectionDetail_tblQuestion] FOREIGN KEY([QuestionId])
    REFERENCES [dbo].[tblQuestion] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblSectionDetail] CHECK CONSTRAINT [FK_tblSectionDetail_tblQuestion]
    GO
    
    ALTER TABLE [dbo].[tblSectionDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblSectionDetail_tblSection] FOREIGN KEY([SectionId])
    REFERENCES [dbo].[tblSection] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblSectionDetail] CHECK CONSTRAINT [FK_tblSectionDetail_tblSection]
    GO
    
    
    
    /****** Object:  Table [dbo].[tblStatus]    Script Date: 3/19/2017 12:36:23 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblStatus](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Status] [varchar](30) NOT NULL,
    	
     CONSTRAINT [PK_tblStatus] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    
    /****** Object:  Table [dbo].[tblSRRHistorical]    Script Date: 3/19/2017 12:42:30 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblSRRHistorical](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[SurveyUserId] [int] NOT NULL,		
    	[SurveyStatusId] [int] NOT NULL,	
    	[SurveyId] [int] NOT NULL,
    	[FiscalYear] [varchar](10) NOT NULL,
    	[ReviewPeriod] [varchar](50) NOT NULL,
    		
     CONSTRAINT [PK_tblSRRHistorical] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    
    ALTER TABLE [dbo].[tblSRRHistorical]  WITH CHECK ADD  CONSTRAINT [FK_tblSRRHistorical_tblStatus] FOREIGN KEY([SurveyStatusId])
    REFERENCES [dbo].[tblStatus] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblSRRHistorical] CHECK CONSTRAINT [FK_tblSRRHistorical_tblStatus]
    GO
    
    ALTER TABLE [dbo].[tblSRRHistorical]  WITH CHECK ADD  CONSTRAINT [FK_tblSRRHistorical_tblSurvey] FOREIGN KEY([SurveyId])
    REFERENCES [dbo].[tblSurvey] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblSRRHistorical] CHECK CONSTRAINT [FK_tblSRRHistorical_tblSurvey]
    GO
    
    ALTER TABLE [dbo].[tblSRRHistorical]  WITH CHECK ADD  CONSTRAINT [FK_tblSRRHistorical_tblSurveyUser] FOREIGN KEY([SurveyUserId])
    REFERENCES [dbo].[tblSurveyUser] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblSRRHistorical] CHECK CONSTRAINT [FK_tblSRRHistorical_tblSurveyUser]
    GO
    
    
    
    
    /****** Object:  Table [dbo].[tblAnswer]    Script Date: 3/19/2017 1:08:45 AM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblAnswer](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[QuestionDetailId] [int] NOT NULL,
    	[Weightage] [decimal](5, 2) NULL,
    	[Answer] [varchar](1000) NOT NULL,	
    	[SRRHistoricalId] [int] NOT NULL,
     CONSTRAINT [PK_tblAnswer] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[tblAnswer]  WITH CHECK ADD  CONSTRAINT [FK_tblAnswer_tblQuestionDetail] FOREIGN KEY([QuestionDetailId])
    REFERENCES [dbo].[tblQuestionDetail] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblAnswer] CHECK CONSTRAINT [FK_tblAnswer_tblQuestionDetail]
    GO
    
    ALTER TABLE [dbo].[tblAnswer]  WITH CHECK ADD  CONSTRAINT [FK_tblAnswer_tblSRRHistorical] FOREIGN KEY([SRRHistoricalId])
    REFERENCES [dbo].[tblSRRHistorical] ([Id])
    GO
    
    ALTER TABLE [dbo].[tblAnswer] CHECK CONSTRAINT [FK_tblAnswer_tblSRRHistorical]
    GO
    
    

    2. table insert script

    USE [XXX]
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblAttribute] ON 
    
    GO
    INSERT [dbo].[tblAttribute] ([Id], [Name], [Description]) VALUES (1, N'TEXT', N'Textbox')
    GO
    INSERT [dbo].[tblAttribute] ([Id], [Name], [Description]) VALUES (2, N'COMMENTS', N'Comments')
    GO
    INSERT [dbo].[tblAttribute] ([Id], [Name], [Description]) VALUES (3, N'ANSWER', N'Answer')
    GO
    
    SET IDENTITY_INSERT [dbo].[tblAttribute] OFF
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblAttributeListItem] ON 
    
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (1, N'Y', 3)
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (2, N'N', 3)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblAttributeListItem] OFF
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblQuestion] ON 
    
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (1, N'GL has advised a QC theme to completion', N'Advised a QCC' )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (2, N'GL has lead/coached a team jishuken to completion within group(problem solving tools)', N'Lead Jishuken Teams' )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (3, N'GL has lead/coached 2nd team jishuken to completion within group(problem solving tools)', N'Lead Jishuken Teams' )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (4, N'GL has lead/coached TBP', N'Lead / Coach TBPs' )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (5, N'GL has lead/coached 2nd TBP', N'Lead / Coach TBPs' )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (6, N'GL ensures TM development (physical plan)', null)
    GO
    
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (7, N'Has versatility plan (Multi-Functional Chart) in place', N'TM versatility improving' )
    GO
    
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (8, N'Meeting versatility plan (MFC targets Or Operator Release Form)', N'TM versatility improving' )
    GO
    
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (9, N'GL ensures TL development (physical plan)', null )
    GO
    
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (10, N'Has versatility plan (Multi-Functional Chart) in place', N'TL versatility improving' )
    GO
    
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (11, N'Meeting versatility plan (MFC targets Or Operator Release Form)', N'TL versatility improving' )
    GO
    
    SET IDENTITY_INSERT [dbo].[tblQuestion] OFF
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblSubQuestionDetail] ON 
    
    GO
    INSERT [dbo].[tblSubQuestionDetail] ([Id], [MainQuestionId], [SubQuestionId]) VALUES (1, 2, 3)
    GO
    INSERT [dbo].[tblSubQuestionDetail] ([Id], [MainQuestionId], [SubQuestionId]) VALUES (2, 4, 5)
    GO
    
    INSERT [dbo].[tblSubQuestionDetail] ([Id], [MainQuestionId], [SubQuestionId]) VALUES (3, 6, 7)
    GO
    INSERT [dbo].[tblSubQuestionDetail] ([Id], [MainQuestionId], [SubQuestionId]) VALUES (4, 6, 8)
    GO
    INSERT [dbo].[tblSubQuestionDetail] ([Id], [MainQuestionId], [SubQuestionId]) VALUES (5, 9, 10)
    GO
    INSERT [dbo].[tblSubQuestionDetail] ([Id], [MainQuestionId], [SubQuestionId]) VALUES (6, 9, 11)
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblSubQuestionDetail] OFF
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblQuestionDetail] ON 
    
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (1, 1, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (2, 2, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (3, 3, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (4, 4, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (5, 5, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (6, 7, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (7, 8, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (8, 10, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (9, 11, 3)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblQuestionDetail] OFF
    
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblWeightage] ON 
    
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (1, 1, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (2, 1, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (3, 2, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (4, 2, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (5, 3, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (6, 3, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (7, 4, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (8, 4, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (9, 5, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (10, 5, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    
    
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (11, 6, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (12, 6, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (13, 7, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (14, 7, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (15, 8, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (16, 8, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (17, 9, 1, CAST(1.00 AS Decimal(5, 2)))
    GO
    INSERT [dbo].[tblWeightage] ([Id], [QuestionDetailId], [AttributeListItemId], [Weightage]) VALUES (18, 9, 2, CAST(0.00 AS Decimal(5, 2)))
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblWeightage] OFF
    
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblTemplate] ON 
    
    GO
    INSERT [dbo].[tblTemplate] ([Id], [Name], [IsActive]) VALUES (1, N'AM QUESTIONNAIRE TEMPLATE', 1)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblTemplate] OFF
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblSurvey] ON 
    
    GO
    INSERT [dbo].[tblSurvey] ([Id], [Name], [TemplateId], [IsActive]) VALUES (1, N'AM QUESTIONNAIRE SURVEY', 1, 1)
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblSurvey] OFF
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblSection] ON 
    
    GO
    INSERT [dbo].[tblSection] ([Id], [Name]) VALUES (1, N'Ensure The Basics')
    GO
    INSERT [dbo].[tblSection] ([Id], [Name]) VALUES (2, N'Secure Conditions for Production')
    GO
    INSERT [dbo].[tblSection] ([Id], [Name]) VALUES (3, N'Manage STW')
    GO
    
    SET IDENTITY_INSERT [dbo].[tblSection] OFF
    
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblTemplateDetail] ON 
    
    GO
    INSERT [dbo].[tblTemplateDetail] ([Id],[TemplateId],[SectionId],[SurveyId]) VALUES (1, 1,  1, 1)
    GO
    INSERT [dbo].[tblTemplateDetail] ([Id],[TemplateId],[SectionId],[SurveyId]) VALUES (2, 1,  2, 1)
    GO
    INSERT [dbo].[tblTemplateDetail] ([Id],[TemplateId],[SectionId],[SurveyId]) VALUES (3, 1,  3, 1)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblTemplateDetail] OFF
    
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblDepartment] ON 
    
    GO
    INSERT [dbo].[tblDepartment] ([Id],[BUDepartmentName] ) VALUES (1, N'BODYWELD')
    GO
    INSERT [dbo].[tblDepartment] ([Id],[BUDepartmentName] ) VALUES (2, N'PAINT')
    GO
    INSERT [dbo].[tblDepartment] ([Id],[BUDepartmentName] ) VALUES (3, N'QUALITY')
    GO
    
    SET IDENTITY_INSERT [dbo].[tblDepartment] OFF
    
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblSurveyUser] ON 
    
    GO
    INSERT [dbo].[tblSurveyUser] ([Id],[EmployeeId], [FirstName], [LastName], [CostCenter], [DepartmentId] ) VALUES (1, N'24147', N'Jonathan', N'Risner', N'GW313', 1)
    GO
    INSERT [dbo].[tblSurveyUser] ([Id],[EmployeeId], [FirstName], [LastName], [CostCenter], [DepartmentId] ) VALUES (2, N'11672', N'Steve', N'Williams', N'MA540', 1)
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblSurveyUser] OFF
    
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblSectionDetail] ON 
    
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (1, 1, 1)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (2, 3, 2)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (3, 3, 3)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (4, 3, 4)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (5, 3, 5)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (6, 2, 6)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (7, 2, 7)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (8, 2, 8)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (9, 2, 9)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (10, 2, 10)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (11, 2, 11)
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblSectionDetail] OFF
    
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblStatus] ON 
    
    GO
    INSERT [dbo].[tblStatus] ([Id],[Status] ) VALUES (1, N'Complete')
    GO
    INSERT [dbo].[tblStatus] ([Id],[Status] ) VALUES (2, N'Pending')
    GO
    INSERT [dbo].[tblStatus] ([Id],[Status] ) VALUES (3, N'Inprogress')
    GO
    
    SET IDENTITY_INSERT [dbo].[tblStatus] OFF
    
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblSRRHistorical] ON 
    
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (1, 1,  1, 1, N'2017', N'4Q2016')
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (2, 2,  1, 1, N'2017', N'4Q2016')
    GO
    
    SET IDENTITY_INSERT [dbo].[tblSRRHistorical] OFF
    
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblAnswer] ON 
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (1, 1, CAST(1.00 AS Decimal(5, 2)), N'Y', 1)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (2, 2, CAST(1.00 AS Decimal(5, 2)), N'Y', 1)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (3, 3, CAST(1.00 AS Decimal(5, 2)), N'Y', 1)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (4, 4, CAST(1.00 AS Decimal(5, 2)), N'Y', 1)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (5, 5, CAST(1.00 AS Decimal(5, 2)), N'Y', 1)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (6, 6, CAST(1.00 AS Decimal(5, 2)), N'Y', 1)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (7, 7, CAST(1.00 AS Decimal(5, 2)), N'Y', 1)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (8, 8, CAST(1.00 AS Decimal(5, 2)), N'Y', 1)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (9, 9, CAST(1.00 AS Decimal(5, 2)), N'Y', 1)
    GO
    
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (10, 1, CAST(0.00 AS Decimal(5, 2)), N'N', 2)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (11, 2, CAST(1.00 AS Decimal(5, 2)), N'Y', 2)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (12, 3, CAST(0.00 AS Decimal(5, 2)), N'N', 2)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (13, 4, CAST(1.00 AS Decimal(5, 2)), N'Y', 2)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (14, 5, CAST(0.00 AS Decimal(5, 2)), N'N', 2)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (15, 6, CAST(1.00 AS Decimal(5, 2)), N'Y', 2)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (16, 7, CAST(0.00 AS Decimal(5, 2)), N'N', 2)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (17, 8, CAST(1.00 AS Decimal(5, 2)), N'Y', 2)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (18, 9, CAST(0.00 AS Decimal(5, 2)), N'N', 2)
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblAnswer] OFF
    
    GO
    

    Attached QUERY RESULTS based of new data


    Please need your inputs

    Thanks


    Vamshi Janagama

    Sunday, March 19, 2017 6:35 AM
  • See if the below one is what you are looking for.

    ;WITH QuestionLvlWeightage AS
    (
    	SELECT	QuestionDetailId, SUM(Weightage) Weightage
    	FROM	tblWeightage
    	GROUP BY	QuestionDetailId
    ), WeightageCalculatedTemp AS
    (
    	SELECT	Q.Id QuestionId, Q.ShortDescription
    	,		MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    			/* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    			SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    			*/
    	FROM	QuestionLvlWeightage W
    	JOIN		tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    	JOIN		tblQuestion Q ON QD.QuestionId = Q.Id
    	LEFT JOIN	tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    	LEFT JOIN	tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    	LEFT JOIN	QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    	GROUP BY	Q.Id, Q.ShortDescription
    ), WeightageCalculated AS
    (
    	SELECT	WC.*
    	FROM	WeightageCalculatedTemp WC
    	LEFT JOIN	(
    					SELECT	DISTINCT SubQuestionId 
    					FROM	tblSubQuestionDetail SubQn
    					JOIN	WeightageCalculatedTemp WCT
    					ON		SubQn.MainQuestionId	=	WCT.QuestionId
    				) SubQn		
    	ON			WC.QuestionId =	SubQn.SubQuestionId
    	WHERE	SubQn.SubQuestionId IS NULL	--	Exclude SubQuestions if they are included as part of Main Question
    )
    SELECT	SH.ReviewPeriod AS ReviewPeriod,  
    		SU.EmployeeId as GLEmployeeId,
    		SU.FirstName + ' ' + SU.LastName  as GroupLeaderName,
    		SU.CostCenter AS CostCenter,
            DP.BUDepartmentName AS Department,
    --		SE.Name as SectionName, 
    		Q.ShortDescription AS ShortDescription,
     		AN.Answer AS  Answer,
    		SUM(WC.TotalWeightage) TotalWeightage,
    		CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
    FROM	tblSRRHistorical SH 
    JOIN	tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
    JOIN	tbltemplate T ON  T.Id = S.TemplateId
    JOIN	tblTemplateDetail TD ON  TD.TemplateId = T.Id
    JOIN	tblSection SE ON  TD.SectionId = SE.Id
    JOIN	tblSectionDetail SED ON  SE.Id = SED.SectionId
    JOIN	tblQuestion Q ON  Q.Id = SED.QuestionId
    JOIN	tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    JOIN	tblAttribute AT ON  AT.Id = QD.AttributeId
    JOIN	tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    JOIN	tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
    --join	tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
    join	tblDepartment DP on SU.DepartmentId = DP.Id
    JOIN	tblStatus ST ON  ST.Id = SH.SurveyStatusId
    JOIN	WeightageCalculated WC ON  Q.Id = WC.QuestionId
    WHERE	S.Name = 'AM QUESTIONNAIRE SURVEY' 
    and		SH.ReviewPeriod = '4Q2016' 
    and		SU.EmployeeId = '24147' 
    and		SE.Name != 'Process' 
    and		Q.ShortDescription != 'Null' 
    group by	SH.ReviewPeriod,  
    			SU.EmployeeId,
    			SU.FirstName+' '+SU.LastName,
    			--SU.ReportsToName,
    			SU.CostCenter,
    			DP.BUDepartmentName,   
    			Q.ShortDescription,
    			AN.Answer

    Thanks!

    Monday, March 20, 2017 7:04 AM
  • Deepak,

    Thank you.

    1)   The query is fetching the right details but i have made a small change to make it work.   i.e. i have commented  " AN.Answer AS  Answer " in Select and " AN.Answer " group by conditions  because when i comment   " and SU.EmployeeId = '24147' " , the query returns the results for two employees but GROUP BY is not happening on "ShortDescription" for one of the employee.

    2)  In the working query from your previous post, I have included a sql satement condition and i have posted the question here in this thread.

    i have included  a sql satement condition to get the employee results order by ReviewPeriod (highlighted in bold). i.e. based on the current date, the sql statement will return the current and previous four reviewperiods. i have commented  " and  SU.EmployeeId = '24147' "  &  " and  SH.ReviewPeriod = '4Q2016'  " conditions

    The below UPDATED QUERY  is fetching the results but i want to display all the employee results order by ReviewPeriod.  Attached the query results by Review Period in the bottom. Need help on the requirement.

    Hope you have the  data  available based on yesterdays  DDL scripts (i.e. 1. table create script  and  2. table insert script).  please run the attached insert script to execute the below UPDATED QUERY

    use xxx
    
    
    SET IDENTITY_INSERT [dbo].[tblSRRHistorical] ON 
    
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (3, 1,  1, 1, N'2016', N'3Q2016')
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (4, 1,  1, 1, N'2016', N'2Q2016')
    GO
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (5, 1,  1, 1, N'2016', N'1Q2016')
    GO
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (6, 1,  1, 1, N'2016', N'4Q2015')
    GO
    
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (7, 2,  1, 1, N'2016', N'3Q2016')
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (8, 2,  1, 1, N'2016', N'2Q2016')
    GO
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (9, 2,  1, 1, N'2016', N'1Q2016')
    GO
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (10, 2,  1, 1, N'2016', N'4Q2015')
    GO
    
    SET IDENTITY_INSERT [dbo].[tblSRRHistorical] OFF
    
    GO
    
    
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblAnswer] ON 
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (19, 1, CAST(1.00 AS Decimal(5, 2)), N'Y', 3)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (20, 2, CAST(1.00 AS Decimal(5, 2)), N'Y', 3)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (21, 3, CAST(1.00 AS Decimal(5, 2)), N'Y', 3)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (22, 4, CAST(1.00 AS Decimal(5, 2)), N'Y', 3)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (23, 5, CAST(1.00 AS Decimal(5, 2)), N'Y', 3)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (24, 6, CAST(1.00 AS Decimal(5, 2)), N'Y', 3)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (25, 7, CAST(1.00 AS Decimal(5, 2)), N'Y', 3)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (26, 8, CAST(1.00 AS Decimal(5, 2)), N'Y', 3)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (27, 9, CAST(1.00 AS Decimal(5, 2)), N'Y', 3)
    GO
    
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (28, 1, CAST(0.00 AS Decimal(5, 2)), N'N', 4)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (29, 2, CAST(1.00 AS Decimal(5, 2)), N'Y', 4)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (30, 3, CAST(0.00 AS Decimal(5, 2)), N'N', 4)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (31, 4, CAST(1.00 AS Decimal(5, 2)), N'Y', 4)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (32, 5, CAST(0.00 AS Decimal(5, 2)), N'N', 4)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (33, 6, CAST(1.00 AS Decimal(5, 2)), N'Y', 4)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (34, 7, CAST(0.00 AS Decimal(5, 2)), N'N', 4)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (35, 8, CAST(1.00 AS Decimal(5, 2)), N'Y', 4)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (36, 9, CAST(0.00 AS Decimal(5, 2)), N'N', 4)
    GO
    
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (37, 1, CAST(1.00 AS Decimal(5, 2)), N'Y', 5)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (38, 2, CAST(1.00 AS Decimal(5, 2)), N'Y', 5)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (39, 3, CAST(1.00 AS Decimal(5, 2)), N'Y', 5)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (40, 4, CAST(1.00 AS Decimal(5, 2)), N'Y', 5)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (41, 5, CAST(1.00 AS Decimal(5, 2)), N'Y', 5)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (42, 6, CAST(1.00 AS Decimal(5, 2)), N'Y', 5)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (43, 7, CAST(1.00 AS Decimal(5, 2)), N'Y', 5)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (44, 8, CAST(1.00 AS Decimal(5, 2)), N'Y', 5)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (45, 9, CAST(1.00 AS Decimal(5, 2)), N'Y', 5)
    GO
    
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (46, 1, CAST(0.00 AS Decimal(5, 2)), N'N', 6)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (47, 2, CAST(1.00 AS Decimal(5, 2)), N'Y', 6)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (48, 3, CAST(0.00 AS Decimal(5, 2)), N'N', 6)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (49, 4, CAST(1.00 AS Decimal(5, 2)), N'Y', 6)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (50, 5, CAST(0.00 AS Decimal(5, 2)), N'N', 6)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (51, 6, CAST(1.00 AS Decimal(5, 2)), N'Y', 6)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (52, 7, CAST(0.00 AS Decimal(5, 2)), N'N', 6)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (53, 8, CAST(1.00 AS Decimal(5, 2)), N'Y', 6)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (54, 9, CAST(0.00 AS Decimal(5, 2)), N'N', 6)
    GO
    
    
    
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (55, 1, CAST(1.00 AS Decimal(5, 2)), N'Y', 7)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (56, 2, CAST(1.00 AS Decimal(5, 2)), N'Y', 7)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (57, 3, CAST(1.00 AS Decimal(5, 2)), N'Y', 7)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (58, 4, CAST(1.00 AS Decimal(5, 2)), N'Y', 7)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (59, 5, CAST(1.00 AS Decimal(5, 2)), N'Y', 7)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (60, 6, CAST(1.00 AS Decimal(5, 2)), N'Y', 7)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (61, 7, CAST(1.00 AS Decimal(5, 2)), N'Y', 7)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (62, 8, CAST(1.00 AS Decimal(5, 2)), N'Y', 7)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (63, 9, CAST(1.00 AS Decimal(5, 2)), N'Y', 7)
    GO
    
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (64, 1, CAST(0.00 AS Decimal(5, 2)), N'N', 8)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (65, 2, CAST(1.00 AS Decimal(5, 2)), N'Y', 8)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (66, 3, CAST(0.00 AS Decimal(5, 2)), N'N', 8)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (67, 4, CAST(1.00 AS Decimal(5, 2)), N'Y', 8)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (68, 5, CAST(0.00 AS Decimal(5, 2)), N'N', 8)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (69, 6, CAST(1.00 AS Decimal(5, 2)), N'Y', 8)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (70, 7, CAST(0.00 AS Decimal(5, 2)), N'N', 8)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (71, 8, CAST(1.00 AS Decimal(5, 2)), N'Y', 8)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (72, 9, CAST(0.00 AS Decimal(5, 2)), N'N', 8)
    GO
    
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (73, 1, CAST(1.00 AS Decimal(5, 2)), N'Y', 9)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (74, 2, CAST(1.00 AS Decimal(5, 2)), N'Y', 9)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (75, 3, CAST(1.00 AS Decimal(5, 2)), N'Y', 9)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (76, 4, CAST(1.00 AS Decimal(5, 2)), N'Y', 9)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (77, 5, CAST(1.00 AS Decimal(5, 2)), N'Y', 9)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (78, 6, CAST(1.00 AS Decimal(5, 2)), N'Y', 9)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (79, 7, CAST(1.00 AS Decimal(5, 2)), N'Y', 9)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (80, 8, CAST(1.00 AS Decimal(5, 2)), N'Y', 9)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (81, 9, CAST(1.00 AS Decimal(5, 2)), N'Y', 9)
    GO
    
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (82, 1, CAST(0.00 AS Decimal(5, 2)), N'N', 10)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (83, 2, CAST(1.00 AS Decimal(5, 2)), N'Y', 10)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (84, 3, CAST(0.00 AS Decimal(5, 2)), N'N', 10)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (85, 4, CAST(1.00 AS Decimal(5, 2)), N'Y', 10)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (86, 5, CAST(0.00 AS Decimal(5, 2)), N'N', 10)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (87, 6, CAST(1.00 AS Decimal(5, 2)), N'Y', 10)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (88, 7, CAST(0.00 AS Decimal(5, 2)), N'N', 10)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (89, 8, CAST(1.00 AS Decimal(5, 2)), N'Y', 10)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (100, 9, CAST(0.00 AS Decimal(5, 2)), N'N', 10)
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblAnswer] OFF
    
    GO
    
    
    
    
    
    
    
    
    
    

    UPDATED QUERY      

    USE XXX

    DECLARE @quartName nvarchar(10)
    -- return previous four quarters, each quarter is three months
    DECLARE @Quarter Table (quar nvarchar(10))
    DECLARE @Count int = 0
    DECLARE @Date date 

    SET @Date = getdate();
    WHILE @Count < 5
    BEGIN
     set @quartName = (SELECT
           CASE
            WHEN MONTH(@Date) BETWEEN 1  AND 3  THEN  '4Q' + convert(char(4), YEAR(@Date) - 1)
            WHEN MONTH(@Date) BETWEEN 4  AND 6  THEN  '1Q' + convert(char(4), YEAR(@Date) - 0)
            WHEN MONTH(@Date) BETWEEN 7  AND 9  THEN  '2Q'+ convert(char(4), YEAR(@Date) - 0) 
            WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN  '3Q' + convert(char(4), YEAR(@Date) - 0) 
           END AS Quarter )
     INSERT INTO @Quarter 
      VALUES 
     (@quartName)

      SET @Count = @Count + 1
        --previous quarter
      SET @Date = DATEADD(month, -3, @Date);
    END 

    ;WITH QuestionLvlWeightage AS
    (
    SELECT QuestionDetailId, SUM(Weightage) Weightage
    FROM tblWeightage
    GROUP BY QuestionDetailId
    ), WeightageCalculatedTemp AS
    (
    SELECT Q.Id QuestionId, Q.ShortDescription,
    MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    /* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    */
    FROM QuestionLvlWeightage W
    JOIN tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    JOIN tblQuestion Q ON QD.QuestionId = Q.Id
    LEFT JOIN tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    LEFT JOIN tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    LEFT JOIN QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    GROUP BY Q.Id, Q.ShortDescription
    ), WeightageCalculated AS
    (
    SELECT WC.*
    FROM WeightageCalculatedTemp WC
    LEFT JOIN (
    SELECT DISTINCT SubQuestionId 
    FROM tblSubQuestionDetail SubQn
    JOIN WeightageCalculatedTemp WCT
    ON SubQn.MainQuestionId = WCT.QuestionId
    ) SubQn
    ON WC.QuestionId = SubQn.SubQuestionId
    WHERE SubQn.SubQuestionId IS NULL -- Exclude SubQuestions if they are included as part of Main Question
    )
    SELECT SH.ReviewPeriod AS ReviewPeriod,  
    SU.EmployeeId as GLEmployeeId,
    SU.FirstName + ' ' + SU.LastName  as GroupLeaderName,
    SU.CostCenter AS CostCenter,
                    DP.BUDepartmentName AS Department,
            SE.Name as SectionName, 
    Q.ShortDescription AS ShortDescription, 
    --AN.Answer AS  Answer,
    SUM(WC.TotalWeightage) TotalWeightage,
    CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
    FROM tblSRRHistorical SH 
    JOIN tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
    JOIN tbltemplate T ON  T.Id = S.TemplateId
    JOIN tblTemplateDetail TD ON  TD.TemplateId = T.Id
    JOIN tblSection SE ON  TD.SectionId = SE.Id
    JOIN tblSectionDetail SED ON  SE.Id = SED.SectionId
    JOIN tblQuestion Q ON  Q.Id = SED.QuestionId
    JOIN tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    JOIN tblAttribute AT ON  AT.Id = QD.AttributeId
    JOIN tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    JOIN tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
    --join tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
    join tblDepartment DP on SU.DepartmentId = DP.Id
    JOIN tblStatus ST ON  ST.Id = SH.SurveyStatusId
    JOIN WeightageCalculated WC ON  Q.Id = WC.QuestionId
    WHERE S.Name = 'AM QUESTIONNAIRE SURVEY' 

    --and SH.ReviewPeriod = '4Q2016' 
    --and SU.EmployeeId = '24147'
    and       SH.ReviewPeriod in( SELECT quar FROM @Quarter)
      and SE.Name != 'Process' 
      and Q.ShortDescription != 'Null' 
    group by SH.ReviewPeriod,  
    SU.EmployeeId,
    SU.FirstName+' '+SU.LastName,
    SU.CostCenter,
    DP.BUDepartmentName,  
    SE.Name, 
    Q.ShortDescription
    --AN.Answer

    query results by Review Period : i want the query to return the results order by employee name, current ReviewPeriod followed by previous four ReviewPeriods.

    something like:

    4Q2016 24147 Jonathan Risner
    4Q2016 24147 Jonathan Risner
    4Q2016 24147 Jonathan Risner
    4Q2016 24147 Jonathan Risner
    4Q2016 24147 Jonathan Risner
    3Q2016 24147 Jonathan Risner
    3Q2016 24147 Jonathan Risner
    3Q2016 24147 Jonathan Risner
    3Q2016 24147 Jonathan Risner
    3Q2016 24147 Jonathan Risner
    2Q2016 24147 Jonathan Risner
    2Q2016 24147 Jonathan Risner
    2Q2016 24147 Jonathan Risner
    2Q2016 24147 Jonathan Risner
    2Q2016 24147 Jonathan Risner
    1Q2016 24147 Jonathan Risner
    1Q2016 24147 Jonathan Risner
    1Q2016 24147 Jonathan Risner
    1Q2016 24147 Jonathan Risner
    1Q2016 24147 Jonathan Risner
    4Q2015 24147 Jonathan Risner
    4Q2015 24147 Jonathan Risner
    4Q2015 24147 Jonathan Risner
    4Q2015 24147 Jonathan Risner
    4Q2015 24147 Jonathan Risner

    Need help on the requirement.

    Thanks


    Vamshi Janagama

    Tuesday, March 21, 2017 3:12 AM
  • Try adding this to the ORDER BY clause of the same query you posted and see if its what you are looking for.

    ORDER BY	GroupLeaderName, RIGHT(ReviewPeriod, 4) DESC, LEFT(ReviewPeriod, 2) DESC

    Thanks!

    Tuesday, March 21, 2017 5:29 AM
  • Deepak,

    1). The  ORDER BY clause is working. Thank you.

    2).  In the working query from your yesterdays post, I need to include condition to the below Working Main QUERY to return  the GroupLeader results based on SurveyStatus. thought to post the question here in this thread itself.

    For a Groupleader in every ReviewPeriod,  both  AM QUESTIONNAIRE SURVEY (SurveyId is 1) &  FMDS QUESTIONNAIRE SURVEY (SurveyId is 2) should be completed, then only Groupleader survey is considered as complete and which will set the surveystatusid for both records in tblSRRHistorical table to 1.  

    Refer the tblHistorical table records for reference. When observed  for both groupleaders, there are 2 records per ReviewPeriod with SurveyStatusId.

    Since there is a dependency on SurveyStatus based on SurveyStatusId ,  i have written a SQL query - if the SurveyStatusId for both records in tblSRRHistorical table is 1 then it returns a single record for a Groupleader for a ReviewPeriod with SurveyStatusId as 1  otherwise it returns 0.
     To Run the below SQL query  please execute the attached DDL insert script. Hope you have all the previous  data  available based on DDL scripts  which were executed till yesterday.

    use xxx
    
    
    SET IDENTITY_INSERT [dbo].[tblTemplate] ON 
    
    GO
    INSERT [dbo].[tblTemplate] ([Id], [Name], [IsActive]) VALUES (2, N'FMDS QUESTIONNAIRE TEMPLATE', 1)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblTemplate] OFF
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblSurvey] ON 
    
    GO
    INSERT [dbo].[tblSurvey] ([Id], [Name], [TemplateId], [IsActive]) VALUES (2, N'FMDS QUESTIONNAIRE SURVEY', 2, 1)
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblSurvey] OFF
    GO
    
    
    
    --as per the data inserted  for "Jonathan Risner" it will return  4Q2016 and 1Q2016  because in these reviewperiods both surveys surveysatusid is 1
    
    SET IDENTITY_INSERT [dbo].[tblSRRHistorical] ON 
    
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (11, 1,  1, 2, N'2017', N'4Q2016')
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (12, 1,  2, 2, N'2016', N'3Q2016')
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (13, 1,  3, 2, N'2016', N'2Q2016')
    GO
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (14, 1,  1, 2, N'2016', N'1Q2016')
    GO
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (15, 1,  2, 2, N'2016', N'4Q2015')
    GO
    
    
    --as per the data inserted  for "Steve Williams" it will return   4Q2016, 3Q2016 and 2Q2016  becuase in these reviewperiods both surveys surveysatusid is 1
    
    
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (16, 2,  1, 2, N'2017', N'4Q2016')
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (17, 2,  1, 2, N'2016', N'3Q2016')
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (18, 2,  1, 2, N'2016', N'2Q2016')
    GO
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (19, 2,  2, 2, N'2016', N'1Q2016')
    GO
    GO
    INSERT [dbo].[tblSRRHistorical] ([Id],[SurveyUserId],[SurveyStatusId],[SurveyId], [FiscalYear], [ReviewPeriod]) VALUES (20, 2,  3, 2, N'2016', N'4Q2015')
    GO
    
    SET IDENTITY_INSERT [dbo].[tblSRRHistorical] OFF
    
    GO

    SQL query

    use xxx

        select 
    (SU.FirstName+' '+SU.LastName) as GroupLeaderName,
    result.FiscalYear,
    result.ReviewPeriod,
    result.Status1 as SurveyStatusId
    from 
    tblSurveyUser SU 
    join
    (
       select 
       h.surveyuserid,
    H.FiscalYear,
    H.ReviewPeriod,
       Status1 = case 
    when (select count(1) from tblSRRHistorical tt where tt.surveyuserid  = h.surveyuserid and H.ReviewPeriod = tt.reviewperiod and tt.SurveyStatusId = 1) > 1 then 1
    else 0
    end

    from tblSRRHistorical  H
    where h.surveyid in (1,2)
    group by
    h.surveyuserid,
    H.FiscalYear,
    H.ReviewPeriod

    ) as result

    on  SU.Id = result.surveyuserid

    Attached the above SQL query results.


    So now in Main QUERY below i.e (working query posted by you yesterday) ,  i want to pass the SurveyStatusId  value  returned from the above SQL query to H.SurveyStatusId  in below where condition (highlighted in bold) so that  below Main QUERY will return only the GroupLeader Results for ReviewPeriods with SurveyStatusId  1

    How should i pass it. Please suggest if  my above approach is right?

         

    Working Main QUERY from Yesterday

          

    use xxx

    DECLARE @quartName nvarchar(10)
    -- return previous four quarters, each quarter is three months
    DECLARE @Quarter Table (quar nvarchar(10))
    DECLARE @Count int = 0
    DECLARE @Date date 

    SET @Date = getdate();
    WHILE @Count < 5
    BEGIN
     set @quartName = (SELECT
           CASE
            WHEN MONTH(@Date) BETWEEN 1  AND 3  THEN  '4Q' + convert(char(4), YEAR(@Date) - 1)
            WHEN MONTH(@Date) BETWEEN 4  AND 6  THEN  '1Q' + convert(char(4), YEAR(@Date) - 0)
            WHEN MONTH(@Date) BETWEEN 7  AND 9  THEN  '2Q'+ convert(char(4), YEAR(@Date) - 0) 
            WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN  '3Q' + convert(char(4), YEAR(@Date) - 0) 
           END AS Quarter )

     INSERT INTO @Quarter 
      VALUES 
     (@quartName)

      SET @Count = @Count + 1
        --previous quarter
      SET @Date = DATEADD(month, -3, @Date);
    END 


    ;WITH QuestionLvlWeightage AS
    (
    SELECT QuestionDetailId, SUM(Weightage) Weightage
    FROM tblWeightage
    GROUP BY QuestionDetailId
    ), WeightageCalculatedTemp AS
    (
    SELECT Q.Id QuestionId, Q.ShortDescription,

            cast(MAX(W.Weightage) AS INT) + cast(ISNULL(SUM(WThruSQ.Weightage), 0) AS INT) TotalWeightage

    --MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    /* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question

    */
    FROM QuestionLvlWeightage W
    JOIN tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    JOIN tblQuestion Q ON QD.QuestionId = Q.Id
    LEFT JOIN tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    LEFT JOIN tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    LEFT JOIN QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    GROUP BY Q.Id, Q.ShortDescription
    ), WeightageCalculated AS
    (
    SELECT WC.*
    FROM WeightageCalculatedTemp WC
    LEFT JOIN (
    SELECT DISTINCT SubQuestionId 
    FROM tblSubQuestionDetail SubQn
    JOIN WeightageCalculatedTemp WCT
    ON SubQn.MainQuestionId = WCT.QuestionId
    ) SubQn
    ON WC.QuestionId = SubQn.SubQuestionId
    WHERE SubQn.SubQuestionId IS NULL -- Exclude SubQuestions if they are included as part of Main Question
    )
    SELECT SH.ReviewPeriod AS ReviewPeriod,  
    SU.EmployeeId as GLEmployeeId,
    SU.FirstName + ' ' + SU.LastName  as GroupLeaderName,
    SU.CostCenter AS CostCenter,
            DP.BUDepartmentName AS Department,
       SE.Name as SectionName, 
    Q.ShortDescription AS ShortDescription, 
    --AN.Answer AS  Answer,
    SUM(WC.TotalWeightage) TotalWeightage,
    CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
    FROM tblSRRHistorical SH 
    JOIN tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
    JOIN tbltemplate T ON  T.Id = S.TemplateId
    JOIN tblTemplateDetail TD ON  TD.TemplateId = T.Id
    JOIN tblSection SE ON  TD.SectionId = SE.Id
    JOIN tblSectionDetail SED ON  SE.Id = SED.SectionId
    JOIN tblQuestion Q ON  Q.Id = SED.QuestionId
    JOIN tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    JOIN tblAttribute AT ON  AT.Id = QD.AttributeId
    JOIN tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    JOIN tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
    --join tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
    join tblDepartment DP on SU.DepartmentId = DP.Id
    JOIN tblStatus ST ON  ST.Id = SH.SurveyStatusId
    JOIN WeightageCalculated WC ON  Q.Id = WC.QuestionId
    WHERE S.Name = 'AM QUESTIONNAIRE SURVEY' 

    --and SH.ReviewPeriod = '4Q2016' 
    --and SU.EmployeeId = '24147' 
      and       SH.ReviewPeriod in( SELECT quar FROM @Quarter)
      and       SH.SurveyStatusId =
      and SE.Name != 'Process' 
      and Q.ShortDescription != 'Null' 
    group by SH.ReviewPeriod,  
    SU.EmployeeId,
    SU.FirstName+' '+SU.LastName,
    SU.CostCenter,
    DP.BUDepartmentName,  
    SE.Name, 
    Q.ShortDescription
    --AN.Answer
    ORDER BY GroupLeaderName, RIGHT(ReviewPeriod, 4) DESC, LEFT(ReviewPeriod, 2) DESC


    Please suggest if  my above approach is right?  Also please advise if there is any other easy approach to handle this.


    Thanks


    Vamshi Janagama

    Wednesday, March 22, 2017 4:07 AM
  • As it looks like one Survey historical id already point to respective user / review period, I tried to first bring all the required survey historical ids that are related to fully completed surveys (meaning both survey status ids to be 1 for respective user / review period). Then used those list of ids directly into the JOIN of MAIN query. This doesn't need to again explicitly check for user / review period as it gets indirectly mapped to survey historical id.

    See if this returns the expected results.

    DECLARE @quartName nvarchar(10)
     -- return previous four quarters, each quarter is three months
     DECLARE @Quarter Table (quar nvarchar(10))
     DECLARE @Count int = 0
     DECLARE @Date date 
    
     SET @Date = getdate();
     WHILE @Count < 5
     BEGIN
      set @quartName = (SELECT
            CASE
             WHEN MONTH(@Date) BETWEEN 1  AND 3  THEN  '4Q' + convert(char(4), YEAR(@Date) - 1)
             WHEN MONTH(@Date) BETWEEN 4  AND 6  THEN  '1Q' + convert(char(4), YEAR(@Date) - 0)
             WHEN MONTH(@Date) BETWEEN 7  AND 9  THEN  '2Q'+ convert(char(4), YEAR(@Date) - 0) 
             WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN  '3Q' + convert(char(4), YEAR(@Date) - 0) 
            END AS Quarter )
    
      INSERT INTO @Quarter 
       VALUES 
      (@quartName)
    
       SET @Count = @Count + 1
         --previous quarter
       SET @Date = DATEADD(month, -3, @Date);
     END 
    
    
     ;WITH QuestionLvlWeightage AS
     (
    SELECT
    QuestionDetailId, SUM(Weightage) Weightage
    FROM
    tblWeightage
    GROUP BY
    QuestionDetailId
     ), WeightageCalculatedTemp AS
     (
    SELECT
    Q.Id QuestionId, Q.ShortDescription,
    
    
            cast(MAX(W.Weightage) AS INT) + cast(ISNULL(SUM(WThruSQ.Weightage), 0) AS INT) TotalWeightage
    
    --MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    /* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    
    */
    FROM
    QuestionLvlWeightage W
    JOIN
    tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    JOIN
    tblQuestion Q ON QD.QuestionId = Q.Id
    LEFT JOIN
    tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    LEFT JOIN
    tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    LEFT JOIN
    QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    GROUP BY
    Q.Id, Q.ShortDescription
     ), WeightageCalculated AS
     (
    SELECT
    WC.*
    FROM
    WeightageCalculatedTemp WC
    LEFT JOIN
    (
    SELECT
    DISTINCT SubQuestionId 
    FROM
    tblSubQuestionDetail SubQn
    JOIN
    WeightageCalculatedTemp WCT
    ON
    SubQn.MainQuestionId
    = WCT.QuestionId
    ) SubQn
    
    ON
    WC.QuestionId =  SubQn.SubQuestionId
    WHERE
    SubQn.SubQuestionId IS NULL
    -- Exclude SubQuestions if they are included as part of Main Question
     ), FullCompletedSurveyIDs AS
    (
    	SELECT	H.Id
    	FROM	tblSRRHistorical H
    	JOIN	(
    				SELECT	SurveyUserId, ReviewPeriod
    				FROM	tblSRRHistorical
    				WHERE	SurveyID IN (1, 2)
    				AND		SurveyStatusID = 1	--	Survey Complete
    				GROUP BY	SurveyUserId, ReviewPeriod
    				HAVING	COUNT(1) = 2
    			) completedSurvey
    	ON		H.SurveyUserId	=	completedSurvey.SurveyUserId
    	AND		H.ReviewPeriod	=	completedSurvey.ReviewPeriod
    )
     SELECT SH.ReviewPeriod AS ReviewPeriod,  
    SU.EmployeeId as GLEmployeeId,
    SU.FirstName + ' ' + SU.LastName  as GroupLeaderName,
    SU.CostCenter AS CostCenter,
             DP.BUDepartmentName AS Department,
       SE.Name as SectionName, 
    Q.ShortDescription AS ShortDescription, 
    --AN.Answer AS  Answer,
    SUM(WC.TotalWeightage) TotalWeightage,
    CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
     FROM tblSRRHistorical SH 
     JOIN FullCompletedSurveyIDs CS ON SH.Id = CS.Id	--	this limits to only full completed surveys (for respective user / review period)
     JOIN tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
     JOIN tbltemplate T ON  T.Id = S.TemplateId
     JOIN tblTemplateDetail TD ON  TD.TemplateId = T.Id
     JOIN tblSection SE ON  TD.SectionId = SE.Id
     JOIN tblSectionDetail SED ON  SE.Id = SED.SectionId
     JOIN tblQuestion Q ON  Q.Id = SED.QuestionId
     JOIN tblQuestionDetail  QD ON QD.QuestionId = Q.Id
     JOIN tblAttribute AT ON  AT.Id = QD.AttributeId
     JOIN tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
     JOIN tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
     --join tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
     join tblDepartment DP on SU.DepartmentId = DP.Id
     JOIN tblStatus ST ON  ST.Id = SH.SurveyStatusId
     JOIN WeightageCalculated WC ON  Q.Id = WC.QuestionId
     WHERE S.Name = 'AM QUESTIONNAIRE SURVEY' 
    
     --and SH.ReviewPeriod = '4Q2016' 
     --and SU.EmployeeId = '24147' 
       and       SH.ReviewPeriod in( SELECT quar FROM @Quarter)
      --and       SH.SurveyStatusId =
       and SE.Name != 'Process' 
       and Q.ShortDescription != 'Null' 
     group by SH.ReviewPeriod,  
    SU.EmployeeId,
    SU.FirstName+' '+SU.LastName,
    
    SU.CostCenter,
    DP.BUDepartmentName,  
    SE.Name, 
    Q.ShortDescription
    --AN.Answer
    ORDER BY
    GroupLeaderName, RIGHT(ReviewPeriod, 4) DESC, LEFT(ReviewPeriod, 2) DESC

    Thanks!

    Wednesday, March 22, 2017 9:08 AM
  • Deepak,

    Thank you for the advise and solution to return the completed surveys results (for respective user / review period). Query is working as expected.

    I am working on the other survey query "FMDS QUESTIONNAIRE SURVEY" which is little different than "AM QUESTIONNAIRE SURVEY" query (i.e. Working query from your yesterdays post) and i will try to see if i can integrate both queries, so that one query can return the both completed surveys details for  groupleaders.

    I will post my questions tomorrow for help.  

    Thank you for your time.


    Vamshi Janagama

    Thursday, March 23, 2017 4:05 AM
  • Hi Deepak,

    I have constructed query to retrieve the results for "FMDS QUESTIONNAIRE SURVEY" for a group leader.  

    Note: For a Groupleader in every ReviewPeriod,  both  AM QUESTIONNAIRE SURVEY (SurveyId is 1) &  FMDS QUESTIONNAIRE SURVEY (SurveyId is 2) should be completed. 

    Need help  to integrate both queries  "AM QUESTIONNAIRE SURVEY" query (i.e. Working query from your previous post )  and  "FMDS QUESTIONNAIRE SURVEY"  query, so that one query can return the both completed surveys  details for  groupleader  something like  ORDERBY

    GroupLeaderName,  Survey,  RIGHT(ReviewPeriod, 4) DESC, LEFT(ReviewPeriod, 2) DESC

    So as per the data provided through  DDL scripts, i believe both "AM QUESTIONNAIRE SURVEY" and "FMDS QUESTIONNAIRE SURVEY" results for  Groupleader  "Steve Williams"  for Reviewperiods  "4Q2016", "2Q2016", "1Q2016"  should be returned.

    In "FMDS QUESTIONNAIRE SURVEY"  query,

    1). Most of the table joins are same as observed from "AM QUESTIONNAIRE SURVEY" query (i.e. Working query from your previous post ) but S.Name is different.

    2).   there is no need of  logic to calculate the TotalWeightage because this value is already configured and available in  tblSRRMasterLabel table where we can pull the value from it. (highlighted in bold)

    3).  ShortDescription   (i.e.  LabelDescription) is also configured and available in  tblSRRMasterLabel table.  (highlighted in bold)

    4).  I have used the Survey Complete logic based on SurveyStatusID from your previous post  Since there is a dependency on SurveyStatus based on SurveyStatusId for group leader

     FMDS QUESTIONNAIRE SURVEY  query 

    use xxx
    
    DECLARE @quartName NVARCHAR(10)
    -- return previous four quarters, each quarter is three months
    DECLARE @Quarter TABLE (quar NVARCHAR(10))
    DECLARE @Count INT = 0
    DECLARE @Date DATE 
    
    SET @Date = getdate();
    WHILE @Count < 5
    BEGIN
     SET @quartName = (SELECT
           CASE
    			WHEN MONTH(@Date) BETWEEN 1  AND 3  THEN  '4Q' + convert(CHAR(4), YEAR(@Date) - 1)
    			WHEN MONTH(@Date) BETWEEN 4  AND 6  THEN  '1Q' + convert(CHAR(4), YEAR(@Date) - 0)
    			WHEN MONTH(@Date) BETWEEN 7  AND 9  THEN  '2Q' + convert(CHAR(4), YEAR(@Date) - 0) 
    			WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN  '3Q' + convert(CHAR(4), YEAR(@Date) - 0) 
           END AS Quarter )
    
     INSERT INTO @Quarter 
      VALUES 
     (@quartName)
    
      SET @Count = @Count + 1
        --previous quarter
      SET @Date = DATEADD(month, -3, @Date);
    END
    ;with FullCompletedSurveyIDs AS
    (
    	SELECT	H.Id
    	FROM	tblSRRHistorical H
    	JOIN	(
    				SELECT	SurveyUserId, ReviewPeriod
    				FROM	tblSRRHistorical
    				WHERE	SurveyID IN (1, 2)
    				AND		SurveyStatusID = 1	--	Survey Complete
    				GROUP BY	SurveyUserId, ReviewPeriod
    				HAVING	COUNT(1) = 2
    			) completedSurvey
    	ON		H.SurveyUserId	=	completedSurvey.SurveyUserId
    	AND		H.ReviewPeriod	=	completedSurvey.ReviewPeriod
    )
    
    SELECT SH.ReviewPeriod AS ReviewPeriod,  
           SU.EmployeeId AS GLEmployeeId,	
    	   (SELECT SU.FirstName+' '+SU.LastName ) AS GroupLeaderName,	     
           SU.CostCenter AS CostCenter,
           DP.BUDepartmentName AS Department,  	        
    	   SE.Name AS  SectionName,	
    	   substring(SML.LabelDescription, 0, patindex('%=%', SML.LabelDescription )) AS ShortDescription,	  	 
    	   (SML.Points) AS TotalWeightage,
    	   cast(sum(AN.Weightage) AS INT) AS ActualPoints   
    FROM tblSRRHistorical SH 
        JOIN FullCompletedSurveyIDs CS ON SH.Id = CS.Id
    	Join tblSurvey S ON SH.SurveyId = S.Id
    	Join tbltemplate T ON  T.Id = S.TemplateId
    	Join tblTemplateDetail TD ON  TD.TemplateId = T.Id
    	Join tblSection SE ON  TD.SectionId = SE.Id
    	Join tblSectionDetail SED ON  SE.Id = SED.SectionId
    	Join tblQuestion Q ON  Q.Id = SED.QuestionId	  
    	Join tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    	Join tblAttribute AT ON  AT.Id = QD.AttributeId
    	join tblSRRMasterLabel SML ON SE.Id = SML.SectionId  and SML.AttributeId = AT.Id 
    	Join tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    	join tblSurveyUser SU ON  SU.Id = SH.SurveyUserId	
    	join tblDepartment DP ON SU.DepartmentId = DP.Id
    	join tblStatus ST ON  ST.Id = SH.SurveyStatusId	
    WHERE S.Name = 'FMDS QUESTIONNAIRE SURVEY' 
    and   SH.ReviewPeriod in( SELECT quar FROM @Quarter) 
    GROUP BY  SH.ReviewPeriod ,  
           SU.EmployeeId ,	
    	   SU.FirstName,SU.LastName ,		     
           SU.CostCenter,
           DP.BUDepartmentName,           
    	   SE.Name,
    	   SML.LabelDescription,
    	   SML.Points

    To Run the  FMDS QUESTIONNAIRE SURVEY  query   please execute the attached DDL -  create and insert scripts. Hope you have all the previous  data  available in the tables based on DDL scripts  which were executed till yesterday.

    1. Create Script

    use xxx
    
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblSRRMasterLabel](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[SectionId] [int] NULL,
    	[AttributeId] [int] NULL,
    	[LabelDescription] [varchar](150) NULL,
    	[MainQuestionId] [int] NULL,	
    	[Points] [int] NULL
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO


    2.  Insert Script

    use xxx
    
    
    SET IDENTITY_INSERT [dbo].[tblSection] ON 
    
    GO
    INSERT [dbo].[tblSection] ([Id], [Name]) VALUES (4, N'Activity Results KPI')
    GO
    INSERT [dbo].[tblSection] ([Id], [Name]) VALUES (5, N'Daily Activities')
    GO
    INSERT [dbo].[tblSection] ([Id], [Name]) VALUES (6, N'Sub KPI')
    GO
    INSERT [dbo].[tblSection] ([Id], [Name]) VALUES (7, N'Main KPI')
    GO
    INSERT [dbo].[tblSection] ([Id], [Name]) VALUES (8, N'Change Point Management')
    GO
    INSERT [dbo].[tblSection] ([Id], [Name]) VALUES (9, N'Focus Activity')
    GO
    
    SET IDENTITY_INSERT [dbo].[tblSection] OFF
    GO
    
    
    
    SET IDENTITY_INSERT [dbo].[tblAttribute] ON 
    
    GO
    INSERT [dbo].[tblAttribute] ([Id], [Name], [Description]) VALUES (4, N'SAFETY', N'Safety')
    GO
    INSERT [dbo].[tblAttribute] ([Id], [Name], [Description]) VALUES (5, N'QUALITY', N'Quality')
    GO
    INSERT [dbo].[tblAttribute] ([Id], [Name], [Description]) VALUES (6, N'PRODUCT', N'Product')
    GO
    INSERT [dbo].[tblAttribute] ([Id], [Name], [Description]) VALUES (7, N'COST', N'Cost')
    GO
    INSERT [dbo].[tblAttribute] ([Id], [Name], [Description]) VALUES (8, N'HRD', N'hrd')
    GO
    
    SET IDENTITY_INSERT [dbo].[tblAttribute] OFF
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblAttributeListItem] ON 
    
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (3, N'Y', 4)
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (4, N'N', 4)
    
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (5, N'Y', 5)
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (6, N'N', 5)
    
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (7, N'Y', 6)
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (8, N'N', 6)
    GO
    
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (9, N'Y', 7)
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (10, N'N', 7)
    GO
    
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (11, N'Y', 8)
    GO
    INSERT [dbo].[tblAttributeListItem] ([Id], [Name], [AttributeId]) VALUES (12, N'N', 8)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblAttributeListItem] OFF
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblTemplateDetail] ON 
    
    GO
    INSERT [dbo].[tblTemplateDetail] ([Id],[TemplateId],[SectionId],[SurveyId]) VALUES (4, 2,  4, 2)
    GO
    INSERT [dbo].[tblTemplateDetail] ([Id],[TemplateId],[SectionId],[SurveyId]) VALUES (5, 2,  5, 2)
    GO
    INSERT [dbo].[tblTemplateDetail] ([Id],[TemplateId],[SectionId],[SurveyId]) VALUES (6, 2,  6, 2)
    GO
    INSERT [dbo].[tblTemplateDetail] ([Id],[TemplateId],[SectionId],[SurveyId]) VALUES (7, 2,  7, 2)
    GO
    INSERT [dbo].[tblTemplateDetail] ([Id],[TemplateId],[SectionId],[SurveyId]) VALUES (8, 2,  8, 2)
    GO
    INSERT [dbo].[tblTemplateDetail] ([Id],[TemplateId],[SectionId],[SurveyId]) VALUES (9, 2,  9, 2)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblTemplateDetail] OFF
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblQuestion] ON 
    
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (12, N'Main KPIs', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (13, N'Main KPI visualize', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (14, N'clear targets specified', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (15, N'Main KPI to CM ', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (16, N'targets specified', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (17, N'spikes annotated', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (18, N'daily activity show the issue and activity', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (19, N'daily activity show the process impacted ', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (20, N'evidence of PDCA, dates', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (21, N'Activity Results track', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (22, N'Activities Results KPI ', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (23, N'trackingvisualize real time status', null )
    GO
    
    
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (24, N'all 4M change points', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (25, N'ensure process capability and identify KPI risk', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (26, N'follow up to ensure process capability', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (27, N'ensure process capability', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (28, N'Visualizes Group Process layout', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (29, N'Focus Activity linked to Sub KPI', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (30, N'Problem Solving tools ', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (31, N'Activity clearly outlined', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (32, N'Evidence of GL', null )
    GO
    INSERT [dbo].[tblQuestion] ([Id], [Question], [ShortDescription]) VALUES (33, N'Focus Activity moving KPI', null )
    GO
    
    SET IDENTITY_INSERT [dbo].[tblQuestion] OFF
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblQuestionDetail] ON 
    
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (10, 12, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (11, 12, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (12, 12, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (13, 12, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (14, 12, 8)
    GO
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (15, 13, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (16, 13, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (17, 13, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (18, 13, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (19, 13, 8)
    GO
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (20, 14, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (21, 14, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (22, 14, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (23, 14, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (24, 14, 8)
    GO
    
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (25, 15, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (26, 15, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (27, 15, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (28, 15, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (29, 15, 8)
    GO
    
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (30, 16, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (31, 16, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (32, 16, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (33, 16, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (34, 16, 8)
    GO
    
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (35, 17, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (36, 17, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (37, 17, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (38, 17, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (39, 17, 8)
    GO
    
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (40, 18, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (41, 18, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (42, 18, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (43, 18, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (44, 18, 8)
    GO
    
    
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (45, 19, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (46, 19, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (47, 19, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (48, 19, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (49, 19, 8)
    GO
    
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (50, 20, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (51, 20, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (52, 20, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (53, 20, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (54, 20, 8)
    GO
    
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (55, 21, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (56, 21, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (57, 21, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (58, 21, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (59, 21, 8)
    GO
    
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (60, 22, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (61, 22, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (62, 22, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (63, 22, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (64, 22, 8)
    GO
    
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (65, 23, 4)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (66, 23, 5)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (67, 23, 6)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (68, 23, 7)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (69, 23, 8)
    GO
    
    
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (70, 24, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (71, 25, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (72, 26, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (73, 27, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (74, 28, 3)
    
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (75, 29, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (76, 30, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (77, 31, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (78, 32, 3)
    GO
    INSERT [dbo].[tblQuestionDetail] ([Id], [QuestionId], [AttributeId]) VALUES (79, 33, 3)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblQuestionDetail] OFF
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblSectionDetail] ON 
    
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (12, 4, 12)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (13, 4, 13)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (14, 4, 14)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (15, 5, 15)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (16, 5, 16)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (17, 5, 17)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (18, 6, 18)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (19, 6, 19)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (20, 6, 20)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (21, 7, 21)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (22, 7, 22)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (23, 7, 23)
    GO
    
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (24, 8, 24)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (25, 8, 25)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (26, 8, 26)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (27, 8, 27)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (28, 8, 28)
    GO
    
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (29, 9, 29)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (30, 9, 30)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (31, 9, 31)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (32, 9, 32)
    GO
    INSERT [dbo].[tblSectionDetail] ([Id],[SectionId],[QuestionId]) VALUES (33, 9, 33)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblSectionDetail] OFF
    GO
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblSRRMasterLabel] ON 
    
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (1, 4, 4, N'Safety Main = 3',null ,3 )
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (2, 4, 5, N'Quality Main = 3',null , 3)
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (3, 4, 6, N'Production Main = 3',null ,3 )
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (4, 4, 7, N'Cost Main = 3',null , 3)
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (5, 4, 8, N'HRD Main = 3',null , 3)
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (6, 5, 4, N'Safety Sub = 3',null , 3)
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (7, 5, 5, N'Quality Sub = 3', null,3 )
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (8, 5, 6, N'Production Sub = 3',null , 3)
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (9, 5, 7, N'Cost Sub = 3', null, 3)
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (10, 5, 8, N'HRD Sub = 3',null , 3)
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (11, 6, 4, N'Safety Activity = 3', null, 3)
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (12, 6, 5, N'Quality Activity = 3', null, 3)
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (13, 6, 6, N'Production Activity = 3', null,3 )
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (14, 6, 7, N'Cost Activity = 3', null,3 )
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (15, 6, 8, N'HRD Activity = 3', null,3 )
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (16, 7, 4, N'Safety Activity KPI = 3', null,3 )
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (17, 7, 5, N'Quality Activity KPI = 3',null ,3 )
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (18, 7, 6, N'Production Activity KPI = 3',null ,3 )
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (19, 7, 7, N'Cost Activity KPI = 3',null ,3 )
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (20, 7, 8, N'HRD Activity KPI = 3',null ,3 )
    GO
    
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (21, 8, 3, N'Change Point Utilization = 5',null ,5 )
    GO
    INSERT [dbo].[tblSRRMasterLabel] ([Id],[SectionId],[AttributeId], [LabelDescription], [MainQuestionId], [Points]) VALUES (22, 9, 3, N'FMDS Focus Activity = 5',null ,5 )
    GO
    
    SET IDENTITY_INSERT [dbo].[tblSRRMasterLabel] OFF
    GO
    
    
    
    
    
    SET IDENTITY_INSERT [dbo].[tblAnswer] ON 
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (101, 10, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (102, 11, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (103, 12, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (104, 13, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (105, 14, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (106, 15, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (107, 16, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (108, 17, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (109, 18, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (110, 19, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (111, 20, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (112, 21, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (113, 22, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (114, 23, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (115, 24, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (116, 25, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (117, 26, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (118, 27, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (119, 28, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (120, 29, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (121, 30, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (122, 31, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (123, 32, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (124, 33, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (125, 34, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (126, 35, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (127, 36, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (128, 37, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (129, 38, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (130, 39, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (131, 40, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (132, 41, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (133, 42, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (134, 43, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (135, 44, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (136, 45, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    
    
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (137, 46, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (138, 47, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (139, 48, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (140, 49, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (141, 50, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (142, 51, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (143, 52, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (144, 53, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (145, 54, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (146, 55, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (147, 56, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (148, 57, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (149, 58, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (150, 59, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (151, 60, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (152, 61, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (153, 62, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (154, 63, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (155, 64, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (156, 65, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (157, 66, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (158, 67, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (159, 68, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (160, 69, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (161, 70, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (162, 71, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (163, 72, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (164, 73, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (165, 74, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (166, 75, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (167, 76, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (168, 77, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (169, 78, CAST(1.00 AS Decimal(5, 2)), N'Y', 16)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (170, 79, CAST(0.00 AS Decimal(5, 2)), N'N', 16)
    GO
    
    
    
    
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (171, 10, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (172, 11, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (173, 12, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (174, 13, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (175, 14, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (176, 15, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (177, 16, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (178, 17, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (179, 18, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (180, 19, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (181, 20, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (182, 21, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (183, 22, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (184, 23, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (185, 24, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (186, 25, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (187, 26, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (188, 27, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (189, 28, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (190, 29, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (191, 30, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (192, 31, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (193, 32, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (194, 33, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (195, 34, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (196, 35, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (197, 36, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (198, 37, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (199, 38, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (200, 39, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (201, 40, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (202, 41, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (203, 42, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (204, 43, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (205, 44, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (206, 45, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (207, 46, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (208, 47, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (209, 48, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (210, 49, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (211, 50, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (212, 51, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (213, 52, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (214, 53, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (215, 54, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (216, 55, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (217, 56, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (218, 57, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (219, 58, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (220, 59, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (221, 60, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (222, 61, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (223, 62, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (224, 63, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (225, 64, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (226, 65, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (227, 66, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (228, 67, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (229, 68, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (230, 69, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (231, 70, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (232, 71, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (233, 72, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (234, 73, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (235, 74, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (236, 75, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (237, 76, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (238, 77, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (239, 78, CAST(1.00 AS Decimal(5, 2)), N'Y', 19)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (240, 79, CAST(0.00 AS Decimal(5, 2)), N'N', 19)
    GO
    
    
    
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (241, 10, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (242, 11, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (243, 12, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (244, 13, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (245, 14, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (246, 15, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (247, 16, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (248, 17, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (249, 18, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (250, 19, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (251, 20, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (252, 21, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (253, 22, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (254, 23, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (255, 24, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (256, 25, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (257, 26, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (258, 27, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (259, 28, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (260, 29, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (261, 30, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (262, 31, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (263, 32, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (264, 33, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (265, 34, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (266, 35, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (267, 36, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (268, 37, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (269, 38, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (270, 39, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (271, 40, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (272, 41, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (273, 42, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (274, 43, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (275, 44, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (276, 45, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (277, 46, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (278, 47, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (279, 48, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (280, 49, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (281, 50, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (282, 51, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (283, 52, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (284, 53, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (285, 54, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (286, 55, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (287, 56, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (288, 57, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (289, 58, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (290, 59, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (291, 60, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (292, 61, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (293, 62, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (294, 63, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (295, 64, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (296, 65, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (297, 66, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (298, 67, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (299, 68, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (300, 69, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (301, 70, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (302, 71, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (303, 72, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (304, 73, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (305, 74, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (306, 75, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (307, 76, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (308, 77, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (309, 78, CAST(1.00 AS Decimal(5, 2)), N'Y', 18)
    GO
    INSERT [dbo].[tblAnswer] ([Id], [QuestionDetailId],  [Weightage], [Answer], [SRRHistoricalId]) VALUES (310, 79, CAST(0.00 AS Decimal(5, 2)), N'N', 18)
    GO
    
    
    SET IDENTITY_INSERT [dbo].[tblAnswer] OFF
    GO

    please let me know if any details are required 

    Thank you 


    Vamshi Janagama

    Friday, March 24, 2017 4:59 AM
  • As both the surveys has different logic to get the results, I left them individually and used UNION ALL to combine the results. Later in the end, I have added the check to ensure that respective Group leader and review period has both surveys for it to display into final result.
    Check if this works for your requirement and returns the expected results. Thanks!

    Saturday, March 25, 2017 6:55 AM
  • DECLARE @quartName nvarchar(10)
    -- return previous four quarters, each quarter is three months
    DECLARE @Quarter Table (quar nvarchar(10))
    DECLARE @Count int = 0
    DECLARE @Date date 
    
    SET @Date = getdate();
    WHILE @Count < 5
    BEGIN
    set @quartName = (SELECT
        CASE
            WHEN MONTH(@Date) BETWEEN 1  AND 3  THEN  '4Q' + convert(char(4), YEAR(@Date) - 1)
            WHEN MONTH(@Date) BETWEEN 4  AND 6  THEN  '1Q' + convert(char(4), YEAR(@Date) - 0)
            WHEN MONTH(@Date) BETWEEN 7  AND 9  THEN  '2Q'+ convert(char(4), YEAR(@Date) - 0) 
            WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN  '3Q' + convert(char(4), YEAR(@Date) - 0) 
        END AS Quarter )
    
    INSERT INTO @Quarter 
    VALUES 
    (@quartName)
    
    SET @Count = @Count + 1
        --previous quarter
    SET @Date = DATEADD(month, -3, @Date);
    END
    
    ;WITH QuestionLvlWeightage AS
    (
    	SELECT	QuestionDetailId, SUM(Weightage) Weightage
    	FROM	tblWeightage
    	GROUP BY	QuestionDetailId
    ), WeightageCalculatedTemp AS
    (
    	SELECT	Q.Id QuestionId, Q.ShortDescription
    	,		MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    			/* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    			SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    			*/
    	FROM	QuestionLvlWeightage W
    	JOIN		tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    	JOIN		tblQuestion Q ON QD.QuestionId = Q.Id
    	LEFT JOIN	tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    	LEFT JOIN	tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    	LEFT JOIN	QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    	GROUP BY	Q.Id, Q.ShortDescription
    ), WeightageCalculated AS
    (
    	SELECT	WC.*
    	FROM	WeightageCalculatedTemp WC
    	LEFT JOIN	(
    					SELECT	DISTINCT SubQuestionId 
    					FROM	tblSubQuestionDetail SubQn
    					JOIN	WeightageCalculatedTemp WCT
    					ON		SubQn.MainQuestionId	=	WCT.QuestionId
    				) SubQn		
    	ON			WC.QuestionId =	SubQn.SubQuestionId
    	WHERE	SubQn.SubQuestionId IS NULL	--	Exclude SubQuestions if they are included as part of Main Question
    ), FullCompletedSurveyIDs AS
    (
    	SELECT	H.Id
    	FROM	tblSRRHistorical H
    	JOIN	(
    				SELECT	SurveyUserId, ReviewPeriod
    				FROM	tblSRRHistorical
    				WHERE	SurveyID IN (1, 2)
    				AND		SurveyStatusID = 1	--	Survey Complete
    				GROUP BY	SurveyUserId, ReviewPeriod
    				HAVING	COUNT(1) = 2
    			) completedSurvey
    	ON		H.SurveyUserId	=	completedSurvey.SurveyUserId
    	AND		H.ReviewPeriod	=	completedSurvey.ReviewPeriod
    ), CombinedSurveyData AS
    (
    	--	AM QUESTIONNAIRE SURVEY
    	SELECT SH.ReviewPeriod AS ReviewPeriod,  
    	SU.EmployeeId as GLEmployeeId,
    	SU.FirstName + ' ' + SU.LastName  as GroupLeaderName,
    	S.Name	AS SurveyName,
    	SU.CostCenter AS CostCenter,
    				DP.BUDepartmentName AS Department,
    		SE.Name as SectionName, 
    	Q.ShortDescription AS ShortDescription, 
    	--AN.Answer AS  Answer,
    	SUM(WC.TotalWeightage) TotalWeightage,
    	CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
    		FROM tblSRRHistorical SH 
    		JOIN FullCompletedSurveyIDs CS ON SH.Id = CS.Id	--	this limits to only full completed surveys (for respective user / review period)
    		JOIN tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
    		JOIN tbltemplate T ON  T.Id = S.TemplateId
    		JOIN tblTemplateDetail TD ON  TD.TemplateId = T.Id
    		JOIN tblSection SE ON  TD.SectionId = SE.Id
    		JOIN tblSectionDetail SED ON  SE.Id = SED.SectionId
    		JOIN tblQuestion Q ON  Q.Id = SED.QuestionId
    		JOIN tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    		JOIN tblAttribute AT ON  AT.Id = QD.AttributeId
    		JOIN tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    		JOIN tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
    		--join tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
    		join tblDepartment DP on SU.DepartmentId = DP.Id
    		JOIN tblStatus ST ON  ST.Id = SH.SurveyStatusId
    		JOIN WeightageCalculated WC ON  Q.Id = WC.QuestionId
    		WHERE S.Name = 'AM QUESTIONNAIRE SURVEY' 
    
    		--and SH.ReviewPeriod = '4Q2016' 
    		--and SU.EmployeeId = '24147' 
    		and       SH.ReviewPeriod in( SELECT quar FROM @Quarter)
    		--and       SH.SurveyStatusId =
    		and SE.Name != 'Process' 
    		and Q.ShortDescription != 'Null' 
    		group by SH.ReviewPeriod,  
    	SU.EmployeeId,
    	SU.FirstName+' '+SU.LastName,
    	S.Name,
    	SU.CostCenter,
    	DP.BUDepartmentName,  
    	SE.Name, 
    	Q.ShortDescription
    	--AN.Answer
    	UNION ALL
    	--	FMDS QUESTIONNAIRE SURVEY
    	SELECT SH.ReviewPeriod AS ReviewPeriod,  
    			SU.EmployeeId AS GLEmployeeId,	
    			(SELECT SU.FirstName+' '+SU.LastName ) AS GroupLeaderName,	
    			S.Name SurveyName,     
    			SU.CostCenter AS CostCenter,
    			DP.BUDepartmentName AS Department,  	        
    			SE.Name AS  SectionName,	
    			substring(SML.LabelDescription, 0, patindex('%=%', SML.LabelDescription )) AS ShortDescription,	  	 
    			(SML.Points) AS TotalWeightage,
    			cast(sum(AN.Weightage) AS INT) AS ActualPoints   
    	FROM tblSRRHistorical SH 
    		JOIN FullCompletedSurveyIDs CS ON SH.Id = CS.Id
    		Join tblSurvey S ON SH.SurveyId = S.Id
    		Join tbltemplate T ON  T.Id = S.TemplateId
    		Join tblTemplateDetail TD ON  TD.TemplateId = T.Id
    		Join tblSection SE ON  TD.SectionId = SE.Id
    		Join tblSectionDetail SED ON  SE.Id = SED.SectionId
    		Join tblQuestion Q ON  Q.Id = SED.QuestionId	  
    		Join tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    		Join tblAttribute AT ON  AT.Id = QD.AttributeId
    		join tblSRRMasterLabel SML ON SE.Id = SML.SectionId  and SML.AttributeId = AT.Id 
    		Join tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    		join tblSurveyUser SU ON  SU.Id = SH.SurveyUserId	
    		join tblDepartment DP ON SU.DepartmentId = DP.Id
    		join tblStatus ST ON  ST.Id = SH.SurveyStatusId	
    	WHERE S.Name = 'FMDS QUESTIONNAIRE SURVEY' 
    	and   SH.ReviewPeriod in( SELECT quar FROM @Quarter) 
    	GROUP BY  SH.ReviewPeriod ,  
    			SU.EmployeeId ,	
    			SU.FirstName,SU.LastName ,	
    			S.Name,	     
    			SU.CostCenter,
    			DP.BUDepartmentName,           
    			SE.Name,
    			SML.LabelDescription,
    			SML.Points
    )
    SELECT	CSD.*
    FROM	CombinedSurveyData CSD
    JOIN	(
    			SELECT	GroupLeaderName, ReviewPeriod
    			FROM	CombinedSurveyData
    			GROUP BY	GroupLeaderName, ReviewPeriod
    			HAVING	COUNT(DISTINCT SurveyName) = 2
    		) BothSurveyDone
    ON		CSD.GroupLeaderName	=	BothSurveyDone.GroupLeaderName
    AND		CSD.ReviewPeriod	=	BothSurveyDone.ReviewPeriod
    ORDER BY GroupLeaderName, SurveyName, RIGHT(CSD.ReviewPeriod, 4) DESC, LEFT(CSD.ReviewPeriod, 2) DESC

    • Proposed as answer by José Diz Saturday, March 25, 2017 11:19 AM
    • Marked as answer by Vamshi Janagama Monday, March 27, 2017 1:16 AM
    Saturday, March 25, 2017 6:56 AM
  • Very useful, thanks !
    Saturday, March 25, 2017 7:04 AM
  • Deepak,

    Query returns expected results. Many Thanks.

    I have a another sql query "Course Details" which will retrieve the  CourseDetails for a groupleader  whose two surveys are completed  per ReviewPeriod.

    Need help  to integrate both queries  i.e. Working query from your previous post with UNION ALL   and  Course Details query, so that one query can return the both  completed surveys  details and Course details  for  groupleader  something like Results in the table  using ORDER BY based on the data provided through DDL.

    ORDER BY   GroupLeaderName, SurveyName, RIGHT(SH.ReviewPeriod , 4) DESC, LEFT(SH.ReviewPeriod , 2) DESC

    ReviewPeriod

    GLEmployeeId

    GroupLeaderName

    SurveyName

    4Q2016

    11672

    Steve Williams

    AM QUESTIONNAIRE SURVEY

    4Q2016

    11672

    Steve Williams

    AM QUESTIONNAIRE SURVEY

    4Q2016

    11672

    Steve Williams

    AM QUESTIONNAIRE SURVEY

    4Q2016

    11672

    Steve Williams

    FMDS QUESTIONNAIRE SURVEY

    4Q2016

    11672

    Steve Williams

    FMDS QUESTIONNAIRE SURVEY

    4Q2016

    11672

    Steve Williams

    FMDS QUESTIONNAIRE SURVEY

    4Q2016

    11672

    Steve Williams

    COURSEDATA

    4Q2016

    11672

    Steve Williams

    COURSEDATA

    4Q2016

    11672

    Steve Williams

    COURSEDATA

    So as per the data provided through  DDL scripts, the "Course Details"query results for  Groupleader  "Steve Williams"  for Reviewperiods  "4Q2016", "2Q2016", "1Q2016"  should be returned.

    Note:  in    FullCompletedSurveyIDs   sql condition with in WHERE condition  SurveyID IN (1, 2) is used. Replace it with SRRMasterId = 1  i.e. GL SRRMaster  because in tblCourseScore table i have referenced SRRHistoricalId.  I have created a table tblSRRMaster  and referenced SRRMasterId column in tblSRRHistorical table. please execute the attached DDL -  create and insert scripts.

    In "Course Details"  query,

    1). Introduced new tables which has all the Course Details for a groupleader per ReviewPeriod.

    2). there is no need of  logic to calculate the TotalWeightage because this value is already configured and available in  tblCourseLabel table where we can pull the value from it. (highlighted in bold)

    3). ShortDescription   (i.e.  CourseLabel Name) is also configured and available in  tblCourseLabel table.  (highlighted in bold)

    4).  ActualPoints  is also configured and available in  tblCourseScore table.  (highlighted in bold)

    5).  I have used the Survey Complete logic based on SurveyStatusID from your previous post  Since there is a dependency on SurveyStatus based on SurveyStatusId for group leader.

            

    USE  XXX

    -- "Course Details"  query --

    DECLARE @quartName nvarchar(10)
    -- return previous four quarters, each quarter is three months
    DECLARE @Quarter Table (quar nvarchar(10))
    DECLARE @Count int = 0
    DECLARE @Date date 

    SET @Date = getdate();
    WHILE @Count < 5
    BEGIN
     SET @quartName = (SELECT
           CASE
            WHEN MONTH(@Date) BETWEEN 1  AND 3  THEN  '4Q' + convert(char(4), YEAR(@Date) - 1)
            WHEN MONTH(@Date) BETWEEN 4  AND 6  THEN  '1Q' + convert(char(4), YEAR(@Date) - 0)
            WHEN MONTH(@Date) BETWEEN 7  AND 9  THEN  '2Q' + convert(char(4), YEAR(@Date) - 0) 
            WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN  '3Q' + convert(char(4), YEAR(@Date) - 0) 
           END AS Quarter )
     INSERT INTO @Quarter 
      VALUES 
     (@quartName)
      SET @Count = @Count + 1
        --previous quarter
      SET @Date = DATEADD(month, -3, @Date);
    END 
    ;with FullCompletedSurveyIDs AS
    (
    SELECT H.Id
    FROM tblSRRHistorical H
    JOIN (
    SELECT SurveyUserId, ReviewPeriod
    FROM tblSRRHistorical
    WHERE SRRMasterId = 1  
    AND SurveyStatusID = 1 -- Survey Complete
    GROUP BY SurveyUserId, ReviewPeriod
    HAVING COUNT(1) = 2
    ) completedSurvey
    ON H.SurveyUserId = completedSurvey.SurveyUserId
    AND H.ReviewPeriod = completedSurvey.ReviewPeriod
    )
    SELECT      SH.ReviewPeriod AS ReviewPeriod,  
    SU.EmployeeId AS GLEmployeeId,
    (SELECT SU.FirstName+' '+SU.LastName ) AS GroupLeaderName,
    'COURSEDATA' as SurveyName,
    SU.CostCenter AS CostCenter,
    DP.BUDepartmentName AS Department,  
    cl.Name as ShortDescription, 
    cl.points as  TotalWeightage, 
    sum( scr.CoursePoints) as ActualPoints
    FROM  tblCourseScore scr
        inner join tblCourseandLabelMapping clm ON  scr.CourseandLabelMappingId = clm.Id
        Inner join tblCourseLabel cl ON cl.Id = clm.CourseLabelId 
        inner join tblSRRHistorical sh ON scr.SRRHistoricalId = sh.Id
        join FullCompletedSurveyIDs CS ON SH.Id = CS.Id
        inner join tblSurveyUser su ON sh.SurveyUserId = su.Id
        join tblDepartment DP ON SU.DepartmentId = DP.Id
    WHERE 
         SH.ReviewPeriod in ( SELECT quar FROM @Quarter)

      --SH.ReviewPeriod = '4Q2016' 
      --and su.EmployeeId = '11672'
    and cl.srrmasterid = 1
    GROUP BY  SH.ReviewPeriod ,  
    SU.EmployeeId ,
    SU.FirstName,
    SU.LastName ,
    SU.CostCenter,
    DP.BUDepartmentName,  
    cl.Name, 
    cl.points
    ORDER BY   GroupLeaderName, SurveyName, RIGHT(SH.ReviewPeriod , 4) DESC, LEFT(SH.ReviewPeriod , 2) DESC

    To Run the Course Details  query   please execute the attached DDL -  create and insert scripts. Hope you have all the previous  data  available in the tables based on DDL scripts  which were executed till yesterday.

    1. Create Script

    USE XXX
    GO
    
    IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = 'SRRMasterId' 
                  AND object_id = OBJECT_ID('tblSRRHistorical'))
    BEGIN
    
    ALTER TABLE dbo.tblSRRHistorical ADD SRRMasterId INT  NULL
    
    END
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblSRRMaster](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](100) NOT NULL,	
    	[IsActive] [bit] NOT NULL,
    	
     CONSTRAINT [PK_tblSRRMaster] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblCourseLabel](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](100) NOT NULL,	
    	[SRRMasterId] [int] NOT NULL,	
    	[Points] [int] NULL,
     CONSTRAINT [PK_tblCourseLabel] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[tblCourseLabel]  WITH CHECK ADD  CONSTRAINT [FK_tblCourseLabel_tblSRRMaster] FOREIGN KEY([SRRMasterId])
    REFERENCES [dbo].[tblSRRMaster] ([Id])
    GO
    ALTER TABLE [dbo].[tblCourseLabel] CHECK CONSTRAINT [FK_tblCourseLabel_tblSRRMaster]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblCourse](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](100) NOT NULL,	
    	[IsActive] [bit] NOT NULL,
    	
     CONSTRAINT [PK_tblCourse] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tblCourseandLabelMapping](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[CourseId] [int] NOT NULL,
    	[CourseLabelId] [int] NOT NULL,	
     CONSTRAINT [PK_tblCourseandLabelMapping] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[tblCourseandLabelMapping]  WITH CHECK ADD  CONSTRAINT [FK_tblCourseandLabelMapping_tblCourse] FOREIGN KEY([CourseId])
    REFERENCES [dbo].[tblCourse] ([Id])
    GO
    ALTER TABLE [dbo].[tblCourseandLabelMapping] CHECK CONSTRAINT [FK_tblCourseandLabelMapping_tblCourse]
    GO
    ALTER TABLE [dbo].[tblCourseandLabelMapping]  WITH CHECK ADD  CONSTRAINT [FK_tblCourseandLabelMapping_tblCourseLabel] FOREIGN KEY([CourseLabelId])
    REFERENCES [dbo].[tblCourseLabel] ([Id])
    GO
    ALTER TABLE [dbo].[tblCourseandLabelMapping] CHECK CONSTRAINT [FK_tblCourseandLabelMapping_tblCourseLabel]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[tblCourseScore](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[CourseandLabelMappingId] [int] NOT NULL,
    	[CourseStatus] [nvarchar](100) NOT NULL,
    	[CoursePoints] [int] NOT NULL,
    	[SRRHistoricalId] [int] NOT NULL,	
    PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    

    2.  Insert Script

    use  XXX
    
    GO
    UPDATE [xxx].[dbo].[tblSRRHistorical] SET
                                          SRRMasterId = 1
    									  WHERE id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) 
    
    SET IDENTITY_INSERT [dbo].[tblSRRMaster] ON 
    GO
    INSERT [dbo].[tblSRRMaster] ([Id], [Name], [IsActive]) VALUES (1, N'GL SRRMaster', 1)
    GO
    SET IDENTITY_INSERT [dbo].[tblSRRMaster] OFF
    GO
    
    SET IDENTITY_INSERT [dbo].[tblCourseLabel] ON 
    GO
    INSERT [dbo].[tblCourseLabel] ([Id],[Name], [SRRMasterId],[Points]) VALUES (1, N'GL Supp. For TLBRD', 1 ,1 )
    GO
    INSERT [dbo].[tblCourseLabel] ([Id],[Name], [SRRMasterId],[Points]) VALUES (2, N'HR CORE 1-11', 1 ,11 )
    GO
    INSERT [dbo].[tblCourseLabel] ([Id],[Name], [SRRMasterId],[Points]) VALUES (3, N'THRM Modules 1-5', 1,5 )
    GO
    INSERT [dbo].[tblCourseLabel] ([Id],[Name], [SRRMasterId],[Points]) VALUES (4, N'Safety Training', 1 , 1)
    GO
    INSERT [dbo].[tblCourseLabel] ([Id],[Name], [SRRMasterId],[Points]) VALUES (5, N'Role Based Training', 1 , 1)
    GO
    INSERT [dbo].[tblCourseLabel] ([Id],[Name], [SRRMasterId],[Points]) VALUES (6, N'Change Point Training', 1 ,1 )
    GO
    INSERT [dbo].[tblCourseLabel] ([Id],[Name], [SRRMasterId],[Points]) VALUES (7, N'FMDS Training', 1 ,1 )
    GO
    INSERT [dbo].[tblCourseLabel] ([Id],[Name], [SRRMasterId],[Points]) VALUES (8, N'QCC Leader Training', 1 , 1)
    GO
    INSERT [dbo].[tblCourseLabel] ([Id],[Name], [SRRMasterId],[Points]) VALUES (9, N'QCC Advisor Training', 1 ,1 )
    GO
    INSERT [dbo].[tblCourseLabel] ([Id],[Name], [SRRMasterId],[Points]) VALUES (10, N'TPS Training', 1 , 1)
    GO
    INSERT [dbo].[tblCourseLabel] ([Id],[Name], [SRRMasterId],[Points]) VALUES (11, N'TBP Panel', 1 , 1)
    GO
    SET IDENTITY_INSERT [dbo].[tblCourseLabel] OFF
    GO
    
    SET IDENTITY_INSERT [dbo].[tblCourse] ON 
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 1, N'GL Support for TLBRD', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 2, N'Diversity 1', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 3, N'Diversity 2', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 4, N'Employment for Supervisors', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 5, N'Workplace Awareness', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 6, N'Response Plan Training', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 7, N'Practices Act Awareness', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 8, N'Positive Employee Relations', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 9, N'Update Training', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 10, N'Non-Core', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 11, N'Secure', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 12, N'SRR Communication', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 13, N'Lockout', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 14, N'Pathogens', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 15, N'One Service Way', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 16, N'Service Communication', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 17, N'THRM 1 (Intro to Communication)', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 18, N'THRM 2 (Group Communication)', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 19, N'THRM 3 (Everyday Interactions)', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 20, N'THRM 4 (Feedback and Coaching)', 1  )
    GO
    
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 21, N'THRM 5 (Conversation)', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 22, N'Decision Making', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 23, N'GL Role Base Training', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 24, N'Manufacturing FMDS for GL', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 25, N'QC Leader', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 26, N'QC Advisor', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 27, N'Introduction to TPS', 1  )
    GO
    INSERT [dbo].[tblCourse] ([Id],[Name], [IsActive]) VALUES ( 28, N'TBP Panel', 1  )
    GO
    SET IDENTITY_INSERT [dbo].[tblCourse] OFF
    GO
    
    SET IDENTITY_INSERT [dbo].[tblCourseandLabelMapping] ON 
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (1, 1,1 )
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (2, 2, 2)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (3, 3, 2)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (4, 4, 2)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (5, 5, 2)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (6, 7, 2)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (7, 8, 2)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (8, 9, 2)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (9, 10, 2)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (10, 11, 2)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (11, 12, 2)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (12, 14, 2)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (13, 16, 3)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (14, 17,3 )
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (15, 18,3)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (16, 19, 3)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (17, 20, 3)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (18, 21, 3)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (19, 22, 4)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (20, 23, 5)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (21, 24, 6)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (22, 24, 7)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (23, 25, 8)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (24, 26, 9)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (25, 27, 10)
    GO
    INSERT [dbo].[tblCourseandLabelMapping] ([Id],[CourseId],[CourseLabelId]) VALUES (26, 28, 11)
    GO
    SET IDENTITY_INSERT [dbo].[tblCourseandLabelMapping] OFF
    GO
    
    SET IDENTITY_INSERT [dbo].[tblCourseScore] ON 
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (1, 1,N'Complete', 1,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (2, 2,N'Complete',  1, 2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (3, 3,N'Complete', 1 ,2 )
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (4, 4,N'Complete', 1,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (5, 5,N'Complete', 1,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (6, 6,N'Complete', 1,2 )
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (7, 7,N'Complete', 1,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (8, 8,N'Complete', 1,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (9, 9,N'Complete',1 ,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (10, 10,N'Complete', 1,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (11, 11,N'Complete',1 ,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (12, 12,N'Complete', 1,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (13, 13,N'Complete',1 ,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (14, 14,N'Not Found', 0,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (15, 15,N'Not Found', 0,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (16, 16,N'Not Found', 0,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (17, 17,N'InComplete', 0,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (18, 18,N'InComplete', 0,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (19, 19,N'InComplete', 0,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (20, 20,N'InComplete',0,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (21, 21,N'InComplete', 0,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (22, 22,N'InComplete', 0,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (23, 23,N'InComplete', 0,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (24, 24,N'InComplete', 0,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (25, 25,N'InComplete',0 ,2)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (26, 26,N'InComplete', 0,2)
    GO
    
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (27, 1,N'Not Found', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (28, 2,N'Not Found', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (29, 3,N'Not Found', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (30, 4,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (31, 5,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (32, 6,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (33, 7,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (34, 8,N'InComplete',0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (35, 9,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (36, 10,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (37, 11,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (38, 12,N'InComplete', 0 ,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (39, 13,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (40, 14,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (41, 15,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (42, 16,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (43, 17,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (44, 18,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (45, 19,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (46, 20,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (47, 21,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (48, 22,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (49, 23,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (50, 24,N'InComplete', 0,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (51, 25,N'Complete', 1,8)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (52, 26,N'Complete', 1,8)
    GO
    
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (53, 1,N'Not Found', 0,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (54, 2,N'Not Found',0 ,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (55, 3,N'Complete',1 ,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (56, 4,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (57, 5,N'Complete',1 ,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (58, 6,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (59, 7,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (60, 8,N'Complete',1 ,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (61, 9,N'Complete',1 ,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (62, 10,N'Complete',1 ,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (63, 11,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (64, 12,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (65, 13,N'Complete', 1 ,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (66, 14,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (67, 15,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (68, 16,N'Complete',1 ,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (69, 17,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (70, 18,N'Complete',1 ,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (71, 19,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (72, 20,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (73, 21,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (74, 22,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (75, 23,N'Complete',1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (76, 24,N'Complete', 1,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (77, 25,N'InComplete',0 ,9)
    GO
    INSERT [dbo].[tblCourseScore] ([Id],[CourseandLabelMappingId] ,[CourseStatus],[CoursePoints], [SRRHistoricalId]) VALUES (78, 26,N'InComplete',0 ,9)
    GO
    
    SET IDENTITY_INSERT [dbo].[tblCourseScore] OFF
    GO
    

    Please let me know if any further details are required.

    Thank you for your time.


    Vamshi Janagama

    Monday, March 27, 2017 3:09 AM
  • Check if this works - tried to club all 3 queries into one using similar UNION ALL. 

    DECLARE @quartName nvarchar(10)
    -- return previous four quarters, each quarter is three months
    DECLARE @Quarter Table (quar nvarchar(10))
    DECLARE @Count int = 0
    DECLARE @Date date 
    
    SET @Date = getdate();
    WHILE @Count < 5
    BEGIN
    set @quartName = (SELECT
        CASE
            WHEN MONTH(@Date) BETWEEN 1  AND 3  THEN  '4Q' + convert(char(4), YEAR(@Date) - 1)
            WHEN MONTH(@Date) BETWEEN 4  AND 6  THEN  '1Q' + convert(char(4), YEAR(@Date) - 0)
            WHEN MONTH(@Date) BETWEEN 7  AND 9  THEN  '2Q'+ convert(char(4), YEAR(@Date) - 0) 
            WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN  '3Q' + convert(char(4), YEAR(@Date) - 0) 
        END AS Quarter )
    
    INSERT INTO @Quarter 
    VALUES 
    (@quartName)
    
    SET @Count = @Count + 1
        --previous quarter
    SET @Date = DATEADD(month, -3, @Date);
    END
    
    ;WITH QuestionLvlWeightage AS
    (
    	SELECT	QuestionDetailId, SUM(Weightage) Weightage
    	FROM	tblWeightage
    	GROUP BY	QuestionDetailId
    ), WeightageCalculatedTemp AS
    (
    	SELECT	Q.Id QuestionId, Q.ShortDescription
    	,		MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    			/* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    			SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    			*/
    	FROM	QuestionLvlWeightage W
    	JOIN		tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    	JOIN		tblQuestion Q ON QD.QuestionId = Q.Id
    	LEFT JOIN	tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    	LEFT JOIN	tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    	LEFT JOIN	QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    	GROUP BY	Q.Id, Q.ShortDescription
    ), WeightageCalculated AS
    (
    	SELECT	WC.*
    	FROM	WeightageCalculatedTemp WC
    	LEFT JOIN	(
    					SELECT	DISTINCT SubQuestionId 
    					FROM	tblSubQuestionDetail SubQn
    					JOIN	WeightageCalculatedTemp WCT
    					ON		SubQn.MainQuestionId	=	WCT.QuestionId
    				) SubQn		
    	ON			WC.QuestionId =	SubQn.SubQuestionId
    	WHERE	SubQn.SubQuestionId IS NULL	--	Exclude SubQuestions if they are included as part of Main Question
    ), FullCompletedSurveyIDs AS
    (
    	SELECT	H.Id
    	FROM	tblSRRHistorical H
    	JOIN	(
    				SELECT	SurveyUserId, ReviewPeriod
    				FROM	tblSRRHistorical
    				WHERE	SurveyID IN (1, 2)	--	SRRMasterId = 1--SurveyID IN (1, 2)
    				AND		SurveyStatusID = 1	--	Survey Complete
    				GROUP BY	SurveyUserId, ReviewPeriod
    				HAVING	COUNT(1) = 2
    			) completedSurvey
    	ON		H.SurveyUserId	=	completedSurvey.SurveyUserId
    	AND		H.ReviewPeriod	=	completedSurvey.ReviewPeriod
    ), CombinedSurveyData AS
    (
    	--	AM QUESTIONNAIRE SURVEY
    	SELECT SH.ReviewPeriod AS ReviewPeriod,  
    	SU.EmployeeId as GLEmployeeId,
    	SU.FirstName + ' ' + SU.LastName  as GroupLeaderName,
    	S.Name	AS SurveyName,
    	SU.CostCenter AS CostCenter,
    				DP.BUDepartmentName AS Department,
    		SE.Name as SectionName, 
    	Q.ShortDescription AS ShortDescription, 
    	--AN.Answer AS  Answer,
    	SUM(WC.TotalWeightage) TotalWeightage,
    	CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
    		FROM tblSRRHistorical SH 
    		JOIN FullCompletedSurveyIDs CS ON SH.Id = CS.Id	--	this limits to only full completed surveys (for respective user / review period)
    		JOIN tblSurvey S ON SH.SurveyId = S.Id --Need to check if reqd.?
    		JOIN tbltemplate T ON  T.Id = S.TemplateId
    		JOIN tblTemplateDetail TD ON  TD.TemplateId = T.Id
    		JOIN tblSection SE ON  TD.SectionId = SE.Id
    		JOIN tblSectionDetail SED ON  SE.Id = SED.SectionId
    		JOIN tblQuestion Q ON  Q.Id = SED.QuestionId
    		JOIN tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    		JOIN tblAttribute AT ON  AT.Id = QD.AttributeId
    		JOIN tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    		JOIN tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
    		--join tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
    		join tblDepartment DP on SU.DepartmentId = DP.Id
    		JOIN tblStatus ST ON  ST.Id = SH.SurveyStatusId
    		JOIN WeightageCalculated WC ON  Q.Id = WC.QuestionId
    		WHERE S.Name = 'AM QUESTIONNAIRE SURVEY' 
    
    		--and SH.ReviewPeriod = '4Q2016' 
    		--and SU.EmployeeId = '24147' 
    		and       SH.ReviewPeriod in( SELECT quar FROM @Quarter)
    		--and       SH.SurveyStatusId =
    		and SE.Name != 'Process' 
    		and Q.ShortDescription != 'Null' 
    		group by SH.ReviewPeriod,  
    	SU.EmployeeId,
    	SU.FirstName+' '+SU.LastName,
    	S.Name,
    	SU.CostCenter,
    	DP.BUDepartmentName,  
    	SE.Name, 
    	Q.ShortDescription
    	--AN.Answer
    	UNION ALL
    	--	FMDS QUESTIONNAIRE SURVEY
    	SELECT SH.ReviewPeriod AS ReviewPeriod,  
    			SU.EmployeeId AS GLEmployeeId,	
    			(SELECT SU.FirstName+' '+SU.LastName ) AS GroupLeaderName,	
    			S.Name SurveyName,     
    			SU.CostCenter AS CostCenter,
    			DP.BUDepartmentName AS Department,  	        
    			SE.Name AS  SectionName,	
    			substring(SML.LabelDescription, 0, patindex('%=%', SML.LabelDescription )) AS ShortDescription,	  	 
    			(SML.Points) AS TotalWeightage,
    			cast(sum(AN.Weightage) AS INT) AS ActualPoints   
    	FROM tblSRRHistorical SH 
    		JOIN FullCompletedSurveyIDs CS ON SH.Id = CS.Id
    		Join tblSurvey S ON SH.SurveyId = S.Id
    		Join tbltemplate T ON  T.Id = S.TemplateId
    		Join tblTemplateDetail TD ON  TD.TemplateId = T.Id
    		Join tblSection SE ON  TD.SectionId = SE.Id
    		Join tblSectionDetail SED ON  SE.Id = SED.SectionId
    		Join tblQuestion Q ON  Q.Id = SED.QuestionId	  
    		Join tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    		Join tblAttribute AT ON  AT.Id = QD.AttributeId
    		join tblSRRMasterLabel SML ON SE.Id = SML.SectionId  and SML.AttributeId = AT.Id 
    		Join tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    		join tblSurveyUser SU ON  SU.Id = SH.SurveyUserId	
    		join tblDepartment DP ON SU.DepartmentId = DP.Id
    		join tblStatus ST ON  ST.Id = SH.SurveyStatusId	
    	WHERE S.Name = 'FMDS QUESTIONNAIRE SURVEY' 
    	and   SH.ReviewPeriod in( SELECT quar FROM @Quarter) 
    	GROUP BY  SH.ReviewPeriod ,  
    			SU.EmployeeId ,	
    			SU.FirstName,SU.LastName ,	
    			S.Name,	     
    			SU.CostCenter,
    			DP.BUDepartmentName,           
    			SE.Name,
    			SML.LabelDescription,
    			SML.Points
    UNION ALL
    	--	Course Data
    	SELECT  SH.ReviewPeriod AS ReviewPeriod,  
    	SU.EmployeeId AS GLEmployeeId,
    	(SELECT SU.FirstName+' '+SU.LastName ) AS GroupLeaderName,
    	'COURSEDATA' as SurveyName,
    	SU.CostCenter AS CostCenter,
    	DP.BUDepartmentName AS Department,  
    	NULL SectionName,
    	cl.Name as ShortDescription, 
    	cl.points as  TotalWeightage, 
    	sum( scr.CoursePoints) as ActualPoints
    	FROM		tblCourseScore scr
    	inner join	tblCourseandLabelMapping clm ON  scr.CourseandLabelMappingId = clm.Id
    	Inner join	tblCourseLabel cl ON cl.Id = clm.CourseLabelId 
    	inner join	tblSRRHistorical sh ON scr.SRRHistoricalId = sh.Id
    	inner join	FullCompletedSurveyIDs CS ON SH.Id = CS.Id
    	inner join	tblSurveyUser su ON sh.SurveyUserId = su.Id
    	inner join	tblDepartment DP ON SU.DepartmentId = DP.Id
    	WHERE	SH.ReviewPeriod in ( SELECT quar FROM @Quarter)
    	and		cl.srrmasterid = 1
    	GROUP BY  SH.ReviewPeriod ,  
    	SU.EmployeeId ,
    	SU.FirstName,
    	SU.LastName ,
    	SU.CostCenter,
    	DP.BUDepartmentName,  
    	cl.Name, 
    	cl.points
    )
    SELECT	CSD.*
    FROM	CombinedSurveyData CSD
    LEFT JOIN	(
    				SELECT	GroupLeaderName, ReviewPeriod
    				FROM	CombinedSurveyData
    				WHERE	SurveyName	<>	'COURSEDATA'
    				GROUP BY	GroupLeaderName, ReviewPeriod
    				HAVING	COUNT(DISTINCT SurveyName) = 2
    		) BothSurveyDone
    ON		CSD.GroupLeaderName	=	BothSurveyDone.GroupLeaderName
    AND		CSD.ReviewPeriod	=	BothSurveyDone.ReviewPeriod
    WHERE	(
    			SurveyName	=	'COURSEDATA'
    		OR	(
    				SurveyName	<>	'COURSEDATA'
    			AND	BothSurveyDone.ReviewPeriod IS NOT NULL
    			)
    		)
    ORDER BY GroupLeaderName, CASE WHEN SurveyName <> 'COURSEDATA' THEN 0 ELSE 1 END, SurveyName, RIGHT(CSD.ReviewPeriod, 4) DESC, LEFT(CSD.ReviewPeriod, 2)
    Thanks!

    Tuesday, March 28, 2017 7:09 AM
  • Deepak,

    Thank you for query's and a lot of learning from them.  

    I will test the query.

    Thanks


    Vamshi Janagama

    Wednesday, March 29, 2017 2:44 PM
  • Deepak,  I have tested the query and it is working as expected.  Many Thanks

    But to get the results as per my need,  i changed the ORDER BY clause as like below

    ORDER BY 

    RIGHT(CSD.ReviewPeriod, 4) DESC, LEFT(CSD.ReviewPeriod, 2) DESC, GroupLeaderName, CASE WHEN SurveyName <> 'COURSEDATA' THEN 0 ELSE 1 END, SurveyName

    If need any changes needed in the query   or  if i need to add any other conditions, i will post questions for help.

    Thank you for your time and this is very useful and helping thread. 


    Vamshi Janagama

    Wednesday, March 29, 2017 5:33 PM
  • Hi Deepak,

    I have constructed query to retrieve the results for "TL SURVEY" for a team leader. 
    Note: For a team leader in every ReviewPeriod,  there is only one survey  i.e. "TL SURVEY" should be completed. 

    TL SURVEY  query 

    1). FullCompletedSurveyIDs  sql condition is not required because there is no dependency of two surveys as like group leader.

    TL SURVEY query
    
    
    use XXX
    
    
    DECLARE @quartName nvarchar(10)
    -- return previous four quarters, each quarter is three months
    DECLARE @Quarter Table (quar nvarchar(10))
    DECLARE @Count int = 0
    DECLARE @Date date 
    
    SET @Date = getdate();
    WHILE @Count < 5
    BEGIN
     set @quartName = (SELECT
           CASE
            WHEN MONTH(@Date) BETWEEN 1  AND 3  THEN  '4Q' + convert(char(4), YEAR(@Date) - 1)
            WHEN MONTH(@Date) BETWEEN 4  AND 6  THEN  '1Q' + convert(char(4), YEAR(@Date) - 0)
            WHEN MONTH(@Date) BETWEEN 7  AND 9  THEN  '2Q'+ convert(char(4), YEAR(@Date) - 0) 
            WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN  '3Q' + convert(char(4), YEAR(@Date) - 0) 
           END AS Quarter )
    
     INSERT INTO @Quarter 
      VALUES 
     (@quartName)
    
      SET @Count = @Count + 1
        --previous quarter
      SET @Date = DATEADD(month, -3, @Date);
    END 
    
    ;WITH QuestionLvlWeightage AS
    (
    SELECT
    QuestionDetailId, SUM(Weightage) Weightage
    FROM
    tblWeightage
    GROUP BY
    QuestionDetailId
    ), WeightageCalculatedTemp AS
    (
    SELECT
    Q.Id QuestionId, Q.ShortDescription,
    
        cast(MAX(W.Weightage) AS INT) + cast(ISNULL(SUM(WThruSQ.Weightage), 0) AS INT) TotalWeightage
    
    --MAX(W.Weightage) + ISNULL(SUM(WThruSQ.Weightage), 0) TotalWeightage-- ,Q.Id, SQD.SubQuestionId, W.Weightage MainQWeight, WThruSQ.Weightage SubQWeight
    /* MAX(W.Weightage) - or MIN can be used - to capture Weightage of MainQuestion only once and not multiple times
    SUM(WThruSQ.Weightage) - gets weightage from all subquestions associated to that main question
    */
    FROM
    QuestionLvlWeightage W
    JOIN
    tblQuestionDetail QD ON W.QuestionDetailId = QD.Id
    JOIN
    tblQuestion Q ON QD.QuestionId = Q.Id
    LEFT JOIN
    tblSubQuestionDetail SQD ON Q.Id = SQD.MainQuestionId
    LEFT JOIN
    tblQuestionDetail QDThruSQ ON SQD.SubQuestionId = QDThruSQ.Id
    LEFT JOIN
    QuestionLvlWeightage WThruSQ ON QDThruSQ.Id = WThruSQ.QuestionDetailId
    GROUP BY
    Q.Id, Q.ShortDescription
    ), WeightageCalculated AS
    (
    SELECT
    WC.*
    FROM
    WeightageCalculatedTemp WC
    LEFT JOIN
    (
    SELECT
    DISTINCT SubQuestionId 
    FROM
    tblSubQuestionDetail SubQn
    JOIN
    WeightageCalculatedTemp WCT
    ON
    SubQn.MainQuestionId
    = WCT.QuestionId
    ) SubQn
    
    ON
    WC.QuestionId =  SubQn.SubQuestionId
    WHERE
    SubQn.SubQuestionId IS NULL
    -- Exclude SubQuestions if they are included as part of Main Question
    )
    
    SELECT SH.ReviewPeriod AS ReviewPeriod,  
    SU.EmployeeId as TLEmployeeId,
    SU.FirstName + ' ' + SU.LastName  as TeamLeader,
    SU.CostCenter AS CostCenter,
    DP.BUDepartmentName AS Department,
    SE.Name as SectionName, 
    Q.ShortDescription AS ShortDescription, 
    --AN.Answer AS  Answer,
    SUM(WC.TotalWeightage) TotalWeightage,
    CAST(SUM(AN.Weightage) AS INT) AS ActualPoints
    FROM tblSRRHistorical SH 
    JOIN tblSurvey S ON SH.SurveyId = S.Id 
    JOIN tbltemplate T ON  T.Id = S.TemplateId
    JOIN tblTemplateDetail TD ON  TD.TemplateId = T.Id
    JOIN tblSection SE ON  TD.SectionId = SE.Id
    JOIN tblSectionDetail SED ON  SE.Id = SED.SectionId
    JOIN tblQuestion Q ON  Q.Id = SED.QuestionId
    JOIN tblQuestionDetail  QD ON QD.QuestionId = Q.Id
    JOIN tblAttribute AT ON  AT.Id = QD.AttributeId
    JOIN tblAnswer AN ON  AN.SRRHistoricalId = SH.Id and AN.QuestionDetailId=QD.Id
    JOIN tblSurveyUser SU ON  SU.Id = SH.SurveyUserId
    join tblMasBusinessUnit BU on SU.BusinessUnitId = BU.Id
    join tblDepartment DP on SU.DepartmentId = DP.Id
    JOIN tblStatus ST ON  ST.Id = SH.SurveyStatusId
    JOIN WeightageCalculated WC ON  Q.Id = WC.QuestionId
    WHERE S.Name = 'TL SURVEY' 
    
    --and SH.ReviewPeriod = '4Q2016' 
    --and SU.EmployeeId = '12378' 
    
      and  SH.ReviewPeriod in( SELECT quar FROM @Quarter)
      and  SH.SurveyStatusId = 1  
      and  SH.SRRMasterId = 2
      and  S.IsActive = 1
      and  Q.ShortDescription != 'Null' 
    
    group by SH.ReviewPeriod,  
    SU.EmployeeId,
    SU.FirstName+' '+SU.LastName,
    SU.CostCenter,
    DP.BUDepartmentName,  
    SE.Name, 
    Q.ShortDescription
    
    ORDER BY
    RIGHT(SH.ReviewPeriod, 4) DESC, LEFT(SH.ReviewPeriod, 2) DESC, TeamLeader, SurveyName


    I have a another sql query "Course Details" which will retrieve the  CourseDetails for a team leader per ReviewPeriod.

    Need help  to integrate both queries  i.e. using UNION ALL,  so that one query can return the completed "TL SURVEY" details and Course details  for  team leader per ReviewPeriod   using

    ORDER BY
    RIGHT(SH.ReviewPeriod , 4) DESC, LEFT(SH.ReviewPeriod , 2) DESC, TeamLeader, SurveyName

    I have not provided DDL Create/Insert scripts as it is same as like for group leader.

    Expecting the Results as like in the below format in table

    ReviewPeriod

    TLEmployeeId

    TeamLeader

    SurveyName

    4Q2016

    11712

    Sam Adam

    TL SURVEY

    4Q2016

    11712

    Sam Adam

    TL SURVEY

    4Q2016

    11712

    Sam Adam

    TL SURVEY

    4Q2016

    11712

    Sam Adam

    TL SURVEY

    4Q2016

    11712

    Sam Adam

    TL SURVEY

    4Q2016

    11712

    Sam Adam

    TL SURVEY

    4Q2016

    11712

    Sam Adam

    COURSEDATA

    4Q2016

    11712

    Sam Adam

    COURSEDATA

    4Q2016

    11712

    Sam Adam

    COURSEDATA

    4Q2016

    11712

    Sam Adam

    COURSEDATA

    4Q2016

    11712

    Sam Adam

    COURSEDATA


    In "Course Details"  query,

    1). FullCompletedSurveyIDs   sql condition is not required because there is no dependency of two surveys as like group leader.

    2). there is no need of  logic to calculate the TotalWeightage because this value is already configured and available in  tblCourseLabel table where we can pull the value from it. (highlighted in bold)

    3). ShortDescription   (i.e.  CourseLabel Name) is also configured and available in  tblCourseLabel table.  (highlighted in bold)

    4).  ActualPoints  is also configured and available in  tblCourseScore table.  (highlighted in bold)

    5).  Since there is no dependency on SurveyStatus based on SurveyStatusId for team leader, the Survey Complete logic based on SurveyStatusID from your previous post is not required for team leader  which means SH.SurveyStatusId = 1  will satisfy to check the Completed "TL Survey".

    Course Details query

    USE XXX -- "Course Details" query -- DECLARE @quartName nvarchar(10) -- return previous four quarters, each quarter is three months DECLARE @Quarter Table (quar nvarchar(10)) DECLARE @Count int = 0 DECLARE @Date date SET @Date = getdate(); WHILE @Count < 5 BEGIN SET @quartName = (SELECT CASE WHEN MONTH(@Date) BETWEEN 1 AND 3 THEN '4Q' + convert(char(4), YEAR(@Date) - 1) WHEN MONTH(@Date) BETWEEN 4 AND 6 THEN '1Q' + convert(char(4), YEAR(@Date) - 0) WHEN MONTH(@Date) BETWEEN 7 AND 9 THEN '2Q' + convert(char(4), YEAR(@Date) - 0) WHEN MONTH(@Date) BETWEEN 10 AND 12 THEN '3Q' + convert(char(4), YEAR(@Date) - 0) END AS Quarter ) INSERT INTO @Quarter VALUES (@quartName) SET @Count = @Count + 1 --previous quarter SET @Date = DATEADD(month, -3, @Date); END SELECT SH.ReviewPeriod AS ReviewPeriod, SU.EmployeeId AS TLEmployeeId, (SELECT SU.FirstName+' '+SU.LastName ) AS TeamLeader, 'COURSEDATA' as SurveyName, SU.CostCenter AS CostCenter, DP.BUDepartmentName AS Department, cl.Name as ShortDescription, cl.points as TotalWeightage, sum( scr.CoursePoints) as ActualPoints FROM tblCourseScore scr inner join tblCourseandLabelMapping clm ON scr.CourseandLabelMappingId = clm.Id Inner join tblCourseLabel cl ON cl.Id = clm.CourseLabelId inner join tblSRRHistorical sh ON scr.SRRHistoricalId = sh.Id inner join tblSurveyUser su ON sh.SurveyUserId = su.Id join tblDepartment DP ON SU.DepartmentId = DP.Id WHERE SH.ReviewPeriod in ( SELECT quar FROM @Quarter) --SH.ReviewPeriod = '4Q2016' --and su.EmployeeId = '11672' and cl.SRRMasterId = 2 GROUP BY SH.ReviewPeriod , SU.EmployeeId , SU.FirstName, SU.LastName , SU.CostCenter, DP.BUDepartmentName, cl.Name, cl.points ORDER BY RIGHT(SH.ReviewPeriod , 4) DESC, LEFT(SH.ReviewPeriod , 2) DESC, TeamLeader, SurveyName

    please let me know if  i need to provided more information.

    Thanks



    Vamshi Janagama

    Monday, April 03, 2017 4:42 AM