CREATE PROCEDURE [SOE_SCENARIOS] AS
go
-----------------------------------------------------------------------
-- Declare variable step 1 & 3 will read for the filename 
-- and the scenario name it will save 
---------------------------------------------------------------------

BEGIN
DECLARE @wScenarioName Varchar(50)

DECLARE cur CURSOR FOR 
SELECT scenario_name FROM  ScenariosToRun
OPEN cur
FETCH NEXT FROM cur INTO @wScenarioName
WHILE @@fetch_status = 0
     BEGIN


		------------------------------------
		-- Step 1:  
		--         Creates SOEbyProduct table
		------------------------------------
		
		IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'SOEbyProduct')
				and OBJECTPROPERTY(id, N'IsUserTable') = 1)
		Begin
		     create table SOEbyProduct (
		     Scenario  char(20), Product char(50), BankName char(50), 
		     SumOfNotional  float, SumOfAvgBal  float, 
		     SumOfEOPBal float, SumOfIntCF float, SumOfAmortCF float, SumOfPrepayCF float, SumOfPrinCF float, 
		     SumOfAddAmt float, SumOfFTEIncome float,AvgAccruedInt float, AvgYield float, AvgPeriodCap  float, 
		     AvgCap float, AvgFloor float, AvgPriceRateBase float, AvgRepriceSpread float , AvgRepriceRate float)
		    CREATE INDEX  SoeID_IDX   on  @wScenarioName (AccountID)
		 --   CREATE INDEX  SoeID_IDX   on  soebasecase (AccountID)
		End
		
		ELSE
			Truncate TABLE SOEbyProduct
		
		
		-----------------------------------------------------------------------------
		-- Step2: 
		--
		--  CREATES a TEMPORY table for the CvgWriteOut_Account table 
		--  so that it can PARSE out the AccountName to obtain product & bankname
		------------------------------------------------------------------------------
		IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'Parsed_CVGWriteOut')
				and OBJECTPROPERTY(id, N'IsUserTable') = 1)
		Begin
		   create table Parsed_CVGWriteout  
		    ( [w_id]  int,
		     Scenid int,
		     Product char(50),
		     BankName char(50) )
		
		CREATE INDEX  ParseID_IDX   on  Parsed_CVGWriteout(w_id);
		END
		
		ELSE
			Truncate TABLE SOEbyProduct
		     
		Insert into Parsed_CVGWriteOut
		([w_id],scenid,product,bankname)
		
		select [id],scenid,
		--these would also work if function failed at some point - keep in here
		--replace(parsename(replace(replace(AccountName+'x','.','#~@'),':','.'),2),'#~@','.') as dimension,
		--replace(parsename(replace(replace(AccountName+'x','.','#~@'),':','.'),3),'#~@','.') as bank,
		--replace(parsename(replace(replace(AccountName+'x','.','#~@'),':','.'),4),'#~@','.') as product
		
		DBO.GetStringAtPos(AccountName,':',1 ),
		DBO.GetStringAtPos(AccountName,':',2 ) 
		from dbo.CvgWriteOut_Accounts as w
		
		
		------------------------------------------------------------------------------------------
		-- Step3:  
		--   
		--  Performs match b/w the above 2 files and create a sum/wavg file called SOEbyProduct 
		--   
		-------------------------------------------------------------------------------------------
		
		INSERT INTO SOEbyProduct
		(Scenario, Product, BankName,
		SumOfNotional, SumOfAvgBal, 
		SumOfEOPBal, SumOfIntCF, sumOfAmortCF, SumOfPrepayCF, SumOfPrinCF, 
		SumOfAddAmt, SumOfFTEIncome, AvgAccruedInt, AvgYield, AvgPeriodCap, 
		AvgCap, AvgFloor, AvgPriceRateBase, AvgRepriceSpread, AvgRepriceRate)
		
		SELECT 'wScenarioName' as Scenario,
		Product,
		BankName,
		sum(s.notional) as SumOfNotional,
		sum(s.avgbal) as SumOfAvgBal,
		sum(s.eopbal) as SumOfEOPBAL,
		sum(s.Intcf) as SumOfIntCF,
		sum(s.amortCF) as SumOfAmortCF,
		sum(s.prepaycf) as SumOfPrepayCF,
		sum(s.princf) as SumofPrinCF,
		-- if null value in field replaces it with 0.0 since floating, otherwise if int would have used 0
		sum (coalesce(s.addamt,0.0)) as SumAddamt,
		sum(coalesce(s.FTEIncome,0.0)) as SumofFTEIncome,
		sum(s.[accruedint]*s.[eopbal])/NULLIF(sum(s.[eopbal]),0.0) as AvgAccruedInt,
		sum(coalesce(s.[avgyld],0.0)*s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgYield,
		sum(s.[PeriodicCap] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgPeriodCap,
		sum(s.[Cap] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgCap,
		sum(s.[Floor] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgFloor,
		sum(s.[ReprRateBase] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgPriceRateBase,
		sum(s.[ReprRateAvgAdj] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgRepriceSpread,
		sum(s.[ReprRate] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgRepriceRate
		--from SOEBasecase as s   JOIN dbo.Parsed_CVGWriteOut as W 
		--on w.[w_id]=s.accountid
		from Parsed_CVGWriteout  as w   inner JOIN @wScenarioName as s 
		on w.[w_id]=s.accountid
		group by PRODUCT,BANKNAME,S.SCENDATEINDEX


    FETCH NEXT FROM CUR INTO @Wscenarioname

END
CLOSE cur
DEALLOCATE cur
END

quote:

---

Originally posted by cirugio

Hi Bohra,

Thank you for your feedback. I implemented your suggestion and ran into a small issue with step3 where it states

SELECT 'wScenarioName' as Scenario. This actually stores the value of 'wScenarioName', what I really need it to store is the
value in the scenario_name field that it is reading from the ScenariosToRun table. I guess it is the same value as what is stored
in the @wScenarioName. How can I do this?

---

quote:

---

--------------------
My overall thought process was to use the cursor so that I maybe able to execute the program numerous times, each time referencing a different table (same structure). I just did not want to have to repeat the logic 40+ time. The tablenames it needs to run each time are stored in the 'ScenariosToRun' table within the 'scenario_name' field. The value of this field is also stored
in step3 Select .... as Scenario line.

Maybe there is a quicker and better way? My apologies for my ignorance on this. Your help is greatly appreciate. Regards, cirugio

---

quote:

---

Originally posted by cirugio

I actually had tried that, but was getting the following error message. I am not sure why, for it is defined in the beginning of the program. Maybe the @sqlstmt plays into it?


Server: Msg 137, Level 15, State 2, Line 8
Must declare the variable '@wScenarioName'.

---

quote:

---

Originally posted by cirugio

Hi Bohra,

I changed it to --> SELECT ' +@wScenarioName+ ' as Scenario,
but now getting the following errors:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'SOEBaseCase'.(
7272 row(s) affected)

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'SOERampDn200'.
(7272 row(s) affected)

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'SOERampUp200'.
(7272 row(s) affected)

It appears to be returning the correct values (e.g. SOEBASE, SOERampDn200, SoeRampUP200), but that value is needs to be stored in the table SOEbyProduct's Scenario field . I am not sure how to store this value. I thinking some kind of additional quotes. I tried " quotes, but also received an error. Thoughts?

---

quote:

---

Originally posted by cirugio

That worked. Thanks.

But for some reason, even though it looks like it is using the cursor, it is only storing the last value the cursor points to.
For example, the ScenariosToRun table contains the following values:

SOEBaseCase
SOERampDn200
SOERampUp200

When the program runs through the cursor, the only value that gets stored from the
SELECT '''+ @wScenarioName + ''' as Scenario, statement is SOERampUp200 for all the records. Your thoughts on this are greatly appreciated. Regards,cirugio

---

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


ALTER                 PROCEDURE [SOE_SCENARIOS] AS

-----------------------------------------------------------------------------
-- Step1: 
--
-- CREATES a TEMPORY table containing parsed out data rom the  CvgWriteOut_Account table 
-- AccountName is parased into two seperate fields containing product & bankname
------------------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'Parsed_CVGWriteOut')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
create table Parsed_CVGWriteout 
( [w_id] int,
Scenid int,
Product char(50),
BankName char(50) )

CREATE INDEX ParseID_IDX on Parsed_CVGWriteout(w_id);
END

ELSE
Truncate TABLE Parsed_CVGWriteout


Insert into Parsed_CVGWriteOut
([w_id],scenid,product,bankname)

select [id],scenid,
--these would also work if function failed at some point - keep in here
--replace(parsename(replace(replace(AccountName+'x','.','#~@'),':','.'),2),'#~@','.') as dimension,
--replace(parsename(replace(replace(AccountName+'x','.','#~@'),':','.'),3),'#~@','.') as bank,
--replace(parsename(replace(replace(AccountName+'x','.','#~@'),':','.'),4),'#~@','.') as product

DBO.GetStringAtPos(AccountName,':',1 ),
DBO.GetStringAtPos(AccountName,':',2 ) 
from dbo.CvgWriteOut_Accounts as w


------------------------------------
-- Step 2: 
-- Creates SOEbyProduct table - which will stored aggregated data (sum/avgs)
-- 
------------------------------------

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'SOEbyProduct')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
create table SOEbyProduct (
Scenario char(20), Product char(50), BankName char(50), 
SumOfNotional float, SumOfAvgBal float, 
SumOfEOPBal float, SumOfIntCF float, SumOfAmortCF float, SumOfPrepayCF float, SumOfPrinCF float, 
SumOfAddAmt float, SumOfFTEIncome float,AvgAccruedInt float, AvgYield float, AvgPeriodCap float, 
AvgCap float, AvgFloor float, AvgPriceRateBase float, AvgRepriceSpread float , AvgRepriceRate float)
End
ELSE
Truncate TABLE SOEbyProduct


------------------------------------------------------------------------------------------
-- Step3: 
-- 
-- Performs match b/w the above 2 files and aggreates the summed/avegered data into the SOEbyProduct table
-- This step is performed multiple times for each table identified in the dbo.ScenariosToRun 
-------------------------------------------------------------------------------------------
BEGIN
DECLARE @wScenarioName Varchar(50)
DECLARE @sqlStmt Varchar (8000)

DECLARE cur CURSOR FOR 
SELECT scenario_name FROM ScenariosToRun
OPEN cur

FETCH NEXT FROM cur INTO @wScenarioName
Exec ('CREATE INDEX SoeID_IDX on ' + @wScenarioName +' (AccountID)')
WHILE @@fetch_status = 0
BEGIN

set @sqlStmt = 'INSERT INTO SOEbyProduct
(Scenario, Product, BankName,
SumOfNotional, SumOfAvgBal, 
SumOfEOPBal, SumOfIntCF, sumOfAmortCF, SumOfPrepayCF, SumOfPrinCF, 
SumOfAddAmt, SumOfFTEIncome, AvgAccruedInt, AvgYield, AvgPeriodCap, 
AvgCap, AvgFloor, AvgPriceRateBase, AvgRepriceSpread, AvgRepriceRate)
SELECT '''+ @wScenarioName + ''' as Scenario,
Product,
BankName,
sum(s.notional) as SumOfNotional,
sum(s.avgbal) as SumOfAvgBal,
sum(s.eopbal) as SumOfEOPBAL,
sum(s.Intcf) as SumOfIntCF,
sum(s.amortCF) as SumOfAmortCF,
sum(s.prepaycf) as SumOfPrepayCF,
sum(s.princf) as SumofPrinCF,
-- if null value in field replaces it with 0.0 since floating, otherwise if int would have used 0
sum (coalesce(s.addamt,0.0)) as SumAddamt,
sum(coalesce(s.FTEIncome,0.0)) as SumofFTEIncome,
sum(s.[accruedint]*s.[eopbal])/NULLIF(sum(s.[eopbal]),0.0) as AvgAccruedInt,
sum(coalesce(s.[avgyld],0.0)*s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgYield,
sum(s.[PeriodicCap] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgPeriodCap,
sum(s.[Cap] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgCap,
sum(s.[Floor] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgFloor,
sum(s.[ReprRateBase] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgPriceRateBase,
sum(s.[ReprRateAvgAdj] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgRepriceSpread,
sum(s.[ReprRate] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgRepriceRate
--from SOEBasecase as s JOIN dbo.Parsed_CVGWriteOut as W 
--on w.[w_id]=s.accountid
from Parsed_CVGWriteout as w inner JOIN ' + @wScenarioName +' as s 
on w.[w_id]=s.accountid
group by PRODUCT,BANKNAME,S.SCENDATEINDEX'

--Print @sqlStmt

Exec (@sqlStmt)



FETCH NEXT FROM CUR INTO @Wscenarioname

END
CLOSE cur
DEALLOCATE cur
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

quote:

---

FETCH NEXT FROM cur INTO @wScenarioName
Exec ('CREATE INDEX SoeID_IDX on ' + @wScenarioName +' (AccountID)')
WHILE @@fetch_status = 0

---

------------------------------------------------------------------------------------------
-- Step3: 
-- 
-- Performs match b/w the above 2 files and aggreates the summed/avegered data into the SOEbyProduct table
-- This step is performed multiple times for each table identified in the dbo.ScenariosToRun 
-------------------------------------------------------------------------------------------
BEGIN
   DECLARE @wScenarioName Varchar(50)
   DECLARE @sqlStmt Varchar (8000)
   DECLARE cur CURSOR FOR 
   SELECT scenario_name FROM ScenariosToRun
   OPEN cur

   FETCH NEXT FROM cur INTO @wScenarioName
--   IF NOT EXISTS (SELECT name from sysindexes WHERE name = 'soeid_idx')
--    Exec ('CREATE INDEX SoeID_IDX on ' + @wScenarioName +' (AccountID)')

WHILE @@fetch_status = 0
BEGIN
   IF NOT EXISTS (SELECT name from sysindexes WHERE name = 'soeid_idx')    
             Exec ('CREATE INDEX SoeID_IDX on ' + @wScenarioName +' (AccountID)')

set @sqlStmt = 'INSERT INTO SOEbyProduct
(Scenario, Product, BankName,
SumOfNotional, SumOfAvgBal, 
SumOfEOPBal, SumOfIntCF, sumOfAmortCF, SumOfPrepayCF, SumOfPrinCF, 
SumOfAddAmt, SumOfFTEIncome, AvgAccruedInt, AvgYield, AvgPeriodCap, 
AvgCap, AvgFloor, AvgPriceRateBase, AvgRepriceSpread, AvgRepriceRate)
SELECT '''+ @wScenarioName + ''' as Scenario,
Product,
BankName,
sum(s.notional) as SumOfNotional,
sum(s.avgbal) as SumOfAvgBal,
sum(s.eopbal) as SumOfEOPBAL,
sum(s.Intcf) as SumOfIntCF,
sum(s.amortCF) as SumOfAmortCF,
sum(s.prepaycf) as SumOfPrepayCF,
sum(s.princf) as SumofPrinCF,
-- if null value in field replaces it with 0.0 since floating, otherwise if int would have used 0
sum (coalesce(s.addamt,0.0)) as SumAddamt,
sum(coalesce(s.FTEIncome,0.0)) as SumofFTEIncome,
sum(s.[accruedint]*s.[eopbal])/NULLIF(sum(s.[eopbal]),0.0) as AvgAccruedInt,
sum(coalesce(s.[avgyld],0.0)*s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgYield,
sum(s.[PeriodicCap] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgPeriodCap,
sum(s.[Cap] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgCap,
sum(s.[Floor] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgFloor,
sum(s.[ReprRateBase] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgPriceRateBase,
sum(s.[ReprRateAvgAdj] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgRepriceSpread,
sum(s.[ReprRate] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgRepriceRate
--from SOEBasecase as s JOIN dbo.Parsed_CVGWriteOut as W 
--on w.[w_id]=s.accountid
from Parsed_CVGWriteout as w inner JOIN ' + @wScenarioName +' as s 
on w.[w_id]=s.accountid
group by PRODUCT,BANKNAME,S.SCENDATEINDEX'

--Print @sqlStmt

Exec (@sqlStmt)



FETCH NEXT FROM CUR INTO @Wscenarioname

END
CLOSE cur
DEALLOCATE cur
END 

quote:

---

IF NOT EXISTS (SELECT name from sysindexes WHERE name = 'soeid_idx')
Exec ('CREATE INDEX SoeID_IDX on ' + @wScenarioName +' (AccountID)')

---

------------------------------------------------------------------------------------------
-- Step3: 
-- 
-- Performs match b/w the above 2 files and aggreates the summed/avegered data into the SOEbyProduct table
-- This step is performed multiple times for each table identified in the dbo.ScenariosToRun 
-------------------------------------------------------------------------------------------
BEGIN
   DECLARE @wScenarioName Varchar(50)
   DECLARE @sqlStmt Varchar (8000)
   DECLARE cur CURSOR FOR 
   SELECT scenario_name FROM ScenariosToRun
   OPEN cur

   FETCH NEXT FROM cur INTO @wScenarioName

WHILE @@fetch_status = 0
BEGIN
   IF NOT EXISTS (SELECT name from sysindexes WHERE name = 'soeid_idx' and id = object_id(@wScenarioName)) 
   Exec ('CREATE INDEX SoeID_IDX on ' + @wScenarioName +' (AccountID)')

set @sqlStmt = 'INSERT INTO SOEbyProduct
(Scenario, Product, BankName,
SumOfNotional, SumOfAvgBal, 
SumOfEOPBal, SumOfIntCF, sumOfAmortCF, SumOfPrepayCF, SumOfPrinCF, 
SumOfAddAmt, SumOfFTEIncome, AvgAccruedInt, AvgYield, AvgPeriodCap, 
AvgCap, AvgFloor, AvgPriceRateBase, AvgRepriceSpread, AvgRepriceRate)
SELECT '''+ @wScenarioName + ''' as Scenario,
Product,
BankName,
sum(s.notional) as SumOfNotional,
sum(s.avgbal) as SumOfAvgBal,
sum(s.eopbal) as SumOfEOPBAL,
sum(s.Intcf) as SumOfIntCF,
sum(s.amortCF) as SumOfAmortCF,
sum(s.prepaycf) as SumOfPrepayCF,
sum(s.princf) as SumofPrinCF,
-- if null value in field replaces it with 0.0 since floating, otherwise if int would have used 0
sum (coalesce(s.addamt,0.0)) as SumAddamt,
sum(coalesce(s.FTEIncome,0.0)) as SumofFTEIncome,
sum(s.[accruedint]*s.[eopbal])/NULLIF(sum(s.[eopbal]),0.0) as AvgAccruedInt,
sum(coalesce(s.[avgyld],0.0)*s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgYield,
sum(s.[PeriodicCap] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgPeriodCap,
sum(s.[Cap] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgCap,
sum(s.[Floor] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgFloor,
sum(s.[ReprRateBase] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgPriceRateBase,
sum(s.[ReprRateAvgAdj] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgRepriceSpread,
sum(s.[ReprRate] *s.[eopbal])/nullif(sum(s.[eopbal]),0.0) as AvgRepriceRate
from Parsed_CVGWriteout as w inner JOIN ' + @wScenarioName +' as s 
on w.[w_id]=s.accountid
group by PRODUCT,BANKNAME,S.SCENDATEINDEX'

--Print @sqlStmt

Exec (@sqlStmt)

FETCH NEXT FROM CUR INTO @Wscenarioname

END
CLOSE cur
DEALLOCATE cur
END 

quote:

---

Originally posted by cirugio

OK. I will try it out now and will get back to you shortly. In the meanwhile, can you tell me do you think I need that 1st set of begin in the program? I was following another example, but not sure if its necessary??

---

quote:

---

1) If I really need the first BEGIN statement in step 3?
when I developed it, I was basing it off another example. It appears to run without it.

---

quote:

---

2) I am trying to understand why I need the set @sqlStmt = ' ? Would you mind providing me with a brief explanatin.
I want to ensure I have a good understnading so that if I have to code another cursor, I understand the reasoning behind it.

---

quote:

---

3) Last question, can you clarify for me why the following statemetn has to be an EXCEC and use ticks :
Exec ('CREATE INDEX SoeID_IDX on ' + @wScenarioName +' (AccountID)')

---