Untitled
unknown
plain_text
2 months ago
32 kB
2
Indexable
-- =============================================
-- AUTHOR: MUSKAN JAIN
-- CREATE DATE: 16 AUG 2023
-- DESCRIPTION : proc_rpt_GlobalStatusReport
-- TEST SCRIPT : EXEC proc_rpt_GlobalStatusReport
-- =============================================
Alter PROC [dbo].[proc_rpt_GlobalStatusReport]
(
@FyId as int = 4,
@MonthId as int=157,
@Conformity as int=null,
@roleID as int= null,
@Win_User VARCHAR(50) = 'muskan.jain'
)
AS
BEGIN
IF OBJECT_ID(N'tempdb..#tblResult', N'U') IS NOT NULL
BEGIN
DROP TABLE #tblResult
END
IF OBJECT_ID(N'tempdb..#tblResult2' , N'U') IS NOT NULL
BEGIN
DROP TABLE #tblResult2
END
Declare @month varchar(50) , @monthNo int
set @month = (Select Month from mst_fy_calendar where ID = @MonthId)
set @monthNo = (Select MonthNo from mst_fy_calendar where ID = @MonthId)
set @roleID = (SELECT TOP 1 RoleID FROM mst_user_mapping where WinUser=@Win_User ORDER BY RoleID ASC )
Create table #tblResult2
(
Pid int identity primary key,
ScheduleID int,
PlanID int,
ScheduleWeekId int,
ScheduleDate int,
ScheduleMonthNo int
)
insert into #tblResult2(ScheduleID,PlanID,ScheduleWeekId,ScheduleDate,ScheduleMonthNo)
Select S1.ScheduleID, S1.PlanID , S1.ScheduleWeekId, '0' , 0 from
(Select ScheduleID , PlanID , ScheduleWeekID from tran_auditPlan_schedule) S1
DECLARE @ScheduleCount int =(select count(1) from #tblResult2)
DECLARE @sepratedDate int , @sepratedMonth int , @ScheduledId int , @FullDate nvarchar(50)
while(@ScheduleCount > 0)
BEGIN
set @ScheduledId = (Select top 1 ScheduleID from #tblResult2 where Pid = @ScheduleCount)
set @FullDate = (select ScheduleWeekDate from tran_auditPlan_schedule where ScheduleID = @ScheduledId)
set @sepratedMonth = (Select Month(@FullDate))
set @sepratedDate = SUBSTRING(@FullDate, CHARINDEX('-', @FullDate) + 4, 2)
update #tblResult2
set ScheduleDate=@sepratedDate , ScheduleMonthNo = @sepratedMonth where ScheduleID=@ScheduledId
set @ScheduleCount = @ScheduleCount - 1
END
Create table #tblResult
(
Pid int identity primary key,
SerialNo int,
Month varchar(255),
KPI varchar(255),
PlantId int,
PlantName varchar(255),
Value varchar(255)
)
Create table #tblResult3
(
Pid int identity primary key,
PlantIdAuth int,
PlantNameAuth varchar(255)
)
if(@roleID = 1)
BEGIN
insert into #tblResult3(PlantIdAuth,PlantNameAuth)
select A1.PlantIdAuth, A1.PlantNameAuth from
(select distinct mpc.PlantID as PlantIdAuth, mpc.PlantName as PlantNameAuth from mst_plant as mpc) A1
END
ELSE
BEGIN
insert into #tblResult3(PlantIdAuth,PlantNameAuth)
select A2.PlantIdAuth, A2.PlantNameAuth from
(select distinct MUP.PlantID as PlantIdAuth , MP.PlantName as PlantNameAuth from mst_user_mapping as MUP
inner join mst_plant as MP on MUP.PlantID = MP.PlantID where MUP.WinUser = @Win_User) A2
END
--FOR FIRST KPI
insert into #tblResult(SerialNo,Month,KPI,PlantId,PlantName,Value)
select T1.SerialNo,T1.Month,T1.KPI,T1.PlantID, T1.PlantName, cast(0 as varchar) + '(' + cast(0 as varchar) + ')' from
(select distinct 1 as SerialNo,@month as Month,'Planned Audits-Current Month' as KPI ,mpc.PlantIDAuth as PlantID, mpc.PlantNameAuth as PlantName
from #tblResult3 as mpc) T1
declare @Plant_Id_For_First int
declare @tblCOuntForFirst int=(select count(1) from #tblResult)
declare @valueForFirst int
while(@tblCOuntForFirst>0)
begin
set @Plant_Id_For_First=(select top 1 PlantId from #tblResult where Pid=@tblCOuntForFirst)
set @valueForFirst=(select count(1) FROM #tblResult2 as TS
inner join tran_auditPlan as TP on TP.PlanID = TS.PlanID
inner join tran_auditProgram as TAP on TAP.AuditProgramID = TP.AuditProgramID
where TP.PlantID=@Plant_Id_For_First and TS.ScheduleMonthNo=@monthNo and TP.FyID = @FyId
and TP.IsActive=1 AND TAP.IsActive=1
--AND TP.FyID in (case when @FyId <> 0 then @FyId else TP.FyID end)
--AND TAP.ConformityID in (case when @Conformity <> 0 then @Conformity else TAP.ConformityID end)
)
update #tblResult
set Value=isnull(cast (@valueForFirst as varchar),cast(0 as varchar)) where PlantId=@Plant_Id_For_First
set @tblCOuntForFirst=@tblCOuntForFirst-1
end
--FOR Second KPI
insert into #tblResult(SerialNo,Month,KPI,PlantId,PlantName,Value)
select T2.SerialNo,T2.Month,T2.KPI,T2.PlantID, T2.PlantName, cast(0 as varchar) + '(' + cast(0 as varchar) + ')' from
(select distinct 2 as SerialNo,@month as Month,'Planned YTM' as KPI ,mpc.PlantIDAuth as PlantID, mpc.PlantNameAuth as PlantName
from #tblResult3 as mpc) T2
declare @Plant_Id_For_Second int
declare @tblCOuntForSecond int=(select count(1) from #tblResult)
declare @valueForSecond int , @updatedtblCOuntForSecond int
declare @pidcountForSecond int= (select top 1 Pid from #tblResult where KPI = 'Planned YTM')
while(@tblCOuntForSecond>0)
begin
set @Plant_Id_For_Second=(select top 1 PlantId from #tblResult where Pid=@tblCOuntForSecond)
set @valueForSecond=(select count(1) as CummulativeValue FROM #tblResult2 as TS
inner join tran_auditPlan as TP on TP.PlanID = TS.PlanID
inner join tran_auditProgram as TAP on TAP.AuditProgramID = TP.AuditProgramID
where TS.ScheduleMonthNo in(select distinct MonthNo from mst_fy_calendar where ID<=@MonthId and FY_ID = @FyId)
and TP.FyID = @FyId and TP.PlantID=@Plant_Id_For_Second and TP.IsActive=1 AND TAP.IsActive=1
--AND TP.FyID in (case when @FyId <> 0 then @FyId else TP.FyID end)
-- AND TAP.ConformityID in (case when @Conformity <> 0 then @Conformity else TAP.ConformityID end)
group by TP.PlantID
)
update #tblResult
set Value=isnull(cast (@valueForSecond as varchar),cast(0 as varchar)) where PlantId=@Plant_Id_For_Second and KPI = 'Planned YTM'
set @updatedtblCOuntForSecond=@tblCOuntForSecond-1
if(@updatedtblCOuntForSecond >= @pidcountForSecond)
begin
set @tblCOuntForSecond=@tblCOuntForSecond-1
end
else
begin
set @tblCOuntForSecond = 0
end
end
--FOR THIRD KPI
insert into #tblResult(SerialNo,Month,KPI,PlantId,PlantName,Value)
select T3.SerialNo,T3.Month,T3.KPI,T3.PlantID, T3.PlantName,0 from
(select distinct 3 as SerialNo,@month as Month,'Overdue Audits-Current Month' as KPI ,mpc.PlantIDAuth as PlantID, mpc.PlantNameAuth as PlantName
from #tblResult3 as mpc) T3
declare @Plant_Id_For_Third int
declare @tblCOuntForThird int=(select count(1) from #tblResult)
declare @valueForThird float , @updatedtblCOuntForThird int
declare @pidcountForThird int= (select top 1 Pid from #tblResult where KPI = 'Overdue Audits-Current Month')
while(@tblCOuntForThird>0)
begin
set @Plant_Id_For_Third=(select top 1 PlantId from #tblResult where Pid=@tblCOuntForThird)
set @valueForThird=(SELECT Count(*) FROM #tblResult2 TS
inner join tran_auditPlan as TP on TP.PlanID = TS.PlanID
inner join tran_auditProgram as TAP on TAP.AuditProgramID = TP.AuditProgramID
WHERE NOT EXISTS
(
SELECT TAS.PlanID FROM tran_auditResults TAS WHERE TS.ScheduleWeekID = TAS.PlanWeekID
and TS.PlanID = TAS.PlanID
) and TS.ScheduleMonthNo=@monthNo and TP.FyID = @FyId and TP.PlantID=@Plant_Id_For_Third and TP.IsActive=1 AND TAP.IsActive=1
-- AND TP.FyID in (case when @FyId <> 0 then @FyId else TP.FyID end)
--AND TAP.ConformityID in (case when @Conformity <> 0 then @Conformity else TAP.ConformityID end)
)
update #tblResult
set Value=isnull(cast (@valueForThird as varchar),cast(0 as varchar)) where PlantId=@Plant_Id_For_Third and KPI = 'Overdue Audits-Current Month'
set @updatedtblCOuntForThird=@tblCOuntForThird-1
if(@updatedtblCOuntForThird >= @pidcountForThird)
begin
set @tblCOuntForThird=@tblCOuntForThird-1
end
else
begin
set @tblCOuntForThird = 0
end
end
--FOR FOURTH KPI
insert into #tblResult(SerialNo,Month,KPI,PlantId,PlantName,Value)
select T4.SerialNo,T4.Month,T4.KPI,T4.PlantID, T4.PlantName,0 from
(select distinct 4 as SerialNo,@month as Month,'Overdue rate' as KPI ,mpc.PlantIDAuth as PlantID, mpc.PlantNameAuth as PlantName
from #tblResult3 as mpc) T4
declare @Plant_Id_For_Fourth int
declare @tblCOuntForFourth int=(select count(1) from #tblResult)
declare @valueForFourth float
declare @cummValueForFourth float
declare @finalCummValueFourth int , @updatedtblCOuntForFourth int
declare @pidcountForFourth int= (select top 1 Pid from #tblResult where KPI = 'Overdue rate')
while(@tblCOuntForFourth>0)
begin
set @Plant_Id_For_Fourth=(select top 1 PlantId from #tblResult where Pid=@tblCOuntForFourth)
set @valueForFourth=(SELECT Count(*) FROM #tblResult2 TS
inner join tran_auditPlan as TP on TP.PlanID = TS.PlanID
inner join tran_auditProgram as TAP on TAP.AuditProgramID = TP.AuditProgramID
WHERE NOT EXISTS
(
SELECT TAS.PlanID FROM tran_auditResults TAS WHERE TS.ScheduleWeekID = TAS.PlanWeekID
and TS.PlanID = TAS.PlanID
) and TS.ScheduleMonthNo=@monthNo and TP.FyID = @FyId and TP.PlantID=@Plant_Id_For_Fourth and TP.IsActive=1 AND TAP.IsActive=1
-- AND TP.FyID in (case when @FyId <> 0 then @FyId else TP.FyID end)
--AND TAP.ConformityID in (case when @Conformity <> 0 then @Conformity else TAP.ConformityID end)
)
set @cummValueForFourth=(SELECT Count(*) FROM #tblResult2 TS
inner join tran_auditPlan as TP on TP.PlanID = TS.PlanID
inner join tran_auditProgram as TAP on TAP.AuditProgramID = TP.AuditProgramID
WHERE TS.ScheduleMonthNo=@monthNo and TP.FyID = @FyId and TP.PlantID = @Plant_Id_For_Fourth and TP.IsActive=1 AND TAP.IsActive=1
--AND TP.FyID in (case when @FyId <> 0 then @FyId else TP.FyID end)
--AND TAP.ConformityID in (case when @Conformity <> 0 then @Conformity else TAP.ConformityID end)
)
if(@cummValueForFourth = 0)
begin
set @finalCummValueFourth = 0
end
else
begin
set @finalCummValueFourth = ((@valueForFourth / @cummValueForFourth) * 100 )
end
update #tblResult
set Value=isnull(cast (@finalCummValueFourth as varchar)+'%',cast(0 as varchar)) where PlantId=@Plant_Id_For_Fourth and KPI = 'Overdue rate'
set @updatedtblCOuntForFourth=@tblCOuntForFourth-1
if(@updatedtblCOuntForFourth >= @pidcountForFourth)
begin
set @tblCOuntForFourth=@tblCOuntForFourth-1
end
else
begin
set @tblCOuntForFourth = 0
end
end
--FOR FIFTH KPI
insert into #tblResult(SerialNo,Month,KPI,PlantId,PlantName,Value)
select T5.SerialNo,T5.Month,T5.KPI,T5.PlantID, T5.PlantName,0 from
(select distinct 5 as SerialNo,@month as Month,'Overdue Audits-YTM' as KPI ,mpc.PlantIDAuth as PlantID, mpc.PlantNameAuth as PlantName
from #tblResult3 as mpc) T5
declare @Plant_Id_For_Fifth int
declare @tblCOuntForFifth int=(select count(1) from #tblResult)
declare @valueForFifth float
declare @valueforthreeyearsfifth float
declare @sumvalueforfifth float , @updatedtblCOuntForFifth int
declare @pidcountForFifth int= (select top 1 Pid from #tblResult where KPI = 'Overdue Audits-YTM')
while(@tblCOuntForFifth>0)
begin
set @Plant_Id_For_Fifth=(select top 1 PlantId from #tblResult where Pid=@tblCOuntForFifth)
set @valueForFifth=(SELECT Count(*) FROM #tblResult2 TS
inner join tran_auditPlan as TP on TP.PlanID = TS.PlanID
inner join tran_auditProgram as TAP on TAP.AuditProgramID = TP.AuditProgramID
WHERE NOT EXISTS
(
SELECT TAS.PlanID FROM tran_auditResults TAS WHERE TS.ScheduleWeekID = TAS.PlanWeekID and TS.PlanID = TAS.PlanID
) and TS.ScheduleMonthNo in(select distinct MonthNo from mst_fy_calendar where ID<=@MonthId and FY_ID = @FyId)
and TP.FyID = @FyId and TP.PlantID=@Plant_Id_For_Fifth and TP.IsActive=1 AND TAP.IsActive=1
--AND TP.FyID in (case when @FyId <> 0 then @FyId else TP.FyID end)
--AND TAP.ConformityID in (case when @Conformity <> 0 then @Conformity else TAP.ConformityID end)
)
--set @valueforthreeyearsfifth = (SELECT Count(*) FROM tran_auditPlan_schedule TS
-- inner join tran_auditPlan as TP on TP.PlanID = TS.PlanID
-- inner join mst_fy_calendar as MC on MC.ID = TS.ScheduleWeekID
-- inner join tran_auditProgram as TAP on TAP.AuditProgramID = TP.AuditProgramID
-- WHERE NOT EXISTS
-- (
-- SELECT TAS.PlanID FROM tran_auditResults TAS WHERE TS.ScheduleWeekID = TAS.PlanWeekID and TS.PlanID = TAS.PlanID
-- ) and TP.FrequencyID = 6 and Mc.Month in(select Month from mst_fy_calendar where ID<=@MonthId and FY_ID = @FyId) and TP.PlantID=@Plant_Id_For_Fifth and TP.IsActive=1 AND TAP.IsActive=1
-- and TP.FyID in (select FY from mst_ThreeYearPlan_Config TYPC where TYPC.FY<@FyId and TYPC.PlanID = TP.PlanID)
-- AND TAP.ConformityID in (case when @Conformity <> 0 then @Conformity else TAP.ConformityID end))
set @sumvalueforfifth = @valueForFifth
update #tblResult
set Value=isnull(cast (@sumvalueforfifth as varchar),cast(0 as varchar)) where PlantId=@Plant_Id_For_Fifth and KPI = 'Overdue Audits-YTM'
set @updatedtblCOuntForFifth=@tblCOuntForFifth-1
if(@updatedtblCOuntForFifth >= @pidcountForFifth)
begin
set @tblCOuntForFifth=@tblCOuntForFifth-1
end
else
begin
set @tblCOuntForFifth = 0
end
end
--FOR SIXTH KPI
insert into #tblResult(SerialNo,Month,KPI,PlantId,PlantName,Value)
select T6.SerialNo,T6.Month,T6.KPI,T6.PlantID, T6.PlantName,0 from
(select distinct 6 as SerialNo,@month as Month,'Overdue rate-YTM' as KPI ,mpc.PlantIDAuth as PlantID, mpc.PlantNameAuth as PlantName
from #tblResult3 as mpc) T6
declare @Plant_Id_For_Sixth int
declare @tblCOuntForSixth int=(select count(1) from #tblResult)
declare @valueForSixth float
declare @cummValueForSixth float
declare @finalCummValueforYTMSixth int , @updatedtblCOuntForSixth int
declare @pidcountForSixth int= (select top 1 Pid from #tblResult where KPI = 'Overdue rate-YTM')
while(@tblCOuntForSixth>0)
begin
set @Plant_Id_For_Sixth=(select top 1 PlantId from #tblResult where Pid=@tblCOuntForSixth)
set @valueForSixth=(SELECT Count(*) FROM #tblResult2 TS
inner join tran_auditPlan as TP on TP.PlanID = TS.PlanID
inner join mst_fy_calendar as MC on MC.ID = TS.ScheduleWeekID
inner join tran_auditProgram as TAP on TAP.AuditProgramID = TP.AuditProgramID
WHERE NOT EXISTS
(
SELECT TAS.PlanID FROM tran_auditResults TAS WHERE TS.ScheduleWeekID = TAS.PlanWeekID and TS.PlanID = TAS.PlanID
) and TS.ScheduleMonthNo in(select distinct MonthNo from mst_fy_calendar where ID<=@MonthId and FY_ID = @FyId)
and TP.FyID = @FyId and TP.PlantID=@Plant_Id_For_Sixth and TP.IsActive=1 AND TAP.IsActive=1
--AND TP.FyID in (case when @FyId <> 0 then @FyId else TP.FyID end)
--AND TAP.ConformityID in (case when @Conformity <> 0 then @Conformity else TAP.ConformityID end)
)
set @cummValueForSixth=(SELECT Count(*) FROM #tblResult2 TS
inner join tran_auditPlan as TP on TP.PlanID = TS.PlanID
inner join mst_fy_calendar as MC on MC.ID = TS.ScheduleWeekID
inner join tran_auditProgram as TAP on TAP.AuditProgramID = TP.AuditProgramID
WHERE TS.ScheduleMonthNo in(select distinct MonthNo from mst_fy_calendar where ID<=@MonthId and FY_ID = @FyId)
and TP.FyID = @FyId and TP.PlantID = @Plant_Id_For_Sixth and TP.IsActive=1 AND TAP.IsActive=1
--AND TP.FyID in (case when @FyId <> 0 then @FyId else TP.FyID end)
--AND TAP.ConformityID in (case when @Conformity <> 0 then @Conformity else TAP.ConformityID end)
)
if(@cummValueForSixth = 0)
begin
set @finalCummValueforYTMSixth = 0
end
else
begin
set @finalCummValueforYTMSixth = ((@valueForSixth * 100) / @cummValueForSixth)
end
update #tblResult
set Value=isnull(cast (@finalCummValueforYTMSixth as varchar)+'%',cast(0 as varchar)) where PlantId=@Plant_Id_For_Sixth and KPI = 'Overdue rate-YTM'
set @updatedtblCOuntForSixth=@tblCOuntForSixth-1
if(@updatedtblCOuntForSixth >= @pidcountForSixth)
begin
set @tblCOuntForSixth=@tblCOuntForSixth-1
end
else
begin
set @tblCOuntForSixth = 0
end
end
--FOR SEVENTH KPI
insert into #tblResult(SerialNo,Month,KPI,PlantId,PlantName,Value)
select T7.SerialNo,T7.Month,T7.KPI,T7.PlantID, T7.PlantName,0 from
(select distinct 7 as SerialNo,@month as Month,'Projects Amount' as KPI ,mpc.PlantIDAuth as PlantID, mpc.PlantNameAuth as PlantName
from #tblResult3 as mpc) T7
declare @Plant_Id_For_Project int
declare @tblCOuntForProject int=(select count(1) from #tblResult)
declare @projectCount int , @updatedtblCOuntForProject int
declare @pidcountForProject int= (select top 1 Pid from #tblResult where KPI = 'Projects Amount')
while(@tblCOuntForProject>0)
begin
set @Plant_Id_For_Project=(select top 1 PlantId from #tblResult where Pid=@tblCOuntForProject)
set @projectCount=(select Count(*) from mst_mapping_plant_oem_project as pop where
pop.PlantID=@Plant_Id_For_Project)
update #tblResult
set Value=isnull(cast (@projectCount as varchar),cast(0 as varchar)) where PlantId=@Plant_Id_For_Project and KPI = 'Projects Amount'
set @updatedtblCOuntForProject=@tblCOuntForProject-1
if(@updatedtblCOuntForProject >= @pidcountForProject)
begin
set @tblCOuntForProject=@tblCOuntForProject-1
end
else
begin
set @tblCOuntForProject = 0
end
end
--FOR EIGHT KPI
insert into #tblResult(SerialNo,Month,KPI,PlantId,PlantName,Value)
select T8.SerialNo,T8.Month,T8.KPI,T8.PlantID, T8.PlantName,0 from
(select distinct 8 as SerialNo,@month as Month,'Product Audit in Place' as KPI ,mpc.PlantIDAuth as PlantID, mpc.PlantNameAuth as PlantName
from #tblResult3 as mpc) T8
declare @Plant_Id_For_Eight int
declare @tblCOuntForEight int=(select count(1) from #tblResult)
declare @project int
declare @productaudit int
declare @percentProductaudit int
while(@tblCOuntForEight>0)
begin
set @Plant_Id_For_Eight=(select top 1 PlantId from #tblResult where Pid=@tblCOuntForEight)
set @project=(select Count(*) from mst_mapping_plant_oem_project as pop where
pop.PlantID=@Plant_Id_For_Eight)
set @productaudit=(SELECT COUNT(*) FROM (SELECT POP.PlantID, POP.ProjectID, POP.OEMID
FROM tran_auditPlan AS TP INNER JOIN tran_auditProgram AS TAP ON TAP.AuditProgramID = TP.AuditProgramID
RIGHT JOIN mst_mapping_plant_oem_project AS POP ON TAP.PlantID = POP.PlantID AND TAP.ProjectID = POP.ProjectID
AND TAP.OEMID = POP.OEMID WHERE TAP.ConformityID = 1 AND TP.PlantID = 42
AND TP.FyID IN (CASE WHEN @FyId <> 0 THEN @FyId ELSE TP.FyID END) GROUP BY POP.PlantID, POP.ProjectID, POP.OEMID
) AS grouped_data)
if(@project = 0)
begin
set @percentProductaudit = 0
end
else
begin
set @percentProductaudit = ((@productaudit * 100) / @project)
end
update #tblResult
set Value=isnull(cast(@percentProductaudit as varchar) + '%',cast(0 as varchar)) where PlantId=@Plant_Id_For_Eight and KPI = 'Product Audit in Place'
set @tblCOuntForEight=@tblCOuntForEight-1
end
--FOR NINTH KPI
insert into #tblResult(SerialNo,Month,KPI,PlantId,PlantName,Value)
select T9.SerialNo,T9.Month,T9.KPI,T9.PlantID, T9.PlantName,0 from
(select distinct 9 as SerialNo,@month as Month,'Requalification plan in place' as KPI ,mpc.PlantIDAuth as PlantID, mpc.PlantNameAuth as PlantName
from #tblResult3 as mpc) T9
declare @Plant_Id_For_Requalification int
declare @tblCOuntForRequalification int=(select count(1) from #tblResult)
declare @projectRequalification int
declare @requalification int
declare @percentRequalification int
while(@tblCOuntForRequalification>0)
begin
set @Plant_Id_For_Requalification=(select top 1 PlantId from #tblResult where Pid=@tblCOuntForRequalification)
set @projectRequalification=(select Count(*) from mst_mapping_plant_oem_project as pop where
pop.PlantID=@Plant_Id_For_Requalification)
set @requalification=(Select Count(*) from mst_mapping_plant_oem_project as POP inner join
tran_auditProgram as TAP on TAP.PlantID = POP.PlantID and TAP.ProjectID = POP.ProjectID and TAP.OEMID = POP.OEMID inner join tran_auditPlan as TP on TAP.AuditProgramID = TP.AuditProgramID
where TAP.ConformityID = 2 and TP.PlantID = @Plant_Id_For_Requalification
AND TP.FyID in (case when @FyId <> 0 then @FyId else TP.FyID end))
if(@projectRequalification = 0)
begin
set @percentRequalification = 0
end
else
begin
set @percentRequalification = ((@requalification * 100) / @projectRequalification)
end
update #tblResult
set Value=isnull(cast(@percentRequalification as varchar) + '%',cast(0 as varchar)) where PlantId=@Plant_Id_For_Requalification and KPI = 'Requalification plan in place'
set @tblCOuntForRequalification=@tblCOuntForRequalification-1
end
--FOR TENTH KPI
insert into #tblResult(SerialNo,Month,KPI,PlantId,PlantName,Value)
select T10.SerialNo,T10.Month,T10.KPI,T10.PlantID, T10.PlantName,0 from
(select distinct 10 as SerialNo,@month as Month,'PSCR Linewalk plan in place' as KPI ,mpc.PlantIDAuth as PlantID, mpc.PlantNameAuth as PlantName
from #tblResult3 as mpc) T10
declare @Plant_Id_For_PSCR int
declare @tblCOuntForPSCR int=(select count(1) from #tblResult)
declare @projectPSCR int
declare @PSCR int
declare @percentPSCR int
while(@tblCOuntForPSCR>0)
begin
set @Plant_Id_For_PSCR=(select top 1 PlantId from #tblResult where Pid=@tblCOuntForPSCR)
set @projectPSCR=(select Count(*) from mst_mapping_plant_oem_project as pop where
pop.PlantID=@Plant_Id_For_PSCR)
set @PSCR=(Select Count(*) from mst_mapping_plant_oem_project as POP inner join
tran_auditProgram as TAP on TAP.PlantID = POP.PlantID and TAP.ProjectID = POP.ProjectID and TAP.OEMID = POP.OEMID inner join tran_auditPlan as TP on TAP.AuditProgramID = TP.AuditProgramID
where TAP.ConformityID = 3 and TP.PlantID = @Plant_Id_For_PSCR
AND TP.FyID in (case when @FyId <> 0 then @FyId else TP.FyID end))
if(@projectPSCR = 0)
begin
set @percentPSCR = 0
end
else
begin
set @percentPSCR = ((@PSCR * 100) / @projectPSCR)
end
update #tblResult
set Value=isnull(cast(@percentPSCR as varchar) + '%',cast(0 as varchar)) where PlantId=@Plant_Id_For_PSCR and KPI = 'PSCR Linewalk plan in place'
set @tblCOuntForPSCR=@tblCOuntForPSCR-1
end
--select * from @tblResult
DECLARE
@columns NVARCHAR(MAX) = '',
@sql NVARCHAR(MAX) = '';
SET @columns = STUFF((SELECT DISTINCT ','+QUOTENAME(PlantName) FROM #tblResult (nolock) FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')
set @sql = 'select * from (
select SerialNo,Month,KPI,PlantName,Value from #tblResult
)Tab1 PIVOT(max(Value) for PlantName IN ('+ @columns +')) as Tab2 order by SerialNo'
Execute(@sql)
END Editor is loading...
Leave a Comment