SQL

SQL

- Design DB and EER/ER diagram
- Stored Procedure/ Triggers /Views
- Extensive ETL experience
- Big data process experienced 
- Various platform experienced (MS, Snowflakes, DB2, Squirrel, DBeaver) 
- Query optimisation

 






 

ALTER PROCEDURE [PMO].[Projects_View_Timeline] @Biz_Division VARCHAR(30) = NULL
	,@Impacted VARCHAR(30) = NULL
AS
BEGIN
	SET NOCOUNT ON;

	CREATE TABLE #Impacted_Projects (
		ProjectID INT
		,Impacted VARCHAR(10)
		)

	IF (
			@impacted <> 'False'
			AND @impacted <> ''
			)
		AND @Biz_Division <> 'All'
	BEGIN
		--Insert all impacted Projects
		INSERT INTO #Impacted_Projects
		SELECT DISTINCT ProjectID
			,1
		FROM PMO.Projects_Impact
		WHERE Impacted_Division LIKE '%' + @Biz_Division + '%'
			AND valid = 1

		--Remove if the impacted project is division leading project
		DELETE
		FROM #Impacted_Projects
		WHERE ProjectID IN (
				SELECT ProjectID
				FROM PMO.Projects
				WHERE Biz_Division LIKE '%' + @Biz_Division + '%'
				)
	END

	IF (
			@Biz_Division = 'All'
			OR @Biz_Division IS NULL
			)
	BEGIN
		SELECT ProjectName
			,Biz_Division
			,Biz_Lead
			,SVC_Type
			,Strategic_Core
			,Strategic_1_Pillar
			,Strategic_2_Pillar
			,Strategic_3_Pillar
			,Strategic_NotAlighned
			,''
			,
			--Strat_Focus,
			Current_Status
			,FORMAT(Current_StartDate, 'dd/MM/yyyy')
			,FORMAT(Current_EndDate, 'dd/MM/yyyy')
			,Change_Degree_Final_Impact --Degree of Change
		FROM PMO.Projects
		ORDER BY Current_StartDate
			,Biz_Division
			,ProjectName
	END

	IF (
			@Biz_Division <> 'All'
			AND @Biz_Division IS NOT NULL
			)
	BEGIN
		SELECT ProjectName
			,Biz_Division
			,Biz_Lead
			,SVC_Type
			,Strategic_Core
			,Strategic_1_Pillar
			,Strategic_2_Pillar
			,Strategic_3_Pillar
			,Strategic_NotAlighned
			,IP.Impacted
			,
			--				Strat_Focus,
			Current_Status
			,FORMAT(Current_StartDate, 'dd/MM/yyyy')
			,FORMAT(Current_EndDate, 'dd/MM/yyyy')
			,Change_Degree_Final_Impact --Degree of Change
		FROM PMO.Projects P
		LEFT JOIN #Impacted_Projects IP ON p.projectiD = IP.projectID
		WHERE Biz_Division LIKE '%' + @Biz_Division + '%'
			OR P.PRojectID IN (
				SELECT PRojectID
				FROM #Impacted_Projects
				)
		ORDER BY IP.impacted ASC
			,Current_StartDate
			,Biz_Division
			,ProjectName
	END

	--Drop table if exists
	IF OBJECT_ID('tempdb..#Impacted_Projects') IS NOT NULL
		DROP TABLE #Impacted_Projects
END