Loop 1 sql server

 USE [dw_antam]
GO
/****** Object:  StoredProcedure [dbo].[InsFactExpIntVolkerPlan]    Script Date: 05/10/2025 19:51:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   procedure [dbo].[InsFactExpIntVolkerPlan]
as
declare @project VARCHAR(20)
declare @factkey VARCHAR(100)

DECLARE cursor_project CURSOR FOR select distinct project FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]


open cursor_project 
fetch next from cursor_project INTO @project
while @@FETCH_STATUS = 0
BEGIN



Begin
    ---print @project+'     1'
     --print  @project 
    --INSERT INTO  [dw_antam].[dw].[FactExpVolkerPlanBackup] ([ProjectID])
    --SELECT @project AS [ProjectID]
    INSERT INTO  [dw_antam].[dw].[FactExpVolkerPlanBackup] ([FactKey],ProjectID
,[Year]
,[GeologyMapping]
,[Geodetic]
,[DrillSpacing25]
,[DrillSpacing50]
,[DrillSpacing100]
,[DrillSpacing200]
,[DrillDeep]
,[DrillInMine]
,[TestPitSpacing50]
,[TestPitSpacing100]
,[TestPitSpacing200]
,[TestPitSpacing50Hole]
,[TestPitSpacing100Hole]
,[TestPitSpacing200Hole]
,[Laboratorium]
,[EtlProcDate])
    SELECT (
    isnull(
  (SELECT  
concat([Project]
      ,[Year])[FactKey]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Pemetaan Geologi (ha)'
  AND Project = @project) 
  ,'hoho')
  )FactKey
  ,
  ISNULL(
  (SELECT  
[Project]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Pemetaan Geologi (ha)'
  AND Project = 'malut'
  )
  ,'hihi')
  [ProjectID]
  ,
  isnull(
  ( SELECT  
[Year]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Pemetaan Geologi (ha)'
  AND Project = @project
  )
  ,111)
  [Year]
      ,
  (SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Pemetaan Geologi (ha)'
  AND Project = @project) 
  
  [GeologyMapping]
      ,(
 SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Lintasan Grid (km)'
  AND Project = @project
  )[Geodetic]
      ,(SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Pengeboran spasi 25 (m)'
  AND Project = @project 
  )[DrillSpacing25]
      ,(SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Pengeboran spasi 50 (m)'
  AND Project = @project
  )[DrillSpacing50]
      ,(SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Pengeboran spasi 100 (m)'
  AND Project = @project
  )[DrillSpacing100]
      ,(SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Pengeboran spasi 200 (m)'
  AND Project = @project
  )[DrillSpacing200]
      ,(
 SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Pemboran Deep Drilling (m)'
  AND Project = @project
  )[DrillDeep]
      ,(
 SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Pemboran In Mine (m)'
  AND Project = @project
  )[DrillInMine]
      ,(SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Kedalaman Testpit spasi 50 (ttk)'
  AND Project = @project
  )[TestPitSpacing50]
      ,(SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Item = 'Kedalaman Testpit spasi 100 (ttk)'
  AND Project = @project
  )[TestPitSpacing100]
      ,(SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Project = @project
  AND Item = 'Kedalaman Testpit spasi 200 (ttk)')[TestPitSpacing200]
      ,(
 SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Project = @project
  AND Item = 'Kedalaman Testpit spasi 50 (m)')[TestPitSpacing50Hole]
      ,(
 SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Project = @project 
  AND Item = 'Kedalaman Testpit spasi 100 (m)')[TestPitSpacing100Hole]
      ,(
 SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Project = @project
  AND Item = 'Kedalaman Testpit spasi 200 (m)')[TestPitSpacing200Hole]
      ,(
 SELECT  
[Value]
  FROM [dw_staging_antam].[dbo].[StgExpInt01.VolkerPlan]
  WHERE [isDeleted] = 0
  AND Project = @project
  AND Item = 'Analisis Laboratorium (ct)')[Laboratorium]
      ,getdate()[GETDATE]

end


fetch next FROM cursor_project INTO @project
END
CLOSE cursor_project

DEALLOCATE cursor_project

Tweet