write a good Article

http://www.techtunes.com.bd/category/oracle
https://www.youtube.com/channel/UCxK1Lc5XTqq8-_7LxmDqy7g


EXEC [pr_GetPlanFundsCategories] 453063803
*/
ALTER PROCEDURE [dbo].[pr_GetPlanFundsCategories] (@PlanNumber INT) AS
Declare @PlanID Varchar(12),
@FundHistoryDate DateTime,
@ClientStatus Varchar(50)

SET NOCOUNT ON

Select @PlanID = PlanClientNumber, @ClientStatus = ISNULL(ClientStatus, '')
FROM PlanAccount where PlanNumber = @PlanNumber

--select @clientstatus
IF @ClientStatus IN ('Proposed','Setup')
BEGIN
Select DISTINCT UPPER(ISNULL(F.AssetClass404a5Report + ' ', 'Other ') + 'Funds') AS AssetClass
, convert(varchar(10), getdate(), 101) AS AsOfDate
, RL.RiskLevelID
from XAccountFund H
INNER JOIN Fund F ON H.FundID = F.FundID
LEFT JOIN tblFundRiskLevel RL ON ISNULL(F.AssetClass404a5Report, 'Other') = ISNULL(RL.AssetClass, '')
WHERE H.AccountID = @PlanNumber
AND (F.Status = 'Open' OR H.MarketValue IS NOT NULL)
ORDER BY RL.RiskLevelID
END

ELSE
BEGIN
SELECT @FundHistoryDate = Max(FundHistoryDate) from PlanFundHistory
SELECT DISTINCT UPPER(ISNULL(F.AssetClass404a5Report + ' ', 'Other ') + 'Funds') AS AssetClass
, convert(varchar(10), getdate(), 101) AS AsOfDate
, RL.RiskLevelID
from PlanFundHistory H
INNER JOIN Fund F ON H.CUSIPNUM = F.Cusip
LEFT JOIN tblFundRiskLevel RL ON ISNULL(F.AssetClass404a5Report, 'Other') = ISNULL(RL.AssetClass, '')
WHERE H.PlanID = @PlanID
AND (F.Status = 'Open' OR H.DollarAmount > 0) AND ISNULL(H.SuspenseCode, '') <> 'Yes'
AND H.FundHistoryDate = @FundHistoryDate
ORDER BY RiskLevelID
--SELECT RiskLevelID,UPPER(AssetClass+ ' Funds') AS AssetClass, '1/1/2000' as AsOfDate from tblFundRiskLevel

END