Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

with the following SQL codes, can you tell what input tables are? USE [AR_Analytics] GO…

  1. User

    with the following SQL codes, can you tell what input tables are?

    USE [AR_Analytics] GO

    /****** Object: StoredProcedure [ap].[spAdviser_HighGrowth] Script Date: 25/09/2024 12:00:39 PM ******/ SET ANSI_NULLS ON GO

    SET QUOTED_IDENTIFIER ON GO

    /************************************************************************************************

    Description:		Retrieves customers with total net inflow greater than $10,000 within the
    					period whose ratio of high growth investments is 80% or more of their
    					total FUM. High growth investments as being identified as the total percentage
    					allocated to Property, Australian Equities, International Equities and Other
    					categories for the product. Products with a negative FUM value is excluded.
    
    Data Sources:		
    

    ac.RAW_RPAY_Adviser ap.Adviser ap.RAW_Arm_CrossReference ac.RAW_Xplan_Adviser

    ac.RAW_FMS_DLD1066_FUM ac.RAW_FMS_DLD0926_TransactionHistory ac.RAW_Firstwrap_Fum ap.RAW_Xplan_Holdings ap.RAW_Xplan_Transaction

    ap.RAW_Xplan_AssetAllocation STG.RAW_MAPPING_FIRSTWRAP_PRODUCTCODE

    ap.WMOnestop_Client ap.RAW_Xplan_Client

    Output Table:		ap.Adviser_RiskIndicatorCustomer_Test
    					ap.Adviser_RiskIndicator_Test
    
    Author:				J. Nguyen
    Create Date:		19 Jul 2018
    Last Update Date:	19 Jul 2018
    

    *************************************************************************************************/

    CREATE PROCEDURE [ap].[spAdviser_HighGrowth] (@ReportFromDate DATE, @ReportToDate DATE) AS

    DECLARE @LoadID INT, @RowsInserted INT;

    BEGIN TRY

    INSERT INTO AR_Analytics.ap.LoadLog (Process, StartDateTime, TableName)
    SELECT OBJECT_NAME(@@PROCID), GETDATE(), 'ap.Adviser_RiskIndicatorCustomer'
    
    SELECT @LoadID = MAX(LoadID)
    FROM AR_Analytics.ap.LoadLog
    
    BEGIN TRAN
    
    	DECLARE @InflowThreshold	DECIMAL(12,2)	= 10000,
    			@InvestmentRatio	DECIMAL(12,2)	= 0.8,
    			@CustomerRatio		DECIMAL(12,2)	= 0.33,
    			@RiskIndicator		VARCHAR(50)		= 'Growth Asset Allocation',
    			@IsCurrent			BIT;
    
    	IF @ReportFromDate IS NULL OR @ReportToDate IS NULL
    		THROW 50000, 'ReportFromDate and ReportToDate cannot be NULL', 1
    
    	IF EXISTS (
    					SELECT APIR
    					FROM ap.AssetAllocationReference_Morningstar
    					GROUP BY APIR
    					HAVING COUNT(*) > 1
    			) 
    		THROW 50000, 'Duplicate APIR codes in ap.AssetAllocationReference_Morningstar cannot be NULL', 1
    
    	CREATE TABLE #Exchange (
    								Exchange		VARCHAR(50)		NOT NULL,
    								AssetAllocation DECIMAL(18,2)	NOT NULL
    							)
    
    	INSERT INTO #Exchange
    	SELECT 'ASX', 1					--Australian Equities
    	UNION ALL SELECT 'NYS', 1		--International Equities
    	UNION ALL SELECT 'NAS', 1		--International Equities
    	UNION ALL SELECT 'GBF', 1		--International Equities
    	UNION ALL SELECT 'NZ', 1		--International Equities
    
    	IF (
    			SELECT MAX(PeriodEndDate)
    			FROM ap.Adviser_RiskIndicator_Test
    			WHERE RiskIndicator = @RiskIndicator
    		) > @ReportToDate
    		SET @IsCurrent = 0
    	ELSE
    		SET @IsCurrent = 1
    
    	DELETE FROM ap.Adviser_RiskIndicatorCustomer_Test
    	WHERE	PeriodStartDate = @ReportFromDate
    		AND PeriodEndDate = @ReportToDate
    		AND RiskIndicator = @RiskIndicator	
    	
    	IF @IsCurrent = 1
    		UPDATE ap.Adviser_RiskIndicatorCustomer_Test
    		SET IsCurrent = 0
    		WHERE	IsCurrent = 1
    			AND PeriodEndDate < @ReportToDate
    			AND RiskIndicator = @RiskIndicator
    		
    	INSERT INTO ap.Adviser_RiskIndicatorCustomer_Test (
    														RiskIndicator
    														,Licensee
    														,ConnectID
    														,ArmID
    														,XplanID
    														,CustomerNumber
    														,CIF
    														,ClientName
    														--,Age
    														,AccountNumber
    														,InvestmentFund
    														,Inflow
    														,FUM
    														,TotalFUM
    														,InvestmentRatio
    														,RiskRating
    														,PeriodStartDate
    														,PeriodEndDate
    														,IsCurrent
    														,DataSource
    													)
    	SELECT	@RiskIndicator
    			,fum.Licensee
    			,fum.ConnectID
    			,fum.ArmID
    			,fum.XplanID
    			,fum.CustomerNumber
    			,COALESCE(co.SurvivorCIF, ca.SurvivorCIF)
    			,fum.ClientName
    			--,DATEDIFF(YEAR, c.DateOfBirth, GETDATE())																										AS ClientAge
    			,fum.AccountNumber
    			,NULL 																																			AS FundName
    			,SUM(fum.Inflow)																																AS Inflow
    			,SUM(fum.Property + fum.AustralianEquities + fum.InternationalEquities + fum.Other)																AS Fum
    			,SUM(fum.TotalFUM)																																AS TotalFum
    			,SUM(fum.Property + fum.AustralianEquities + fum.InternationalEquities + fum.Other) / SUM(fum.TotalFUM)											AS InvestmentRatio
    			,IIF(SUM(fum.Property + fum.AustralianEquities + fum.InternationalEquities + fum.Other) / SUM(fum.TotalFUM) > @InvestmentRatio, 'High', 'Low')	AS Rating
    			,@ReportFromDate
    			,@ReportToDate
    			,@IsCurrent
    			,fum.DataSource
    	FROM ap.Adviser_RiskIndicatorAccount_Test fum
    	--LEFT JOIN AR_Analytics.ap.WMOnestop_ClientOSCI cl
    	--	ON fum.CustomerNumber = cl.OSCI
    	--	AND cl.PrimaryOSCI = 1
    	--	AND cl.Deleted = 0
    	LEFT JOIN AR_Analytics.ap.WMOnestop_ClientOSCI co
    		ON fum.CustomerNumber = co.OSCI
    		AND co.PrimaryOSCI = 1
    		AND co.Deleted = 0
    		AND LEN(fum.AccountNumber) = 15
    		AND fum.AccountNumber LIKE '001%'
    	LEFT JOIN (
    					SELECT	AccountNumber
    							,SurvivorCIF
    							,ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY SurvivorCIF DESC) AS RowNo
    					FROM AR_Analytics.ap.WMOnestop_ClientAccount
    					WHERE Deleted = 0
    				) ca ON fum.AccountNumber = ca.AccountNumber
    					AND ca.RowNo = 1
    	WHERE	1=1
    		AND fum.PeriodStartDate = @ReportFromDate
    		AND fum.PeriodEndDate = @ReportToDate
    	GROUP BY fum.Licensee
    			,fum.ConnectID
    			,fum.ArmID
    			,fum.XplanID
    			,fum.CustomerNumber
    			,COALESCE(co.SurvivorCIF, ca.SurvivorCIF)
    			,fum.ClientName
    			--,c.DateOfBirth
    			,fum.AccountNumber
    			,fum.PeriodStartDate
    			,fum.PeriodEndDate
    			,fum.DataSource
    	HAVING SUM(fum.Inflow) > @InflowThreshold
    			
    	UPDATE AR_Analytics.ap.LoadLog
    	SET EndDateTime	= GETDATE(),
    		Records		= @@ROWCOUNT
    	WHERE LoadID = @LoadID
    
    	INSERT INTO AR_Analytics.ap.LoadLog (Process, StartDateTime, TableName)
    	SELECT OBJECT_NAME(@@PROCID), GETDATE(), 'ap.Adviser_RiskIndicator'
    
    	SELECT @LoadID = MAX(LoadID)
    	FROM AR_Analytics.ap.LoadLog
    
    	DELETE FROM ap.Adviser_RiskIndicator_Test
    	WHERE	PeriodStartDate = @ReportFromDate
    		AND PeriodEndDate = @ReportToDate
    		AND RiskIndicator = @RiskIndicator
    	
    	IF @IsCurrent = 1
    		UPDATE ap.Adviser_RiskIndicator_Test
    		SET IsCurrent = 0
    		WHERE	IsCurrent = 1
    			AND PeriodEndDate < @ReportToDate
    			AND RiskIndicator = @RiskIndicator
    
    	INSERT INTO ap.Adviser_RiskIndicator_Test (
    												RiskIndicator
    												,Licensee
    												,ConnectID
    												,ArmID
    												,XplanID
    												,Customers
    												,TotalCustomers
    												,InvestmentRatio
    												,RiskRating
    												,Score
    												,AEWSScore
    												,AEWSRating
    												,PeriodStartDate
    												,PeriodEndDate
    												,IsCurrent
    												,DataSource
    											)
    	SELECT	@RiskIndicator
    			,a.Licensee
    			,a.ConnectID
    			,a.ArmID
    			,a.XplanID
    			,ISNULL(t.RiskCustomers, 0)															AS Customers
    			,ISNULL(t.TotalCustomers, 0)														AS TotalCustomers
    			,ISNULL(ISNULL(t.RiskCustomers, 0)/t.TotalCustomers, 0)								AS InvestmentRatio
    			,IIF(ISNULL(t.RiskCustomers, 0)/t.TotalCustomers > @CustomerRatio, 'High', 'Low')	AS Rating
    			,IIF(ISNULL(t.RiskCustomers, 0)/t.TotalCustomers > @CustomerRatio, 3, 0)			AS Score
    			,0 																					AS AEWSScore
    			,'N/A' 																				AS AEWSRating
    			,@ReportFromDate
    			,@ReportToDate
    			,@IsCurrent
    			,'FMS/FirstWrap/Xplan' AS DataSource
    	FROM (
    				SELECT	DISTINCT Licensee
    						,ConnectID
    						,ArmID
    						,XplanID
    				FROM ap.Adviser_RiskIndicatorAccount_Test
    				WHERE	1=1
    					AND PeriodStartDate = @ReportFromDate
    					AND PeriodEndDate = @ReportToDate
    			) a
    	LEFT JOIN (
    					SELECT	ArmID
    							,CONVERT(FLOAT, COUNT(DISTINCT CustomerNumber))					AS TotalCustomers
    							,COUNT(DISTINCT IIF(RiskRating = 'High', CustomerNumber, NULL)) AS RiskCustomers
    					FROM ap.Adviser_RiskIndicatorCustomer_Test
    					WHERE	1=1
    						AND PeriodStartDate = @ReportFromDate
    						AND PeriodEndDate = @ReportToDate
    						AND RiskIndicator = @RiskIndicator
    						--AND Inflow > @InflowThreshold
    					GROUP BY ArmID
    				) t ON a.ArmID = t.ArmID
    
    	SET @RowsInserted = @@ROWCOUNT;
    
    	UPDATE t
    	SET t.AEWSScore		= s.TotalScore,
    		t.AEWSRating	= CASE t.Licensee
    								WHEN 'Pathways' THEN IIF(s.TotalScore <= 5, 'Low', IIF(s.TotalScore <= 9, 'Medium', 'High'))
    								WHEN 'Financial Wisdom Ltd' THEN IIF(s.TotalScore <= 4, 'Low', IIF(s.TotalScore <= 8, 'Medium', 'High'))
    								WHEN 'Count Financial Ltd' THEN IIF(s.TotalScore < 5, 'Low', IIF(s.TotalScore < 9, 'Medium', 'High'))
    								ELSE IIF(s.TotalScore <= 6, 'Low', IIF(s.TotalScore <= 10, 'Medium', 'High'))	--CFP and others
    						   END
    	FROM ap.Adviser_RiskIndicator_Test t
    	INNER JOIN (
    					SELECT	ConnectID
    							,PeriodStartDate
    							,PeriodEndDate
    							,SUM(Score) AS TotalScore
    					FROM AR_Analytics.ap.Adviser_RiskIndicator_Test
    					GROUP BY ConnectID
    							,PeriodStartDate
    							,PeriodEndDate
    				) s ON t.ConnectID = s.ConnectID
    					AND t.PeriodStartDate = s.PeriodStartDate
    					AND t.PeriodEndDate = s.PeriodEndDate
    	WHERE	t.PeriodStartDate = @ReportFromDate
    		AND t.PeriodEndDate = @ReportToDate;
    
    COMMIT;
    
    UPDATE AR_Analytics.ap.LoadLog
    SET EndDateTime	= GETDATE(),
    	Records		= @RowsInserted
    WHERE LoadID = @LoadID
    

    END TRY

    BEGIN CATCH

    PRINT 'Error: ' + ERROR_MESSAGE()
    
    UPDATE AR_Analytics.ap.LoadLog
    SET ErrorMsg = ERROR_MESSAGE()
    WHERE LoadID = @LoadID
    
    ROLLBACK;
    

    END CATCH GO

  2. Assistant

    The SQL code you provided is a stored procedure designed to analyze and process financial data for advisers, specifically looking to identify customers with certain investment characteristics. Based on the comments within the code, we can identify several input tables that the stored procedure interacts with.

    Input Tables:

    1. ap.Adviser_RiskIndicatorAccount_Test - This table appears to hold account-level risk indicators for advisers, and it is used to aggregate inflow data and calculate investment ratios.

    2. ap.AssetAllocationReference_Morningstar - This table is referenced to check for duplicate APIR codes and may contain references related to asset allocation data.

    3. AP.RAW_Xplan_Client - While the direct usage isn't clear, it’s usually involved in providing client-specific information in financial applications.

    4. AR_Analytics.ap.WMOnestop_ClientOSCI - This table is joined to provide Survivor CIF values and find matching records based on customer numbers.

    5. AR_Analytics.ap.WMOnestop_ClientAccount - Another client-related table referenced in the context of obtaining Survivor CIF for clients attached to adviser accounts.

    6. ap.Adviser_RiskIndicator_Test - Used both for maintaining a summary of advisers' risk indicators and for calculating customer and investment ratios.

    7. ar.Analytics.ap.LoadLog - Useful for load logging and tracking progress and errors of the procedure execution.

    Additional Data Sources:

    1. ac.RAW_RPAY_Adviser
    2. ap.Adviser
    3. ap.RAW_Arm_CrossReference
    4. ac.RAW_Xplan_Adviser
    5. ac.RAW_FMS_DLD1066_FUM
    6. ac.RAW_FMS_DLD0926_TransactionHistory
    7. ac.RAW_Firstwrap_Fum
    8. ap.RAW_Xplan_Holdings
    9. ap.RAW_Xplan_Transaction
    10. ap.RAW_Xplan_AssetAllocation
    11. STG.RAW_MAPPING_FIRSTWRAP_PRODUCTCODE

    These additional tables are expected to be part of the broader data ecosystem being analyzed but are not directly mentioned in the part of the code you provided. From the comments, it seems they play roles in various data aggregations or provide necessary data for calculating performance metrics.

    Purpose of the Procedure:

    The procedure generates the following outputs based on the inputs:

    • ap.Adviser_RiskIndicatorCustomer_Test
    • ap.Adviser_RiskIndicator_Test

    It processes and evaluates customers based on factors such as inflow amounts and the ratio of investments in high-growth assets, ultimately producing summaries of risk indications for advisers. The comments in the procedure highlight its aim of filtering customers who meet specific investment thresholds, which is vital for financial analysis and adviser monitoring.