Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2009
    Posts
    18

    Unanswered: 1 to 5 rating between Intermediate and Senior SQL Developer

    Hi,

    I would like to get the forum's opinion on the attached .txt file that contains five stored procs. I would like a rating of 1 to 5, 1 being a very intermediate sql developer and 5 being a solid senior sql developer. The stored procs do not need to be compared to Expert or Redmond Guru status. The five sample stored procs are used for a forecasting, budgeting and actuals project management application that uses excel as a front end (due to user demands, faster design and rollout, and excel's built in formula engine) and sql 2005 as the back end. There are about 10 very advanced excel files that interact with sql 2005 to pull specific data based on user form selections for analysis. The description of the five stored procs is as follows:

    - uspGetForecastProject - makes an insert into a locking table to lock a project for data entry. It then obtains the max insert time stamp to pull the most current project forecast b.c all forecasting data is time stamped (datetime data type) so analysis can be done on how well project managers are forecasting from a historical perspective. It then pivots the normalized data and has the months go across the top.

    - uspGetYTDReportProjectionEstimated - gets the annual estimated to complete (EAC) numbers (actuals (timesheets) plus remaining forecast) depending on the month within the year and calculates a year to date EAC depending on the month. Cross joins are used to account for months where there isnt any data so when a user uses a pivot table, no mater what month they choose the annual and YTD numbers carry the totals to the next months. A ratio is used to join estimated forecast to timesheets in months that haven't closed financialy. So the ratio is multiplied against the forecast for a month that hasn't closed yet. Unions bring together the annual with ytd at a program and project level.

    - uspGetForecastProjectBalance - when a forecast is submitted to the database all of the individual resources (employees) working on the project are loaded into the staging table, where this balancing sproc is run to confirm that the cost and rates are working properly and no data gets into the db that could be corrupted. FTEs are units multiplied by a monthly cost to give grand totals. There is a lot of validation that goes on in the front end.

    - uspInsertDimensionValues - this is dynamic sql to load in various primary key data that are attributes to various business keys that is used as foreign keys in various fact tables.

    - uspTimeDetailProcess - this loads timesheet data weekly and is part of an ETL package (actually an ELT package considering the SSIS pack loads the data into a staging table where this sproc runs against the import and formats and loads it.) Someday I will develop out the SSIS pack so it truly becomes and ETL. Timeconstraints and I am not an SSIS guru.

    Thank you for your honest feedback and 1 is very intermediate and 5 is a solid senior developer.
    Attached Files Attached Files
    Last edited by ease2002; 02-13-11 at 22:36.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Post this as a plain text file and I might have a look.
    But I won't open a format that potentially contains viruses (and where the software to read costs quite an amount of money)

  3. #3
    Join Date
    Aug 2009
    Posts
    18
    I replaced the word doc with a txt. Thank you for your time.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's a rather odd request. And that's a lot of code.
    Pick out one sproc, and tell me the purpose of the exercise, and I'll consider looking through it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2009
    Posts
    18
    I am doing this to define a position for a sql 2005 developer. I will post the next sproc in a reply to this one due to the 10k max character post quantity. I really appreciate your time.

    - uspGetForecastProject - makes an insert into a locking table to lock a project for data entry. It then obtains the max insert time stamp to pull the most current project forecast b.c all forecasting data is time stamped (datetime data type) so analysis can be done on how well project managers are forecasting from a historical perspective. It then pivots the normalized data and has the months go across the top.

    Code:
    ALTER PROCEDURE [dbo].[uspGetForecastProject]
    	-- Add the parameters for the stored procedure here
    	@Project varchar(25),
    	@Source varchar(50),
    	@Year Int,
    	@UserID varchar(8)
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	Declare @MaxDateTime DateTime
    	
    	If @Source = 'Forecast'	
    		Begin
    			INSERT INTO [LKUP_FRCST_LCK]
    				   ([PROJ_CD]
    				   ,[PROD_ID]
    				   ,[INSRT_TS]
    				   ,YR_NBR)
    			 VALUES
    				   (@Project
    				   ,@UserID
    				   ,GetDate()
    				   ,@Year)
    		End		
    
    	Set @MaxDateTime = (Select Max(INSRT_TS) 
    						From FRCST
    						Where rtrim(PROJ_CD) = @Project
    						And YR_NBR = @Year And SRC_FRCST_TXT = @Source)
    			
    	SELECT SRC_FRCST_TXT, PGM_CD, PROJ_CD, RSRC_FULL_NM, RSRC_SHRT_NM, RSRC_TYP_CD, RSRC_ROLE_TXT, 
    		   [Owner], ACTUL_STRT_DT, TENT_STRT_DT,
    		   USER_COMMT_TXT, MGR_NM, RESP_CD, DEPT_NM, VEND_NM, UPDT_USER_ID, INSRT_TS, 
    		   [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]		   
    			
        FROM 
    		(Select SRC_FRCST_TXT, rtrim(PGM_CD) as PGM_CD, rtrim(PROJ_CD) as PROJ_CD, RSRC_FULL_NM, rtrim(a.RSRC_SHRT_NM) as RSRC_SHRT_NM, rtrim(a.RSRC_TYP_CD) as RSRC_TYP_CD, RSRC_ROLE_TXT, 
    		   '' as [Owner], ACTUL_STRT_DT, TENT_STRT_DT,
    		   USER_COMMT_TXT, MGR_NM, a.RESP_CD, DEPT_NM, VEND_NM, UPDT_USER_ID, a.INSRT_TS, 
    		   MTH_NBR, FRCST_FULL_TM_EE_CNT
    		From FRCST a
    					Left Join MSTR_LIST_OF_RSRC b
    						On a.RSRC_SHRT_NM = b.PROD_ID
    					Left Join RSRC c
    						On a.RSRC_SHRT_NM = c.RSRC_SHRT_NM
    		Where YR_NBR = @Year And rtrim(PROJ_CD) = @Project And SRC_FRCST_TXT = @Source
    			  And a.INSRT_TS = @MaxDateTime) p
    	Pivot
    		(
    		Sum(FRCST_FULL_TM_EE_CNT)
    		For [Mth_NBR] in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    		) as pvt
    	Order By SRC_FRCST_TXT, RSRC_TYP_CD, RSRC_FULL_NM
    
    END

  6. #6
    Join Date
    Aug 2009
    Posts
    18
    - uspGetYTDReportProjectionEstimated - gets the annual estimated to complete (EAC) numbers (actuals (timesheets) plus remaining forecast) depending on the month within the year and calculates a year to date EAC depending on the month. Cross joins are used to account for months where there isnt any data so when a user uses a pivot table, no mater what month they choose the annual and YTD numbers carry the totals to the next months. A ratio is used to join estimated forecast to timesheets in months that haven't closed financialy. So the ratio is multiplied against the forecast for a month that hasn't closed yet. Unions bring together the annual with ytd at a program and project level.

    Code:
     USE [FinancialDB]
    GO
    /****** Object:  StoredProcedure [dbo].[uspGetYTDReportProjectionEstimated]    Script Date: 02/11/2011 09:25:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[uspGetYTDReportProjectionEstimated]
    	-- Add the parameters for the stored procedure here
    	@Department Varchar(50),
    	@Year Int
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    -- Insert statements for procedure here
    	Declare @Ratio Numeric(6,4) -- Obtains the proper ratio for ToDate and PROJ_CDion data based off of the LKUP_CAL table
    	Declare @CurrFiscalYearMonth int -- Obtains the current TimeDetail fiscal YR_NBR and MTH_NBR from the LKUP_YRMM table				
    	Declare @MaxActualsYearMonth int -- Obtains the current Actuals fiscal YR_NBR and MTH_NBR from the LKUP_YRMM table
    	
    	Set @Ratio = (
    				  Select Top 1 RTO_PCT
    				  From LKUP_CAL									
    				  Where DT = (Select Max(FST_MON_DT) from TM_DTL)
    				  )
    	
    	Set @CurrFiscalYearMonth = (
    								Select MTH_2_NBR as YM
    								from LKUP_CAL
    								Where DT = (Select Max(FST_MON_DT) from TM_DTL)
    										
    								)
    	
    	Set @MaxActualsYearMonth = (
    								Select max(b.YRMM_NBR) as YM
    								from ACTULS a
    										Join LKUP_YRMM b
    												On a.YR_NBR = b.YR_NBR
    												And a.MTH_NBR = b.MTH_NBR
    								)
    
    	Select max(INSRT_TS) as INSRT_TS, e.PROJ_CD, e.YR_NBR, SRC_FRCST_TXT into #Temp
    	From FRCST e
    		Join LKUP_LOB_PGM f
    			On e.PGM_CD = f.PGM_NM
    			And e.PROJ_CD = f.PROJ_NM
    	Where APPL_DEPT_NM = @Department And 
    			SRC_FRCST_TXT = 'Forecast' and e.yr_nbr = @Year 
    	Group By e.PROJ_CD, e.YR_NBR, SRC_FRCST_TXT
    	
    	Select a.pgm_cd, a.Proj_Cd, b.mth_nbr, Sum(a.FRCST_CST_AMT) as ProjectionEstimated Into #Temp1
    	From FRCST a
    			Join #Temp t
    				On a.INSRT_TS = t.INSRT_TS
    					And a.PROJ_CD = t.PROJ_CD
    					And a.YR_NBR = t.YR_NBR
    					And a.Src_Frcst_txt = t.Src_Frcst_txt 	
    			Join LKUP_GRP_RSRC_TYP r
    				On a.rsrc_typ_cd = r.rsrc_typ_cd 
    			Cross Join
    				(Select Mth_Nbr
    				 From LKUP_Cal
    				 Where yr_nbr = @Year 
    				 Group By Mth_Nbr
    				 ) b
    	Where a.yr_nbr = @Year and a.mth_nbr > right(@CurrFiscalYearMonth,2)
    		  and r.GRP_RSRC_TYP_CD in ('IT', 'PASD') 
    	Group by a.pgm_cd, a.Proj_Cd, b.mth_nbr
    	
    	Select a.pgm_cd, a.Proj_Cd, b.mth_nbr, Sum(a.FRCST_CST_AMT*(@Ratio)) as ProjectionEstimated Into #Temp2
    	From FRCST a
    			Join #Temp t
    				On a.INSRT_TS = t.INSRT_TS
    					And a.PROJ_CD = t.PROJ_CD
    					And a.YR_NBR = t.YR_NBR
    					And a.Src_Frcst_txt = t.Src_Frcst_txt 	
    			Join LKUP_GRP_RSRC_TYP r
    				On a.rsrc_typ_cd = r.rsrc_typ_cd 
    			Cross Join
    				(Select Mth_Nbr
    				 From LKUP_Cal
    				 Where yr_nbr = @Year 
    				 Group By Mth_Nbr
    				 ) b
    	Where a.yr_nbr = @Year and a.mth_nbr = right(@CurrFiscalYearMonth,2)
    		  and r.GRP_RSRC_TYP_CD in ('IT', 'PASD') 
    	Group by a.pgm_cd, a.Proj_Cd, b.mth_nbr
    
    
    	Select a.pgm_cd, a.Proj_Cd, b.mth_nbr, Sum(a.ACTUL_CST_AMT) as ProjectionEstimated Into #Temp3
    	From Actuls a
    			Join LKUP_GRP_RSRC_TYP r
    				On a.rsrc_typ_cd = r.rsrc_typ_cd 
    			Cross Join
    				(Select Mth_Nbr
    				 From LKUP_Cal
    				 Where yr_nbr = @Year 
    				 Group By Mth_Nbr
    				 ) b
    	Where a.yr_nbr = @Year 
    		and (proj_cd like 'C%' or proj_cd like 'P%')
    		and r.GRP_RSRC_TYP_CD in ('IT', 'PASD') 
    	Group by a.pgm_cd, a.Proj_Cd, b.mth_nbr		
    
    	Select a.pgm_cd, a.Proj_Cd, b.mth_nbr, Sum(a.CST_AMT) as ProjectionEstimated Into #Temp4
    	From TM_Dtl a
    			Join LKUP_GRP_RSRC_TYP r
    				On a.rsrc_typ_cd = r.rsrc_typ_cd 
    			Cross Join
    				(Select Mth_Nbr
    				 From LKUP_Cal
    				 Where yr_nbr = @Year 
    				 Group By Mth_Nbr
    				 ) b
    	Where a.yr_nbr = @Year and a.mth_nbr = right(@CurrFiscalYearMonth,2)
    		and (proj_cd like 'C%' or proj_cd like 'P%')
    		and r.GRP_RSRC_TYP_CD in ('IT', 'PASD') 
    	Group by a.pgm_cd, a.Proj_Cd, b.mth_nbr		
    
    	SELECT rtrim(a.PGM_CD) as Program
    		  ,rtrim(a.[PROJ_CD]) as Project
    		  ,c.Proj_Desc as ProjectDesc
    		  ,rtrim(a.[PROJ_CD]) + ' ' + c.Proj_Desc as ProjectDescription
    		  ,convert(varchar(3), DateName( month , DateAdd( month , a.mth_nbr , 0 ) - 1 ),100) as MonthNm
    		  ,@Year as [Year]
    		  ,Sum(a.ProjectionEstimated) as ProjectionEstimated
    		  ,'Projection' as Source
    		  ,c.TRK_NM as Track
    		  ,c.RLSE_NBR as Release
    		  ,rtrim(c.APPL_DEPT_NM) as Department
    		  ,'' as Groups
    		
    	FROM #Temp1 a
    				Join LKUP_LOB_PGM c
    					On a.PGM_CD = c.PGM_NM
    					And a.PROJ_CD = c.PROJ_NM	
    									
    	Where c.APPL_DEPT_NM = @Department 
    --		  And a.mth_nbr > right(@CurrFiscalYearMonth,2)
    	Group By rtrim(a.PGM_CD)
    		  ,rtrim(a.[PROJ_CD]) 
    		  ,c.Proj_Desc 
    		  ,rtrim(a.[PROJ_CD]) + ' ' + c.Proj_Desc
    		  ,convert(varchar(3), DateName( month , DateAdd( month , a.mth_nbr , 0 ) - 1 ),100)
    		  ,c.TRK_NM 
    		  ,c.RLSE_NBR 
    		  ,rtrim(c.APPL_DEPT_NM)
    		   
    
    Union All
    
    	SELECT rtrim(a.PGM_CD) as Program
    		  ,rtrim(a.[PROJ_CD]) as Project
    		  ,c.Proj_Desc as ProjectDesc
    		  ,rtrim(a.[PROJ_CD]) + ' ' + c.Proj_Desc as ProjectDescription
    		  ,convert(varchar(3), DateName( month , DateAdd( month , a.mth_nbr , 0 ) - 1 ),100) as MonthNm
    		  ,@Year as [Year]
    		  ,Sum(a.ProjectionEstimated) as ProjectionEstimated
    		  ,'Projection' as Source
    		  ,c.TRK_NM as Track
    		  ,c.RLSE_NBR as Release
    		  ,rtrim(c.APPL_DEPT_NM) as Department
    		  ,'' as Groups
    		 
    	FROM #Temp2 a
    				Join LKUP_LOB_PGM c
    					On a.PGM_CD = c.PGM_NM
    					And a.PROJ_CD = c.PROJ_NM	
    									
    	Where c.APPL_DEPT_NM = @Department 
    		  
    	Group By rtrim(a.PGM_CD)
    		  ,rtrim(a.[PROJ_CD]) 
    		  ,c.Proj_Desc 
    		  ,rtrim(a.[PROJ_CD]) + ' ' + c.Proj_Desc
    		  ,convert(varchar(3), DateName( month , DateAdd( month , a.mth_nbr , 0 ) - 1 ),100)
    		  ,c.TRK_NM 
    		  ,c.RLSE_NBR 
    		  ,rtrim(c.APPL_DEPT_NM)
    		   
    
    Union All
    	
    	SELECT rtrim(a.PGM_CD) as Program
    		  ,rtrim(a.[PROJ_CD]) as Project
    		  ,c.Proj_Desc as ProjectDesc
    		  ,rtrim(a.[PROJ_CD]) + ' ' + c.Proj_Desc as ProjectDescription
    		  ,convert(varchar(3), DateName( month , DateAdd( month , a.mth_nbr , 0 ) - 1 ),100) as MonthNm
    		  ,@Year as [Year]
    		  ,Sum(a.ProjectionEstimated) as ProjectionEstimated
    		  ,'Projection' as Source
    		  ,c.TRK_NM as Track
    		  ,c.RLSE_NBR as Release
    		  ,rtrim(c.APPL_DEPT_NM) as Department
    		  ,'' as Groups
    			
    	FROM #Temp3 a
    				Join LKUP_LOB_PGM c
    					On a.PGM_CD = c.PGM_NM
    					And a.PROJ_CD = c.PROJ_NM	
    				
    	Where c.APPL_DEPT_NM = @Department 
    		  		  
    	Group By rtrim(a.PGM_CD)
    		  ,rtrim(a.[PROJ_CD]) 
    		  ,c.Proj_Desc 
    		  ,rtrim(a.[PROJ_CD]) + ' ' + c.Proj_Desc
    		  ,convert(varchar(3), DateName( month , DateAdd( month , a.mth_nbr , 0 ) - 1 ),100)
    		  ,c.TRK_NM 
    		  ,c.RLSE_NBR 
    		  ,rtrim(c.APPL_DEPT_NM)
    		   
    
    Union All
    	
    	SELECT rtrim(a.PGM_CD) as Program
    		  ,rtrim(a.[PROJ_CD]) as Project
    		  ,c.Proj_Desc as ProjectDesc
    		  ,rtrim(a.[PROJ_CD]) + ' ' + c.Proj_Desc as ProjectDescription
    		  ,convert(varchar(3), DateName( month , DateAdd( month , a.mth_nbr , 0 ) - 1 ),100) as MonthNm
    		  ,@Year as [Year]
    		  ,Sum(a.ProjectionEstimated) as ProjectionEstimated
    		  ,'Projection' as Source
    		  ,c.TRK_NM as Track
    		  ,c.RLSE_NBR as Release
    		  ,rtrim(c.APPL_DEPT_NM) as Department
    		  ,'' as Groups
    	
    	FROM #Temp4 a
    			Join LKUP_LOB_PGM c
    				On a.PGM_CD = c.PGM_NM
    				And a.PROJ_CD = c.PROJ_NM
    						
    	WHERE c.APPL_DEPT_NM = @Department 
    		  and a.mth_nbr > right(@MaxActualsYearMonth,2)	 	
    	
    	Group By rtrim(a.PGM_CD)
    		  ,rtrim(a.[PROJ_CD]) 
    		  ,c.Proj_Desc 
    		  ,rtrim(a.[PROJ_CD]) + ' ' + c.Proj_Desc
    		  ,convert(varchar(3), DateName( month , DateAdd( month , a.mth_nbr , 0 ) - 1 ),100)
    		  ,c.TRK_NM 
    		  ,c.RLSE_NBR 
    		  ,rtrim(c.APPL_DEPT_NM)
    		   
    
    	Drop Table #Temp
    	Drop Table #Temp1	
    	Drop Table #Temp2
    	Drop Table #Temp3  
    	Drop Table #Temp4
    
    
    END

  7. #7
    Join Date
    Aug 2009
    Posts
    18
    - uspGetForecastProjectBalance - when a forecast is submitted to the database all of the individual resources (employees) working on the project are loaded into the staging table, where this balancing sproc is run to confirm that the cost and rates are working properly and no data gets into the db that could be corrupted. FTEs are units multiplied by a monthly cost to give grand totals. There is a lot of validation that goes on in the front end.

    Code:
    USE [FinancialDB]
    GO
    /****** Object:  StoredProcedure [dbo].[uspGetForecastProjectBalance]    Script Date: 02/11/2011 09:53:19 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[uspGetForecastProjectBalance] 
    	-- Add the parameters for the stored procedure here
    	@Project Varchar(25),
    	@Source Varchar(50),
    	@UserID Varchar(25),
    	@ForecastFTEs Numeric(18,9),
    	@ForecastCost Numeric(18,4),
    	@Year int
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @v_trans_started BIT
    
    	BEGIN TRY
    		--PRINT 'Executing uspDeleteBudget.'
    
    		SET @v_trans_started = 0
    		IF @@TRANCOUNT = 0
    		  BEGIN
    		     BEGIN TRANSACTION;
    		     SET @v_trans_started = 1
    		  END
    		ELSE
    			
    			SET @v_trans_started = 0
    
    			Declare @DBForecastFTEs numeric(18,9)
    			Declare @DBForecastCostDynamic numeric(18,4)			
    				
    
    			Set @DBForecastFTEs = (Select sum(FRCST_FULL_TM_EE_CNT) 
    								   From IMPT_FRCST_PROJ
    								    Where PROJ_CD = @Project
    										And FRCST_SRC_DESC = @Source
    										And UPDT_USERID = @UserID
    										And Yr_NBR = @Year)			
    			
    			Set @DBForecastCostDynamic = (Select Sum(FRCST_FULL_TM_EE_CNT*MTH_CST)
    										 From IMPT_FRCST_PROJ a
    											Join LKUP_MTH_CST b
    												On a.RSRC_TYP_CD = b.RSRC_TYP_CD
    												And a.YR_NBR = b.YR_NBR
    												And a.MTH_NBR = b.MTH_NBR
    												
    										 Where PROJ_CD = @Project
    											And FRCST_SRC_DESC = @Source
    											And UPDT_USERID = @UserID
    											And a.yr_nbr = @Year)					
    			
    		  						
    			If @ForecastFTEs is Null 			
    				RaisError('Go to catch block to raise custom message.',16,1)
    			If @ForecastCost is Null
    				RaisError('Go to catch block to raise custom message.',16,1)
    			If (Round(ABS(@ForecastFTEs-IsNull(@DBForecastFTEs,0)), 2) > .002)
    				RaisError('Go to catch block to raise custom message.',16,1)
    			If (Round(ABS((@ForecastCost-(IsNull(@DBForecastCostDynamic,0)))),0) > 3)
    				RaisError('Go to catch block to raise custom message.',16,1)
    				
    		IF @v_trans_started = 1
    			BEGIN
    				SET @v_trans_started = 0
    				COMMIT TRANSACTION;
    			END
    
       END TRY
    
       BEGIN CATCH
    
          IF @v_trans_started = 1
             BEGIN
                SET @v_trans_started = 0
                ROLLBACK TRANSACTION;
             END
    	   
    	  Declare @ErrorVar int		
    	  Set @ErrorVar = @@Error  
    	
    	  If (@ErrorVar <> 0)
    			Begin			  
    			  Declare @ErrorMessage Varchar(4000)
    			  Declare @ErrorSeverity Int
    			  Declare @ErrorState Int
    			
    			  SELECT 
    			  @ErrorMessage = ERROR_MESSAGE(),
    			  @ErrorSeverity = ERROR_SEVERITY(),
    			  @ErrorState = ERROR_STATE()
    			
    			  RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState)		  
    			End
    	  Else
    		If (Round(ABS(@ForecastFTEs-IsNull(@DBForecastFTEs,0)), 2) > .002)  
    			
    			RaisError('There was a problem loading FTEs. No data was loaded.',16,1)			  
    		
    	  Else
    		If (Round(ABS((@ForecastCost-IsNull(@DBForecastCostDynamic,0))),0) > 3)	
    			
    			RaisError('There was a problem loading Cost. No data was loaded.',16,1)
    	  
          Else
    		
    			RaisError('There was a problem loading Data. No data was loaded.',16,1)	 	
    		
        END CATCH
    END

  8. #8
    Join Date
    Aug 2009
    Posts
    18
    - uspInsertDimensionValues - this is dynamic sql to load in various primary key data that are attributes to various business keys that is used as foreign keys in various fact tables.

    Code:
    USE [FinancialDB]
    GO
    /****** Object:  StoredProcedure [dbo].[uspInsertDimensionValues]    Script Date: 02/11/2011 09:15:25 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[uspInsertDimensionValues] (
    	-- Add the parameters for the stored procedure here
    	@InsertTable Varchar(50), --Insert Table
    	@InsertField Varchar(50), --Insert Field
    	@SelectTable Varchar(50), --Select Table
    	@SelectField Varchar(50), --Select Field
    	@TimeStamp DateTime
    	)
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	
    		DECLARE @v_trans_started BIT
    
    	BEGIN TRY
    		--PRINT 'Executing uspInsertDimensionValues, Table ' + @InsertTable + '.'
    
    		SET @v_trans_started = 0
    		IF @@TRANCOUNT = 0
    			BEGIN
    				BEGIN TRANSACTION;
    				SET @v_trans_started = 1
    			END
    		ELSE
    			SET @v_trans_started = 0
    			
    			DECLARE @sql    nvarchar(Max),
    					@params nvarchar(Max),
    					@CTimeStamp Varchar(50)
    --					@InsertTable Varchar(50), --Insert Table
    --					@InsertField Varchar(50), --Insert Field
    --					@SelectTable Varchar(50), --Select Table
    --					@SelectField Varchar(50), --Select Field
    --					@TimeStamp DateTime	
    --			
    --			Set @TimeStamp = GetDate()
    			Set @CTimeStamp = @TimeStamp
    --			Set @InsertTable = 'PGM'			
    --			Set @InsertField = 'PGM_CD'
    --			Set @SelectTable = 'IMPT_TM_DTL'
    --			Set @SelectField = 'PGM_CD'			
    
    			SET @sql = 'INSERT INTO dbo.' + QuoteName(@InsertTable) + ' 
    							(' + QuoteName(@InsertField) + ', INSRT_TS )
    							Select a.' + QuoteName(@SelectField) + ', ''' + @CTimeStamp + ''' 
    							From dbo.' + QuoteName(@SelectTable) + ' a
    							Where Not Exists
    								(
    								Select b.' + QuoteName(@InsertField) + ' 
    								From dbo.' + QuoteName(@InsertTable) + ' b
    								Where b.' + QuoteName(@InsertField) + ' = a.' + QuoteName(@SelectField) + ' 
    								)
    							Group By a.' + QuoteName(@SelectField) + ''
    			
    			SELECT @params = '@InsertTable Varchar(50), ' +
    							'@InsertField Varchar(50), ' +
    							'@SelectField Varchar(50), ' +
    							'@CTimeStamp DateTime, ' +
    							'@SelectTable Varchar(50)' 
    											
    			EXEC sp_executesql @sql, @params, @InsertTable, @InsertField, @SelectField, @CTimeStamp, @SelectTable
       
    		IF @v_trans_started = 1
    			BEGIN
    				SET @v_trans_started = 0
    				COMMIT TRANSACTION;
    			END
    
    	END TRY
    
    	BEGIN CATCH
    
    		IF @v_trans_started = 1
    			BEGIN
    				SET @v_trans_started = 0
    				ROLLBACK TRANSACTION;
    			END
    		
    		Declare @ErrorMessage Varchar(4000)
    	    Declare @ErrorSeverity Int
    	    Declare @ErrorState Int
    		
    		SELECT 
    		@ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE()
    		
    		RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState)
    
    	END CATCH
    	
    END

  9. #9
    Join Date
    Aug 2009
    Posts
    18
    - uspTimeDetailProcess - this loads timesheet data weekly and is part of an ETL package (actually an ELT package considering the SSIS pack loads the data into a staging table where this sproc runs against the import and formats and loads it.) Someday I will develop out the SSIS pack so it truly becomes and ETL. Timeconstraints and I am not an SSIS guru.

    I can't post this one as the characters are too long.

    Thanks to anyone who replies. I really do appreciate it.
    Last edited by ease2002; 02-14-11 at 13:12.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So, you're trying to define a position of a SQL Seveloper, and you need to assign a rating of this position based on the complexity of the procedures? In other words, if an intermediate developer can figure out what the procs are doing, then you'll advertize the position as Intermediate Level Developer?

    P.S.: And they hardly have any "Redmond Gurus" left in Redmond
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Aug 2009
    Posts
    18
    Yes. I'm trying to gauge if the stored procs are well written and would be those of a senior or intermediate, or somewhere in between.

    Thank you for your assistance.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Disregarding the fact that the naming convention in the code you posted absolutely sucks, I'd say that whoever wrote that code is an accomplished SQL developer. Good commenting, use of whitespace and formatting, plus implementation of advanced sql concepts such as cross joins and pivot.
    I'd rate them a 4 or a 5, based upon my cursory glance through the code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by ease2002 View Post
    Yes. I'm trying to gauge if the stored procs are well written and would be those of a senior or intermediate, or somewhere in between.

    Thank you for your assistance.
    Give him the raise he is asking for, he deserves it.

  14. #14
    Join Date
    Aug 2009
    Posts
    18
    Thank you, Blindman and Gagnon. You guys have helped me out a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •