Cacher is the code snippet organizer for pro developers

---------------------Double the Weightage In Configuration Table-------------------------------
UPDATE dbo.AuditTypeXSubsectionWeightage
SET dbo.AuditTypeXSubsectionWeightage.Weightage = dbo.AuditTypeXSubsectionWeightage.Weightage * 2
WHERE dbo.AuditTypeXSubsectionWeightage.AuditTypeId IN (1,4)

--------------------Double the weightage in detail table-----------------------------------------------------

UPDATE asd
SET asd.SubsectionWeightage = asd.SubsectionWeightage * 2
	 FROM dbo.AuditSubsectionDetail asd
INNER JOIN dbo.Audit a ON asd.AuditId = a.Id AND a.AuditTypeId IN (1,4)

----------------------------------------------------------------------------------------------------------

SELECT *
FROM dbo.Audit a
WHERE a.AuditTypeId IN (1, 4)
	AND a.IsActive = 1
	AND a.AuditStatusId >= 6
----------------------------------------------------------------------------------------------------
DECLARE @rowCount INT = 0
DECLARE @i INT = 1
DECLARE @tblAuditId TABLE (
	Id INT identity(1, 1),
	AuditId BIGINT,
	CreatedBy NVARCHAR(50),
	CreatedDate DATETIME
	)

INSERT INTO @tblAuditId (
	AuditId,
	CreatedBy,
	CreatedDate
	)
SELECT a.Id,
	a.CreatedBy,
	a.CreatedDate
FROM dbo.Audit a
WHERE a.AuditTypeId IN (1, 4)
	AND a.IsActive = 1
	AND a.AuditStatusId >= 6

SELECT @rowCount = count(tai.Id)
FROM @tblAuditId tai

DECLARE @lpAuditId BIGINT
DECLARE @lpCreatedBy NVARCHAR(50)
DECLARE @lpCreatedDate DATETIME

WHILE @i <= @rowCount
BEGIN
	SELECT @lpAuditId = tai.AuditId,
		@lpCreatedBy = tai.CreatedBy,
		@lpCreatedDate = tai.CreatedDate
	FROM @tblAuditId tai
	WHERE tai.Id = @i

	DELETE
	FROM dbo.AuditSectionDetail 
	WHERE  AuditId = @lpAuditId

	INSERT INTO dbo.AuditSectionDetail (
		AuditId,
		SectionId,
		MaxScoreAtSection,
		IsActive,
		CreatedBy,
		CreatedDate
		)
	SELECT @lpAuditId,
		hmSection.Id AS SectionId,
		sum(asd.SubsectionWeightage) AS MaxScoreAtSection,
		1,
		@lpCreatedBy,
		@lpCreatedDate
	FROM dbo.AuditSubsectionDetail asd
	INNER JOIN dbo.HierarchyMaster hmSubSection ON asd.SubsectionId = hmSubSection.Id
		AND hmSubSection.IsActive = 1
	INNER JOIN dbo.HierarchyMaster hmSection ON hmSubSection.ParentId = hmSection.Id
		AND hmSection.IsActive = 1
	WHERE asd.AuditId = @lpAuditId
	GROUP BY hmSection.Id,
		hmSection.NAME

	EXEC dbo.USP_CalculateAuditScores @auditId = @lpAuditId,
		@logInUserEmpId = @lpCreatedBy

	SET @i = @i + 1
END