with the following SQL codes, can you tell what input tables are? USE [AR_Analytics] GO…
- 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 = @LoadIDEND TRY
BEGIN CATCH
PRINT 'Error: ' + ERROR_MESSAGE() UPDATE AR_Analytics.ap.LoadLog SET ErrorMsg = ERROR_MESSAGE() WHERE LoadID = @LoadID ROLLBACK;END CATCH GO
- 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:
-
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.
-
ap.AssetAllocationReference_Morningstar - This table is referenced to check for duplicate APIR codes and may contain references related to asset allocation data.
-
AP.RAW_Xplan_Client - While the direct usage isn't clear, it’s usually involved in providing client-specific information in financial applications.
-
AR_Analytics.ap.WMOnestop_ClientOSCI - This table is joined to provide Survivor CIF values and find matching records based on customer numbers.
-
AR_Analytics.ap.WMOnestop_ClientAccount - Another client-related table referenced in the context of obtaining Survivor CIF for clients attached to adviser accounts.
-
ap.Adviser_RiskIndicator_Test - Used both for maintaining a summary of advisers' risk indicators and for calculating customer and investment ratios.
-
ar.Analytics.ap.LoadLog - Useful for load logging and tracking progress and errors of the procedure execution.
Additional 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
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.
-