snowflake_storcproc_example2.sql

USE ROLE GG_SNOWFLAKE_REPORTING_GPAVON;
USE WAREHOUSE FRB_WH;
USE DATABASE EDCI_SANDBOX;
USE SCHEMA REPORTING_GPAVON;


CREATE or replace PROCEDURE SP_TL_HIERARCHY(PERSONINITIALKEY FLOAT,DATE_PARAM VARCHAR)
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
var rs = snowflake.execute( { sqlText: 


`





--SET (PERSONINITIALKEY,DATE_PARAM) = ('4','2021-06-30') ; -- 13015 -selfridge , 13568 - barbara palmer

--CREATE OR REPLACE TABLE TL_HIERARCHY AS 
INSERT INTO TL_HIERARCHY


WITH FRB_ORG AS ( 
					SELECT * 
					FROM "EDCI_SANDBOX"."REPORTING_GPAVON"."TL_FRB_ORG"
					WHERE 1 = 1

				)



/*Create Recursive Base, this will be used in the union below*/
,EMPLOYEE_MANAGER AS 
						(
						SELECT
						 0																			AS EMPLOYEE_LEVEL 
						,A.INITIALS																	AS INITIALS
						,A.PERSONINITIALKEY															AS PERSONINITIALKEY
						,A.PAYEEID																	AS PAYEEID
						,A.EMPLOYEE_NAME															AS EMPLOYEE_NAME
						,A.TITLE																	AS TITLE
						,A.CITY																		AS CITY
						,A.STATE																	AS STATE
						,A.LOCATION																	AS LOCATION
						,A.REGION																	AS REGION
						,A.REPORTSTOINITIALS														AS REPORTS_TO_INITIALS
						,A.REPORTSTONAME															AS REPORTS_TO_NAME
						,B.INITIALS																	AS SUPERVISOR_INITIALS
						,B.PERSONINITIALKEY															AS SUPERVISOR_PERSONINITIALKEY
						,B.PAYEEID																	AS SUPERVISOR_PAYEEID
						,B.EMPLOYEE_NAME															AS SUPERVISOR_EMPLOYEE_NAME
						,B.TITLE																	AS SUPERVISOR_TITLE
						,B.CITY																		AS SUPERVISOR_CITY
						,B.STATE																	AS SUPERVISOR_STATE
						,B.LOCATION																	AS SUPERVISOR_LOCATION
						,B.REGION																	AS SUPERVISOR_REGION
						,B.REPORTSTOINITIALS														AS SUPERVISOR_REPORTS_TO_INITIALS
						,B.REPORTSTONAME															AS SUPERVISOR_REPORTS_TO_NAME

						FROM FRB_ORG A
						LEFT JOIN  FRB_ORG B
						ON (A.REPORTSTOINITIALS = B.INITIALS)
						WHERE 1 = 1
						)


--SELECT * FROM EMPLOYEE_MANAGER

				

,EMPLOYEE_HIERARCHY AS (

						/*THIS IS THE ANCHOR, MEANING THE QUERY WILL LOOP OVER ITSELF FOR EACH MANAGER AND GET THEIR DELEGATES*/

						/*STEP 1: GET THE EMPLOYEE MANAGER OF CHOICE. THIS IS CONSIDERED LEVEL 1 (TOP OF THE PYRAMID) -- ALSO CALLED THE INITIAL QUERY OR ANCHOR MEMBER
						THIS BASE RESULT WILL BE USED FOR THE NEXT ITERATION. 
						*/

						SELECT 
						 1							AS EMPLOYEE_LEVEL
						,INITIALS					AS INITIALS
						,PERSONINITIALKEY			AS PERSONINITIALKEY
						,PAYEEID					AS PAYEEID
						,EMPLOYEE_NAME				AS EMPLOYEE_NAME
						,TITLE						AS TITLE	
						,CITY						AS CITY
						,STATE					    AS STATE	
						,LOCATION					AS LOCATION
						,REGION						AS REGION		
						,REPORTS_TO_INITIALS        AS REPORTS_TO_INITIALS
						,REPORTS_TO_NAME			AS REPORTS_TO_NAME
						,SUPERVISOR_INITIALS		AS SUPERVISOR_INITIALS
						,SUPERVISOR_EMPLOYEE_NAME	AS SUPERVISOR_EMPLOYEE_NAME
						FROM 
						EMPLOYEE_MANAGER
						WHERE PAYEEID = '${PERSONINITIALKEY}'  --$PERSONINITIALKEY
				

						UNION ALL

						/*RECURSION HAPPENS HERE DUE TO THE JOIN HAPPENING WITHIN A UNION...KINDA CLEVER, NO? THIS WILL CREATE THE VARIOUS OTHER LEVELS.
						BASED OFF THE MANAGER CHOSEN ABOVE WE GET THEIR DELEGATES. RECURSIVE MEMBER -- IS UNIONED WITH THE ANCHOR MEMBER  VIA THE UNION ALL FUNCTION.
						HERE WE EXECUTE THE RECURSIVE MEMBER WITH THE INPUT RESULT SET FROM THE PREVIOUS INTERATION */
						
			

						SELECT
						EMPLOYEE_MANAGER_DELEGATES.EMPLOYEE_LEVEL + 1								AS EMPLOYEE_LEVEL
					   ,EMPLOYEE_MANAGER.INITIALS													AS INITIALS
					   ,EMPLOYEE_MANAGER.PERSONINITIALKEY											AS PERSONINITIALKEY
					   ,EMPLOYEE_MANAGER.PAYEEID													AS PAYEEID
					   ,EMPLOYEE_MANAGER.EMPLOYEE_NAME												AS EMPLOYEE_NAME
					   ,EMPLOYEE_MANAGER.TITLE														AS TITLE
					   ,EMPLOYEE_MANAGER.CITY														AS CITY
					   ,EMPLOYEE_MANAGER.STATE														AS STATE
					   ,EMPLOYEE_MANAGER.LOCATION													AS LOCATION
					   ,EMPLOYEE_MANAGER.REGION														AS REGION	
					   ,EMPLOYEE_MANAGER.REPORTS_TO_INITIALS										AS REPORTS_TO_INITIALS
					   ,EMPLOYEE_MANAGER.REPORTS_TO_NAME											AS REPORTS_TO_NAME
					   ,EMPLOYEE_MANAGER.SUPERVISOR_INITIALS										AS SUPERVISOR_INITIALS
					   ,EMPLOYEE_MANAGER.SUPERVISOR_EMPLOYEE_NAME									AS SUPERVISOR_EMPLOYEE_NAME
					   	FROM EMPLOYEE_MANAGER
						INNER JOIN EMPLOYEE_HIERARCHY AS EMPLOYEE_MANAGER_DELEGATES
						ON (EMPLOYEE_MANAGER_DELEGATES.INITIALS = EMPLOYEE_MANAGER.REPORTS_TO_INITIALS)
						


						)

--SELECT * FROM EMPLOYEE_HIERARCHY


        
        
        
        
        
        
        
        
        
        



--NOTE THE COLUMNS MUST BE IN THE SAME ORDER AS THE TABLE OTHERWISE DATA WILL BE INPUTTED INCORRECTLY. 
  SELECT 
	   EMPLOYEE_LEVEL
      ,INITIALS
      ,PERSONINITIALKEY
      ,EMPLOYEE_NAME
      ,TITLE
      ,CITY
      ,STATE
      ,LOCATION
      ,REGION
      ,REPORTS_TO_INITIALS
      ,REPORTS_TO_NAME
      ,INSERT_DT
      ,SELECTED_OFFICER_INITIALS
      ,SELECTED_OFFICER_LOGIN
      ,SELECTED_OFFICER_NAME
      ,ROW_NUM
      ,PROD_DT
	  ,PAYEEID
--INTO [DataScienceAndAnalytics].[teamlead].[hierarchy]
FROM
(
		SELECT 
		 DISTINCT EH.EMPLOYEE_LEVEL																									AS EMPLOYEE_LEVEL
		,EH.INITIALS																												AS INITIALS
		,CAST(EH.PERSONINITIALKEY as INT)																							AS PERSONINITIALKEY
		,EH.EMPLOYEE_NAME																											AS EMPLOYEE_NAME
		,EH.TITLE 																													AS TITLE
		,EH.CITY																													AS CITY
		,EH.STATE																													AS STATE
		,EH.LOCATION																												AS LOCATION
		,EH.REGION																													AS REGION
		,EH.REPORTS_TO_INITIALS																										AS REPORTS_TO_INITIALS
		,EH.REPORTS_TO_NAME																											AS REPORTS_TO_NAME
		,'${DATE_PARAM}' /*Convert(DATE,DATEADD(DAY, -1 ,GETDATE()))*/	/* '2020-03-18'	*/									    	AS PROD_DT   -- change this when you manually input dates
		,CURRENT_DATE																												AS INSERT_DT
		,FRB_PLP.INITIALS																											AS SELECTED_OFFICER_INITIALS
		,CASE
		    WHEN EH.PAYEEID = 20 THEN 'COHORT ALL' --Step 4 in FRB_ORG script when adding custom teams
			WHEN EH.PAYEEID BETWEEN 1 AND 10 THEN CONCAT('COHORT ', CAST(EH.PAYEEID as INT))
			ELSE FRB_PLP.LOGIN
		 END 																														AS SELECTED_OFFICER_LOGIN
		,CASE
		    WHEN EH.PAYEEID = 20 THEN 'COHORT ALL' --Step 4 in FRB_ORG script when adding custom teams
			WHEN EH.PAYEEID BETWEEN 1 AND 10 THEN CONCAT('COHORT ', CAST(EH.PAYEEID as INT))
			ELSE CONCAT(FRB_PLP.PREFERREDNAME,' ',FRB_PLP.LASTNAME)	
		 END																													AS SELECTED_OFFICER_NAME 
		,ROW_NUMBER() OVER (PARTITION BY EH.EMPLOYEE_LEVEL, EH.INITIALS 
							ORDER BY 
							CASE WHEN EH.PAYEEID BETWEEN 1 AND 100 THEN 1 ---Step 5 in FRB_ORG when adding custom teams. REMEMBER TO EDIT THIS IF A CUSTOMER TEAM HAS A NUMBER HIGHER THAN 100
								 ELSE 2
							END DESC)																								AS ROW_NUM	-- REMOVES DUPES CAUSED BY COHORTS
		,CAST(EH.PAYEEID as INT)																												AS PAYEEID		
		
		FROM EMPLOYEE_HIERARCHY EH
		
		LEFT JOIN (
					SELECT *
					FROM EagleVision.dbo.T_REF_PEOPLE_INITIALS
				    WHERE 1 = 1 
					AND CURRENTFLAG = '1'
                    AND PAYEEID NOT IN ('CONTRACTOR','CONTINGENT','CONSULTANT','NULL')
				   ) FRB_PLP --[EagleVision].[dbo].[V_REF_PEOPLE_INITIALS_CURR]
		ON (CASE WHEN COLLATE(FRB_PLP.PAYEEID,'') LIKE '%[A-Z]%' THEN 0 ELSE CAST(FRB_PLP.PAYEEID AS FLOAT) END = ${PERSONINITIALKEY}) --$PERSONINITIALKEY
		
		WHERE 1 = 1
 
) HIERARCHY
WHERE 1 =1 
--AND INITIALS = 'A0B'
AND ROW_NUM = 1 -- REMOVES DUPES CAUSED BY COHORTS		
  
  
`
   
   
   } );
return 'Done.';
$$;

  
--CALL SP_TL_HIERARCHY(13015, TO_VARCHAR(SUBSTRING(DATEADD(DAY,-1,'2021-07-10'),1,10)))