May 2, 2008 at 9:44 am
I was under the understanding that during an update the order of the columns and variables were resolved top down. I want to do a running total of sorts but I want the next row to get the value not the current.
BEGIN
DECLARE @rtINT, @dummy INT
CREATE TABLE #tbl( val INT, rt INT )
CREATE CLUSTERED INDEX idxTbl ON #tbl(val)
INSERT INTO #tbl (val)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
UPDATE #tbl SET
rt = COALESCE(@rt,0),
@rt = COALESCE(@rt,0) + val,
@dummy = val
SELECT * FROM #tbl
DROP TABLE #tbl
END
GO
returns
val rt
----------- -----------
1 1
2 3
3 6
I want it to return
val rt
----------- -----------
1 0
2 1
3 3
My thought was to set the rt column then set the variable for the next row so when it is evaluated then I will get the previous value. This is just a short example, the real data will have groupings etc but this should get my point across enough. Please help! I can elaborate more if needed but there is a lot of code involved with what I'm working with.
May 2, 2008 at 10:10 am
I think this does what you want:
[font="Courier New"]BEGIN
DECLARE @rt INT, @dummy INT
CREATE TABLE #tbl( val INT, rt INT )
CREATE CLUSTERED INDEX idxTbl ON #tbl(val)
INSERT INTO #tbl (val)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
UPDATE #tbl SET
rt = COALESCE(@rt,0) + @dummy - val - val,
@rt = COALESCE(@rt,0) + val,
@dummy = val
SELECT * FROM #tbl
DROP TABLE #tbl
END[/font]
I don't think you can really depend on this though. My first thought would be parallelism being a problem - you may have to set MAXDOP to 1, but the order in which an update happens is completely out of your control. This script depends on the update happening in sequential order and that does not seem like a good plan.
This could be done through a recursive query, a cursor loop (sorry), or a tally table.
I do like your idea though.
May 3, 2008 at 12:17 am
No, the order is predictable especially if you supress parallelism with MAXDOP... please see the following article...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
... and just say "NO" to cursors 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2008 at 5:28 am
That article, great by the way, is where I got the idea from. I realized I forgot the with index hint and then also added option(maxdop 1) and still get the undesired results...
BEGIN
DECLARE @rt INT, @dummy INT
CREATE TABLE #tbl( val INT, rt INT )
CREATE CLUSTERED INDEX idxTbl ON #tbl(val)
INSERT INTO #tbl (val)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
SET @rt = 0
UPDATE #tbl SET
rt = COALESCE(@rt,0),
@rt = COALESCE(@rt,0) + val,
@dummy = val
FROM #tbl WITH (INDEX(idxTbl))
OPTION(maxdop 1)
SELECT * FROM #tbl
DROP TABLE #tbl
END
GO
So what is it that I'm missing?
May 5, 2008 at 8:38 am
that still returns
val rt
----------- -----------
1 1
2 3
3 6
where I want it to return
val rt
----------- -----------
1 0
2 1
3 3
I actually want the running total to start at zero all the time and the next line has the running total without the current value.
May 5, 2008 at 8:43 am
Keith DuAime (5/5/2008)
That article, great by the way, is where I got the idea from. I realized I forgot the with index hint and then also added option(maxdop 1) and still get the undesired results...
Michael's code appears to do what you want... have you tried his code?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2008 at 8:49 am
Yes I did and it does work but I tweaked it to
UPDATE #tbl SET
rt = COALESCE(@rt,0) - val,
@rt = COALESCE(@rt,0) + val,
@dummy = val
not knowing why the dummy was being added then subtracted since my actual key is not numeric. Problem is that this is just an example. I am trying to get an amortized monthly amount that is calculated semesterly and wanted to start with a number that isn't a running total, actually set to the original value but then thereafter have a running total, thus the question why is the variable being evaluated before the column value? I have gotten a little close with my actual need but I cheated by putting in a record before the settlement date and then running the total starting in the settlement month.
I also tried
UPDATE #tbl SET
@rt = 0,
rt = @rt,
@dummy = val
thinking it would set @rt = 0 set the column, then re-evaluate but then returns
val rt
----------- -----------
1 1
2 2
3 3
Which is returns the current value, sets it to zero then adds the value. Then I tried
UPDATE #tbl SET
@rt = CASE WHEN val = 1 THEN 0 ELSE @rt END,
rt = @rt,
@dummy = val
which again returns
val rt
----------- -----------
1 1
2 3
3 6
Figuring I know when to reset the variable but don't know what I am missing to do exactly as I need.
May 6, 2008 at 6:24 am
The reason why the variable had the value subtracted twice is to make it work properly... you wanted the running total to NOT include the current value and that's one of the few ways to do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2008 at 11:35 am
ok, I am getting the result set I finally need but it's still pretty messy, I abbreviated all the data needed but this code will run alone. Any ideas on what can be tweaked to make it more readable. And now that you can all see where I was going with the questions...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tally]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tally
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tally]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE dbo.tally (
date_time datetime NOT NULL,
tally INT NOT NULL
)
GO
ALTER TABLE dbo.tally ADD CONSTRAINT pkTally PRIMARY KEY(date_time);
GO
ALTER TABLE dbo.tally ADD CONSTRAINT UnqTally UNIQUE(tally)
GO
INSERT INTO tally
SELECT DATEADD(dd, seq.id, '01/01/2000') date_time,--Date Time
seq.id + 1
FROM(
SELECT(a4.id + a3.id + a2.id + a1.id + a0.id) id
FROM(
SELECT 0 id UNION ALL
SELECT 1UNION ALL
SELECT 2UNION ALL
SELECT 3UNION ALL
SELECT 4UNION ALL
SELECT 5UNION ALL
SELECT 6UNION ALL
SELECT 7UNION ALL
SELECT 8UNION ALL
SELECT 9
)a0
CROSS JOIN(
SELECT 0 id UNION ALL
SELECT 10UNION ALL
SELECT 20UNION ALL
SELECT 30UNION ALL
SELECT 40UNION ALL
SELECT 50UNION ALL
SELECT 60UNION ALL
SELECT 70UNION ALL
SELECT 80UNION ALL
SELECT 90
)a1
CROSS JOIN(
SELECT 0 id UNION ALL
SELECT 100UNION ALL
SELECT 200UNION ALL
SELECT 300UNION ALL
SELECT 400UNION ALL
SELECT 500UNION ALL
SELECT 600UNION ALL
SELECT 700UNION ALL
SELECT 800UNION ALL
SELECT 900
)a2
CROSS JOIN(
SELECT 0 idUNION ALL
SELECT 1000UNION ALL
SELECT 2000UNION ALL
SELECT 3000UNION ALL
SELECT 4000UNION ALL
SELECT 5000UNION ALL
SELECT 6000UNION ALL
SELECT 7000UNION ALL
SELECT 8000UNION ALL
SELECT 9000
)a3
CROSS JOIN(
SELECT 0 idUNION ALL
SELECT 10000UNION ALL
SELECT 20000UNION ALL
SELECT 30000UNION ALL
SELECT 40000UNION ALL
SELECT 50000UNION ALL
SELECT 60000UNION ALL
SELECT 70000UNION ALL
SELECT 80000UNION ALL
SELECT 90000
)a4
)seq
GO
IF OBJECT_ID('ltdx') IS NOT NULL
DROP TABLE ltdx
GO
CREATE TABLE ltdx(
company_idCHAR(10),
issue_idCHAR(8),
settle_dateDATETIME,
cur_mat_dateDATETIME,
reoff_priceFLOAT,
disc_premFLOAT,
cum_iss_exp FLOAT,
org_prinFLOAT,
I_PAY_TYPECHAR(1),
rateFLOAT,
org_eff_intFLOAT,
irr_def_costFLOAT,
pd_methodCHAR(1),
ie_methodCHAR(1),
descriptionxCHAR(35),
portfolioCHAR(6),
curr_codeCHAR(6),
idINT IDENTITY(1,1) PRIMARY KEY,
CONSTRAINT pk1_ltd UNIQUE(company_id, issue_id)
)
GO
--Dataset from user entry
INSERT INTO ltdx
select 'CMP','WIRED','2007-06-06 00:00:00.000','2012-06-06 00:00:00.000','100','0','0','50000000','M','3','3','3','S','S','WIRE TXNS','PF','USD'
UNION ALL select 'CMP','VARIABLE','2008-01-09 00:00:00.000','2009-01-09 00:00:00.000','100','0','0','5000000','M','3','3','3','S','S','TEST VARIABLE RATE','PF','USD'
UNION ALL select 'CMP','CCY','2008-01-09 00:00:00.000','2009-01-09 00:00:00.000','100','0','0','3000000','M','3','3','3','S','S','FOREIGN CURRENCY','PF','CAD'
UNION ALL select 'CMP','30YR','2008-05-01 00:00:00.000','2015-11-01 00:00:00.000','99','10000','10000','1000000','S','5','5.171982','5','E','E','30YR WITH 10000 DISC','PF','USD'
UNION ALL select 'CMP','DISCOUNT','2006-01-01 00:00:00.000','2011-01-01 00:00:00.000','92.6404','36798','0','500000','S','10','12','10','E','E','DISCOUNTED BOND','PF','USD'
UNION ALL select 'CMP','DISCM','2006-01-01 00:00:00.000','2011-01-01 00:00:00.000','92.6404','36798','0','500000','M','10','12','10','E','E','DISCOUNTED BOND','PF','USD'
UNION ALL select 'CMP','PREM','2006-01-01 00:00:00.000','2011-01-01 00:00:00.000','108.1146','40573','0','500000','S','10','8.025284','10','E','E','PREMIUM','PF','USD'
IF OBJECT_ID('pLtdAmortization') IS NOT NULL
DROP PROCEDURE pLtdAmortization
GO
CREATE PROCEDURE pLtdAmortization(
@asCmpCHAR(10),
@asRefCHAR(8),
@asDpIcCHAR(2)
) AS
BEGIN
DECLARE
@lsCmpCHAR(10),
@lsRefCHAR(8)
SELECT
@lsCmp= NULLIF(@asCmp,'*'),
@lsRef= NULLIF(@asRef,'*')
CREATE TABLE #LtdAmort (
cmpCHAR(10),
refCHAR(8),
EffDateDATETIME,
StlDateDATETIME,
MtryDateDATETIME,
DiscPremIssCHAR(1),
OrgAmortAmtFLOAT,
faceFLOAT,
NumOfPmtYrTINYINT,
NumOfMthTINYINT,
IntRateFLOAT,
EffRateFLOAT,
AdjPrnFLOAT,
AmortExpFLOAT,
UnAmortBalFLOAT,
IntPaidFLOAT,
IntExpFLOAT
)
CREATE CLUSTERED INDEX IdxLtdAmort ON #LtdAmort (cmp, ref, EffDate)
--Get all the data needed including a record prior to the settlement date in order to have a running total start on the settlement date
INSERT INTO #LtdAmort (
cmp,
ref,
EffDate,
StlDate,
MtryDate,
DiscPremIss,
OrgAmortAmt,
face,
NumOfPmtYr,
NumOfMth,
IntRate,
EffRate,
AdjPrn,
AmortExp,
UnAmortBal,
IntPaid,
IntExp
)
SELECT
ltd.company_id,
ltd.issue_id,
DATEADD(MONTH,t.tally-2,ltd.settle_date),
ltd.settle_date,
ltd.cur_mat_date,
CASE @asDpIc
WHEN 'DP' THEN CASE WHEN ltd.reoff_price < 100 THEN 'D' ELSE 'P' END
WHEN 'IC' THEN 'I'
END,
CASE @asDpIc WHEN 'DP' THEN ltd.disc_prem ELSE ltd.cum_iss_exp END,
ltd.org_prin,
CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 4
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 2
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 1
ELSE 12
END,
CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 3
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 6
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 12
ELSE 1
END,
ltd.rate / 100,
CASE @asDpIc WHEN 'DP' THEN ltd.org_eff_int ELSE ltd.irr_def_cost END / 100,
ltd.org_prin - CASE @asDpIc WHEN 'DP' THEN (ltd.disc_prem * CASE WHEN ltd.reoff_price < 100 THEN 1 ELSE -1 END) ELSE ltd.cum_iss_exp END,
CASE @asDpIc WHEN 'DP' THEN (ltd.disc_prem * CASE WHEN ltd.reoff_price < 100 THEN 1 ELSE -1 END) ELSE ltd.cum_iss_exp END,
CASE @asDpIc WHEN 'DP' THEN (ltd.disc_prem * CASE WHEN ltd.reoff_price < 100 THEN 1 ELSE -1 END) ELSE ltd.cum_iss_exp END,
ROUND(ltd.org_prin * ltd.rate / 100 /
CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 4
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 2
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 1
ELSE 12
END /
CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 3
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 6
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 12
ELSE 1
END,2),
ltd.org_prin - CASE @asDpIc WHEN 'DP' THEN (ltd.disc_prem * CASE WHEN ltd.reoff_price < 100 THEN 1 ELSE -1 END) ELSE ltd.cum_iss_exp END
FROM ltdx ltd
INNER JOIN tally t ON
t.tally BETWEEN 1 AND DATEDIFF(MONTH,ltd.settle_date,ltd.cur_mat_date) + 2
WHERE
ltd.issue_id = COALESCE(@lsRef,ltd.issue_id) AND
ltd.company_id = COALESCE(@lsCmp,ltd.company_id) AND
(
(@asDpIc = 'DP' AND ltd.reoff_price <> 100 AND ltd.pd_method = 'E') OR
(@asDPIc = 'IC' AND ltd.cum_iss_exp > 0 AND ltd.ie_method = 'E')
)
ORDER BY ltd.company_id, ltd.issue_id, t.tally
DECLARE
@keyCHAR(10),
@IntPaidFLOAT,
@IntExpFLOAT,
@AmortExpFLOAT,
@AdjPrinFLOAT,
@UnAmortBalFLOAT,
@AmortExpRtFLOAT
UPDATE #LtdAmort SET
--Find the interest expence
@IntExp = IntExp = ROUND(CASE
WHEN StlDate = EffDate THEN (face - AmortExp) * EffRate / NumOfPmtYr / NumOfMth
WHEN DATEDIFF(MONTH,StlDate,EffDate) % NumOfMth = 0 THEN (@AdjPrin + @AmortExp) * EffRate / NumOfPmtYr / NumOfMth
ELSE @IntExp
END,2),
--Adjust the principal with the amortization amount
@AdjPrin = AdjPrn = ROUND(CASE
WHEN EffDate <= StlDate THEN AdjPrn
ELSE @AdjPrin
END + CASE
WHEN EffDate <= StlDate THEN 0
ELSE @AmortExp
END
,2),
--Get the amortization amount based on the effective rate and subtract the interest expence
@AmortExp = AmortExp = ROUND(CASE
WHEN EffDate = DATEADD(MONTH,-1,StlDate) THEN (face - AmortExp) * EffRate / NumOfPmtYr / NumOfMth
WHEN DATEDIFF(MONTH,StlDate,EffDate) % NumOfMth = 0 THEN @AdjPrin * EffRate / NumOfPmtYr / NumOfMth
ELSE @IntExp
END - IntPaid,2),
--Countdown running total
@UnAmortBal = UnAmortBal = ROUND(CASE
WHEN EffDate <= StlDate THEN UnAmortBal
ELSE @UnAmortBal
END -
COALESCE(@AmortExp,0),2),
--Dummy field for index
@key = ref
FROM #LtdAmort WITH(INDEX(IdxLtdAmort))
--Get rid of the record prior to settlement since it's no longer needed
DELETE FROM #LtdAmort WHERE EffDate < StlDate
--clean up amounts (this still needs work because there are cases where they should be negative)
UPDATE #LtdAmort SET
AdjPrn = ABS(AdjPrn),
AmortExp = ABS(AmortExp),
UnAmortBal = ABS(UnAmortBal)
--When the amount amortizes too much the data has to be set to zero
UPDATE #LtdAmort SET
AmortExp = 0,
UnAmortBal = 0
WHERE DiscPremIss IN ('D','I')
AND AdjPrn + AmortExp > face
--When the amount didn't amortize enough set the last record to the balance of the previous record
UPDATE #LtdAmort SET
AmortExp = Prev.UnAmortBal
FROM #LtdAmort
INNER JOIN #LtdAmort Prev ON
Prev.cmp = #LtdAmort.cmp AND
Prev.ref = #LtdAmort.ref AND
DATEADD(MONTH,1,Prev.EffDate) = #LtdAmort.EffDate
WHERE #LtdAmort.AmortExp = 0
AND Prev.UnAmortBal > 0
--more resetting when adjustment is bigger than the face of the instrument
UPDATE #LtdAmort SET
AdjPrn = face
WHERE DiscPremIss IN ('D','I')
AND AdjPrn > face
--Same deal for Premiums but the amount is the net not the gross
UPDATE #LtdAmort SET
AdjPrn = face,
AmortExp = AmortExp + UnAmortBal,
UnAmortBal = 0
WHERE DiscPremIss = 'P'
AND EffDate = MtryDate
UPDATE #LtdAmort SET
AdjPrn = #LtdAmort.face,
AmortExp = UnAmortBal - Amortexp,
UnAmortBal = 0
FROM #LtdAmort
INNER JOIN (
SELECT cmp, ref, MAX(EffDate) EffDate
FROM #LtdAmort
GROUP BY cmp, ref
) LastRecord ON
#LtdAmort.cmp = LastRecord.cmp AND
#LtdAmort.ref = LastRecord.ref AND
#LtdAmort.EffDate = LastRecord.EffDate
WHERE #LtdAmort.UnAmortBal > 0
--And here we are with the result set, somewhat desired.
SELECT
@asCmp p_cmp,
@asRef p_ref,
@asDpIc p_dpic,
#LtdAmort.cmp,
#LtdAmort.ref,
#LtdAmort.EffDate,
#LtdAmort.StlDate,
#LtdAmort.MtryDate,
#LtdAmort.DiscPremIss,
#LtdAmort.OrgAmortAmt,
#LtdAmort.face,
#LtdAmort.NumOfPmtYr,
#LtdAmort.NumOfMth,
#LtdAmort.IntRate,
#LtdAmort.EffRate,
#LtdAmort.AdjPrn,
#LtdAmort.AmortExp,
#LtdAmort.UnAmortBal,
#LtdAmort.IntPaid,
#LtdAmort.IntExp,
ltd.descriptionx,
ltd.portfolio,
ltd.curr_code,
--cmp.NAME CMP_NAME,
--pf.NAME PF_NAME,
--ccy.curr_desc CCY_NAME
'Decsriptive info' descx
FROM #LtdAmort
INNER JOIN ltdx ltd ON
ltd.company_id = #LtdAmort.cmp AND
ltd.issue_id = #LtdAmort.ref
--LEFT OUTER JOIN company cmp ON cmp.company_id = ltd.company_id
--LEFT OUTER JOIN portcode pf ON pf.portfolio = ltd.portfolio
--LEFT OUTER JOIN currency ccy ON ccy.curr_type = ltd.curr_code
DROP TABLE #LtdAmort
END
GO
EXEC pLtdAmortization
'*',
'*',
'DP'
GO
May 7, 2008 at 6:35 pm
Keith DuAime (5/7/2008)
ok, I am getting the result set I finally need but it's still pretty messy, I abbreviated all the data needed but this code will run alone. Any ideas on what can be tweaked to make it more readable. And now that you can all see where I was going with the questions...
Yep... Let's start with your "Tally" table... Your's looks like this (I've added a command to measure the time it takes to run)...
[font="Courier New"]if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tally]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tally
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tally]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE dbo.tally (
date_time datetime NOT NULL,
tally INT NOT NULL
)
GO
SET STATISTICS TIME ON
INSERT INTO tally
SELECT DATEADD(dd, seq.id, '01/01/2000') date_time,--Date Time
seq.id + 1
FROM(
SELECT(a4.id + a3.id + a2.id + a1.id + a0.id) id
FROM(
SELECT 0 id UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
)a0
CROSS JOIN(
SELECT 0 id UNION ALL
SELECT 10 UNION ALL
SELECT 20 UNION ALL
SELECT 30 UNION ALL
SELECT 40 UNION ALL
SELECT 50 UNION ALL
SELECT 60 UNION ALL
SELECT 70 UNION ALL
SELECT 80 UNION ALL
SELECT 90
)a1
CROSS JOIN(
SELECT 0 id UNION ALL
SELECT 100 UNION ALL
SELECT 200 UNION ALL
SELECT 300 UNION ALL
SELECT 400 UNION ALL
SELECT 500 UNION ALL
SELECT 600 UNION ALL
SELECT 700 UNION ALL
SELECT 800 UNION ALL
SELECT 900
)a2
CROSS JOIN(
SELECT 0 id UNION ALL
SELECT 1000 UNION ALL
SELECT 2000 UNION ALL
SELECT 3000 UNION ALL
SELECT 4000 UNION ALL
SELECT 5000 UNION ALL
SELECT 6000 UNION ALL
SELECT 7000 UNION ALL
SELECT 8000 UNION ALL
SELECT 9000
)a3
CROSS JOIN(
SELECT 0 id UNION ALL
SELECT 10000 UNION ALL
SELECT 20000 UNION ALL
SELECT 30000 UNION ALL
SELECT 40000 UNION ALL
SELECT 50000 UNION ALL
SELECT 60000 UNION ALL
SELECT 70000 UNION ALL
SELECT 80000 UNION ALL
SELECT 90000
)a4
)seq
SET STATISTICS TIME OFF
GO
ALTER TABLE dbo.tally ADD CONSTRAINT pkTally PRIMARY KEY(date_time);
GO
ALTER TABLE dbo.tally ADD CONSTRAINT UnqTally UNIQUE(tally)
GO
[/font]
When I run that, I get...
[font="Courier New"]SQL Server Execution Times:
CPU time = 860 ms, elapsed time = 933 ms.
(100000 row(s) affected)[/font]
Now, let's compare all of that code to the following (which includes something you forgot... a GRANT)
[font="Courier New"]--===== If the Tally table already exists, drop it so we can recreate it
IF OBJECT_ID('dbo.Tally','U') IS NOT NULL
DROP TABLE dbo.Tally
GO
SET STATISTICS TIME ON
--===== Create and populate the Tally table on the fly
SELECT TOP 100000 --equates to more than 373 years of dates
Dt = ISNULL(DATEADD(dd,ROW_NUMBER() OVER (ORDER BY sc1.ID),'19000101'),0),
N = ISNULL(ROW_NUMBER() OVER (ORDER BY sc1.ID),0)
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
SET STATISTICS TIME OFF
--===== Add a Clustered Primary Key to the number to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100 --Maximizes performance on static table
--===== Add an index to the date column, also for performance
CREATE INDEX IX_Tally_Dt
ON dbo.Tally (Dt) WITH FILLFACTOR = 100 --Maximizes performance on static table
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
GO
[/font]
... and here's how long that code takes...
[font="Courier New"]SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 285 ms.
(100000 row(s) affected)[/font]
Not trying to have a race here, but the method I've shown is about 4 times faster and the code is a heck of a lot shorter. Also, for ease of future coding, I've changed the column names to very simple ones... N for the number column and Dt for the date column. Just about anyone who looks at code containing a "Numbers" or "Tally" or combined utility table, like this, will know what the columns are, immediately.
I also moved the Clustered Primary Key to N column just because most folks use the table as a Tally table more than they do a Calendar table. Since this is a purely "static" table (no changes after creation), I also changed the indexes to have a Fill Factor of 100 so we don't have to read past any blank space in the pages of the table... it's just one extra little kick for performance.
Of course, the column names and the preference for the PK are just my opinion... it's your table and you can set it up any way you like.
BTW, the ISNULL's that I used are just a little trick I learned to keep the ol' "Cannot assign primary key on nullable column" away since I'm using SELECT/INTO to actually define the table rather than declaring it explicitly.
Last but not least, since you have 373 years worth of dates available, I set the starting date way back so that the N column is actually the SQL "Date Serial" that matches the date. You still have about 265 future dates available.
I'll be back after I check out more of your code...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2008 at 8:06 pm
Heh... remember, you asked what could be done to make this more readable...
Here's one of your inserts including the indentation I found when I copied it... quick, you're troubleshooting the code for a problem in the NumOfMth column... which formula is it?
[font="Courier New"] INSERT INTO #LtdAmort (
cmp,
ref,
EffDate,
StlDate,
MtryDate,
DiscPremIss,
OrgAmortAmt,
face,
NumOfPmtYr,
NumOfMth,
IntRate,
EffRate,
AdjPrn,
AmortExp,
UnAmortBal,
IntPaid,
IntExp
)
SELECT
ltd.company_id,
ltd.issue_id,
DATEADD(MONTH,t.tally-2,ltd.settle_date),
ltd.settle_date,
ltd.cur_mat_date,
CASE @asDpIc
WHEN 'DP' THEN CASE WHEN ltd.reoff_price < 100 THEN 'D' ELSE 'P' END
WHEN 'IC' THEN 'I'
END,
CASE @asDpIc WHEN 'DP' THEN ltd.disc_prem ELSE ltd.cum_iss_exp END,
ltd.org_prin,
CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 4
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 2
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 1
ELSE 12
END,
CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 3
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 6
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 12
ELSE 1
END,
ltd.rate / 100,
CASE @asDpIc WHEN 'DP' THEN ltd.org_eff_int ELSE ltd.irr_def_cost END / 100,
ltd.org_prin - CASE @asDpIc WHEN 'DP' THEN (ltd.disc_prem * CASE WHEN ltd.reoff_price < 100 THEN 1 ELSE -1 END) ELSE ltd.cum_iss_exp END,
CASE @asDpIc WHEN 'DP' THEN (ltd.disc_prem * CASE WHEN ltd.reoff_price < 100 THEN 1 ELSE -1 END) ELSE ltd.cum_iss_exp END,
CASE @asDpIc WHEN 'DP' THEN (ltd.disc_prem * CASE WHEN ltd.reoff_price < 100 THEN 1 ELSE -1 END) ELSE ltd.cum_iss_exp END,
ROUND(ltd.org_prin * ltd.rate / 100 /
CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 4
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 2
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 1
ELSE 12
END /
CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 3
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 6
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 12
ELSE 1
END,2),
ltd.org_prin - CASE @asDpIc WHEN 'DP' THEN (ltd.disc_prem * CASE WHEN ltd.reoff_price < 100 THEN 1 ELSE -1 END) ELSE ltd.cum_iss_exp END
FROM ltdx ltd
INNER JOIN tally t ON
t.tally BETWEEN 1 AND DATEDIFF(MONTH,ltd.settle_date,ltd.cur_mat_date) + 2
WHERE
ltd.issue_id = COALESCE(@lsRef,ltd.issue_id) AND
ltd.company_id = COALESCE(@lsCmp,ltd.company_id) AND
(
(@asDpIc = 'DP' AND ltd.reoff_price <> 100 AND ltd.pd_method = 'E') OR
(@asDPIc = 'IC' AND ltd.cum_iss_exp > 0 AND ltd.ie_method = 'E')
)
ORDER BY ltd.company_id, ltd.issue_id, t.tally
[/font]
Just purely from an ease of troubleshooting standpoint, I make sure that every item in the SELECT list is labeled to match the INSERT. I also use indenting and other formatting to ensure that semi-complex formulas, like the ones with more than 1 CASE statement, are clearly "line broken" at logical breaks in the formula so you don't have to read a 140 character line all at once. I also limit things on a line to 120 characters so that you don't have to pan your screen left or right to see everthing. Most formulas fit vertically on a single screen and you can see the whole thing all at once if you use line breaks when it's logical.
So, here's what I'd probably end up with for formatted code... I'll probably get a lot of flack for putting the column aliases to the left of the = sign, but it sure does make things easy to read and find in the heat of battle. My understanding is that they're going to deprecate that in a couple of years, but until they do, it's just too handy to not use.
[font="Courier New"]--===== Get all the data needed including a record prior to the settlement date in order to have a running total start
-- on the settlement date
INSERT INTO #LtdAmort (
cmp,
ref,
EffDate,
StlDate,
MtryDate,
DiscPremIss,
OrgAmortAmt,
face,
NumOfPmtYr,
NumOfMth,--
IntRate,
EffRate,
AdjPrn,
AmortExp,
UnAmortBal,
IntPaid,
IntExp
)
SELECT
cmp = ltd.company_id,
ref = ltd.issue_id,
EffDate = DATEADD(MONTH,t.tally-2,ltd.settle_date),
StlDate = ltd.settle_date,
MtryDate = ltd.cur_mat_date,
DiscPremIss = CASE @asDpIc
WHEN 'DP' THEN CASE
WHEN ltd.reoff_price < 100 THEN 'D'
ELSE 'P'
END
WHEN 'IC' THEN 'I'
END,
OrgAmortAmt = CASE @asDpIc
WHEN 'DP' THEN ltd.disc_prem
ELSE ltd.cum_iss_exp
END,
face = ltd.org_prin,
NumOfPmtYr = CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 4
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 2
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 1
ELSE 12
END,
NumOfMth = CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 3
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 6
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 12
ELSE 1
END,
IntRate = ltd.rate / 100,
EffRate = CASE @asDpIc
WHEN 'DP' THEN ltd.org_eff_int
ELSE ltd.irr_def_cost
END
/ 100,
AdjPrn = ltd.org_prin
- CASE @asDpIc
WHEN 'DP' THEN (ltd.disc_prem * CASE
WHEN ltd.reoff_price < 100 THEN 1
ELSE -1
END)
ELSE ltd.cum_iss_exp
END,
AmortExp = CASE @asDpIc
WHEN 'DP' THEN (ltd.disc_prem * CASE
WHEN ltd.reoff_price < 100 THEN 1
ELSE -1
END)
ELSE ltd.cum_iss_exp
END,
UnAmortBal = CASE @asDpIc
WHEN 'DP' THEN (ltd.disc_prem * CASE
WHEN ltd.reoff_price < 100 THEN 1
ELSE -1
END)
ELSE ltd.cum_iss_exp
END,
IntPaid = ROUND(ltd.org_prin * ltd.rate / 100 / CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 4
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 2
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 1
ELSE 12
END
/ CASE
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 3
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 6
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 12
ELSE 1
END
,2),
IntExp = ltd.org_prin
- CASE @asDpIc
WHEN 'DP' THEN (ltd.disc_prem * CASE
WHEN ltd.reoff_price < 100 THEN 1
ELSE -1
END)
ELSE ltd.cum_iss_exp
END
FROM dbo.Ltdx ltd
INNER JOIN dbo.Tally t
ON t.Tally BETWEEN 1 AND (DATEDIFF(MONTH,ltd.settle_date,ltd.cur_mat_date) + 2)
WHERE ltd.issue_id = COALESCE(@lsRef,ltd.issue_id)
AND ltd.company_id = COALESCE(@lsCmp,ltd.company_id)
AND (
(@asDpIc = 'DP' AND ltd.reoff_price <> 100 AND ltd.pd_method = 'E')
OR
(@asDPIc = 'IC' AND ltd.cum_iss_exp > 0 AND ltd.ie_method = 'E')
)
ORDER BY ltd.company_id, ltd.issue_id, t.Tally
[/font]
One of the biggest problems I saw is how many places you have a join yet you don't use aliases on the columns... you just assume that the next guy is going to know which table each column comes from. That takes a huge amount of time to figure out when you're trying to troubleshoot code... You should always use table aliases on all columns, uniquely named or not, whenever you have a join involved. You also have places where you joined and elected to use the full table name instead of an alias. At the very least, that's inconsistant. Same goes for your use of AND... sometimes it's at the end of a line, sometimes it's at the beginning... even though one type is in the SELECT list and the other in a WHERE clause, it improves readability if you're consistant.
You have some logic in the code that others may not understand the "WHY" of. For example, there are some places in the code where if the value is less than 100, you assign a negative multiplier instead of a positive one. That should probably be better documented in the code. I'd also be temped to make a comment header and include a list of constants you used just to make it easier on the next guy. Remember that, about 99% of the time, the only place where's there's documentation about some code... is in the code itself. It doesn't matter if it becomes a book. My feeling is that you should be able to remove all the code and be able to write an business user level functional flow chart from the comments.
There's some other things I'd do, but they're all personal preferences on my part. For example, I don't indent for the very first BEGIN in a proc because it takes too much horizontal realestate.
By the way... you've done a very nice job of the "Divide'n'Conquer" thing. You didn't try to do it all in a single SELECT and that's good. You also did a nice job of hammering out some pretty complex algorithms and then handled the exceptions in separate updates... lot's of folks would, again, have tried to do it all in a single SELECT and that would have made for some slow code in the process. Well, done!
Last, but not least... I hope you don't take any of my recommendations on "readability style" personally... they're just recommendations on my part. Your style is a whole lot better than some of the single line "let it wrap" garbage that I normally get to see. And you didn't use all 1 case or the other which is a rare pleasure for me to view, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2008 at 7:06 am
Thanks for the info on the tally table. It was originially designed for 2k and not updated to 2k5 and didn't realize about the fill factor. Most of the reporting depends on the dates more than the number column. Most of our reports are daily reports and reference tables are cross joined to dates to left outer join data when it exists and running totals created in the report.
I didn't notice where I didn't put the table name but am sure you found it somewhere :hehe: but I'll keep my eye out for it.
I don't know how I got into the habbit of using and/or at the end sometimes and not others. Originally I used a proprietary cursor centric language and the and/or's were always in the beginning but I liked the AND at the end on the joins but I understand where you're going with it.
I did make a few assumptions and updated the code for people to understand more. I've been working with these tables for 10 years and I don't even understand it all, I'm told if this then that; I don't ask I just do. I've only been doing TSQL and PL/SQL for the past 2-3 years, yes when this is done and working I have to port it to Oracle :w00t:
Originally I was attempting to do everything at once, thus the original question of why aren't the variables being evaluated in the order I wanted. So once I got enough information I decided it best to 'fix' what was needed rather than mess up the update statement, that I messed up a few times, trying to get it all at once.
So anywho, here is my updated procedure and any more information would be great.
IF OBJECT_ID('pLtdAmortization') IS NOT NULL
DROP PROCEDURE pLtdAmortization
GO
CREATE PROCEDURE pLtdAmortization(
@asCmpCHAR(10),/*User may select a specific company or * for all*/
@asRefCHAR(8),/*User may select a specific issue or * for all */
@asDpIcCHAR(2)/*User must select DP(Discount/Premium) or IC(IssuanceCost)*/
) AS
BEGIN
/*
Trick to use coalesce in order to get index scan instead of table scan.
('*' = @var or table.field = @var) forces table scan
table.field = coalesce(@var,table.field) gets index seek/scan
*/
DECLARE
@lsCmpCHAR(10),
@lsRefCHAR(8)
SELECT
@lsCmp= NULLIF(@asCmp,'*'),
@lsRef= NULLIF(@asRef,'*')
/*Temp table for the calculations and report at the end*/
CREATE TABLE #LtdAmort (
cmpCHAR(10),/*Company issuing debt*/
refCHAR(8),/*Reference to lookup record*/
EffDateDATETIME,/*Each month from settlement to maturity for the amortization calculations*/
StlDateDATETIME,/*Settlement Date*/
MtryDateDATETIME,/*Maturity Date*/
DiscPremIssCHAR(1),/*Is the record a Discount, Premium or Issuance Cost related*/
OrgAmortAmtFLOAT,/*The Original Amortization amount*/
faceFLOAT,/*The Gross amount of the debt*/
NumOfPmtYrTINYINT,/*The Number of interest payments per year*/
NumOfMthTINYINT,/*The Number of months for an interest payment*/
IntRateFLOAT,/*The interest rate of the debt*/
EffRateFLOAT,/*Effective rate for amortization*/
AdjPrnFLOAT,/*The running total of the net amount +- the amortization*/
AmortExpFLOAT,/*The monthly amortization amount to report*/
UnAmortBalFLOAT,/*The balance of the unamortized amount*/
IntPaidFLOAT,/*The interest paid for a given month...Even though payments my be quarterly this is the reported amount booking for the month*/
IntExpFLOAT/*The interest expence for a given month, will be updated since it's based on then the adjusted principal*/
)
/*Without Jeff Moden, this would be a cursor driven procedure :) */
CREATE CLUSTERED INDEX IdxLtdAmort ON #LtdAmort (cmp, ref, EffDate)
/*
Get all the data needed including a record prior to the settlement date
in order to have a running total start on the settlement date
*/
INSERT INTO #LtdAmort (cmp,ref,EffDate,StlDate,MtryDate,DiscPremIss,OrgAmortAmt,face,NumOfPmtYr,NumOfMth,IntRate,EffRate,AdjPrn,AmortExp,UnAmortBal,IntPaid,IntExp)
SELECT
/*cmp*/ltd.company_id,
/*ref*/ltd.issue_id,
/*EffDate*/DATEADD(MONTH,t.tally-2,ltd.settle_date),
/*StlDate*/ltd.settle_date,
/*MtryDate*/ltd.cur_mat_date,
/*DiscPremIss*/CASE @asDpIc
WHEN 'DP' THEN
CASE WHEN ltd.reoff_price < 100 THEN 'D'/*Discounted*/
ELSE 'P'/*Premium*/
END
WHEN 'IC' THEN 'I'/*IssuanceCost*/
END,
/*OrgAmortAmt*/CASE @asDpIc
WHEN 'DP' THEN ltd.disc_prem/*Discount/Premium Amount*/
ELSE ltd.cum_iss_exp/*Issuance Cost Amount*/
END,
/*face*/ltd.org_prin,
/*NumOfPmtYr*/CASE /*Physical Number of payments per year*/
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 4/*Quarterly*/
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 2/*Semesterly*/
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 1/*Annually*/
ELSE 12/*Monthly, There are other options but they will be calculated monthly*/
END,
/*NumOfMnth*/CASE /*Number of months per payment*/
WHEN LTD.I_PAY_TYPE IN ('Q','J') THEN 3/*Quarterly*/
WHEN LTD.I_PAY_TYPE IN ('S','K') THEN 6/*Semesterly*/
WHEN LTD.I_PAY_TYPE IN ('A','L') THEN 12/*Annually*/
ELSE 1/*Monthly, There are other options but they will be calculated monthly*/
END,
/*IntRate*/ltd.rate / 100,
/*EffRate*/CASE
@asDpIc WHEN 'DP' THEN ltd.org_eff_int/*Discount/Premium rate*/
ELSE ltd.irr_def_cost/*Issuance Cost rate*/
END / 100,
/*AdjPrn*/ltd.org_prin -
CASE
@asDpIc WHEN 'DP' THEN/*Discount/Premium*/
ltd.disc_prem *
CASE
WHEN ltd.reoff_price < 100 THEN 1/*Discounted, Subtract the discount from the face*/
ELSE -1/*Premium, Add the discount to the face*/
END
ELSE ltd.cum_iss_exp/*Issuance Costs are always subtracted*/
END,
/*AmortExp*/CASE @asDpIc
WHEN 'DP' THEN/*Discount/Premium*/
ltd.disc_prem *
CASE
WHEN ltd.reoff_price < 100 THEN 1
ELSE -1
END
ELSE ltd.cum_iss_exp/*Issuance Costs*/
END,
/*UnAmortBal*/CASE
@asDpIc WHEN 'DP' THEN /*Discount/Premium*/
ltd.disc_prem *
CASE
WHEN ltd.reoff_price < 100 THEN 1/*Discount*/
ELSE -1/*Premium*/
END
ELSE ltd.cum_iss_exp/*Issuance Cost*/
END,
/*IntPaid*/ROUND(ltd.org_prin * ltd.rate / 100 / 12,2),/*The true calculate is face * IntRate / NumOfPmtYr,
however this report is month so it is always divided by 12*/
/*IntExp*/ltd.org_prin -
CASE
@asDpIc WHEN 'DP' THEN/*Discount/Premium*/
ltd.disc_prem *
CASE
WHEN ltd.reoff_price < 100 THEN 1/*Discount*/
ELSE -1/*Premium*/
END
ELSE ltd.cum_iss_exp/*IssuanceCost*/
END
FROM ltdx ltd
INNER JOIN tally t ON/*The number of months from settlement to maturity plus the month prior to settlement for running totals*/
t.tally BETWEEN 1 AND DATEDIFF(MONTH,ltd.settle_date,ltd.cur_mat_date) + 2
WHERE
ltd.issue_id = COALESCE(@lsRef,ltd.issue_id) AND
ltd.company_id = COALESCE(@lsCmp,ltd.company_id) AND
(
/*User selected discount/premium and the reoff_price must not be 100 and the method must be that of (E)ffective*/
(@asDpIc = 'DP' AND ltd.reoff_price <> 100 AND ltd.pd_method = 'E') OR
/*User selected Issuance cost and the cum_iss_exp must be greater than zero and the method must be that of (E)ffective*/
(@asDPIc = 'IC' AND ltd.cum_iss_exp > 0 AND ltd.ie_method = 'E')
)
ORDER BY ltd.company_id, ltd.issue_id, t.tally
DECLARE
@keyCHAR(10),/*Dummy field to help with INDEX hint*/
@IntExpFLOAT,/*The interest expence for the given month*/
@AmortExpFLOAT,/*The amortized amount for the given month*/
@AdjPrinFLOAT,/*Running total of the principle*/
@UnAmortBalFLOAT/*Running total of the Unamortized balance*/
UPDATE #LtdAmort SET
--Find the interest expence
@IntExp = IntExp = ROUND(CASE
/*When an issue is at the beginning use the
net amount(face-amortexp) * Effective rate / 12
remember this is a monthly report, not the true amortization schedule
*/
WHEN StlDate = EffDate THEN (face - AmortExp) * EffRate / 12
/*When the month is the same as the amortization schedule
recalculate the amortization scheduled amount and divide it for monthly reporting
*/
WHEN DATEDIFF(MONTH,StlDate,EffDate) % NumOfMth = 0 THEN (@AdjPrin + @AmortExp) * EffRate / 12
/*The month amount from the amortization schedule*/
ELSE @IntExp
END,2),
--Adjust the principal with the amortization amount
@AdjPrin = AdjPrn = ROUND(CASE
WHEN EffDate <= StlDate THEN AdjPrn
ELSE @AdjPrin
END + CASE
WHEN EffDate <= StlDate THEN 0
ELSE @AmortExp
END
,2),
--Get the amortization amount based on the effective rate and subtract the interest expence
@AmortExp = AmortExp = ROUND(CASE
WHEN EffDate = DATEADD(MONTH,-1,StlDate) THEN (face - AmortExp) * EffRate / 12
WHEN DATEDIFF(MONTH,StlDate,EffDate) % NumOfMth = 0 THEN @AdjPrin * EffRate / 12
ELSE @IntExp
END - IntPaid,2),
--Countdown running total
@UnAmortBal = UnAmortBal = ROUND(CASE
WHEN EffDate <= StlDate THEN UnAmortBal
ELSE @UnAmortBal
END -
COALESCE(@AmortExp,0),2),
--Dummy field for index
@key = ref
FROM #LtdAmort WITH(INDEX(IdxLtdAmort))
--Get rid of the record prior to settlement since it's no longer needed
DELETE FROM #LtdAmort WHERE EffDate < StlDate
--clean up amounts (this still needs work because there are cases where they should be negative)
UPDATE #LtdAmort SET
AdjPrn = ABS(AdjPrn),
AmortExp = ABS(AmortExp),
UnAmortBal = ABS(UnAmortBal)
--When the amount amortizes too much the data has to be set to zero
UPDATE #LtdAmort SET
AmortExp = 0,
UnAmortBal = 0
WHERE DiscPremIss IN ('D','I')
AND AdjPrn + AmortExp > face
--When the amount didn't amortize enough set the last record to the balance of the previous record
UPDATE #LtdAmort SET
AmortExp = Prev.UnAmortBal
FROM #LtdAmort
INNER JOIN #LtdAmort Prev ON
Prev.cmp = #LtdAmort.cmp AND
Prev.ref = #LtdAmort.ref AND
DATEADD(MONTH,1,Prev.EffDate) = #LtdAmort.EffDate
WHERE #LtdAmort.AmortExp = 0
AND Prev.UnAmortBal > 0
--more resetting when adjustment is bigger than the face of the instrument
UPDATE #LtdAmort SET
AdjPrn = face
WHERE DiscPremIss IN ('D','I')
AND AdjPrn > face
--Same deal for Premiums but the amount is the net not the gross
UPDATE #LtdAmort SET
AdjPrn = face,
AmortExp = AmortExp + UnAmortBal,
UnAmortBal = 0
WHERE DiscPremIss = 'P'
AND EffDate = MtryDate
UPDATE #LtdAmort SET
AdjPrn = #LtdAmort.face,
AmortExp = UnAmortBal - Amortexp,
UnAmortBal = 0
FROM #LtdAmort
INNER JOIN (
SELECT cmp, ref, MAX(EffDate) EffDate
FROM #LtdAmort
GROUP BY cmp, ref
) LastRecord ON
#LtdAmort.cmp = LastRecord.cmp AND
#LtdAmort.ref = LastRecord.ref AND
#LtdAmort.EffDate = LastRecord.EffDate
WHERE #LtdAmort.UnAmortBal > 0
--And here we are with the result set, somewhat desired.
SELECT
@asCmp p_cmp,
@asRef p_ref,
@asDpIc p_dpic,
#LtdAmort.cmp,
#LtdAmort.ref,
#LtdAmort.EffDate,
#LtdAmort.StlDate,
#LtdAmort.MtryDate,
#LtdAmort.DiscPremIss,
#LtdAmort.OrgAmortAmt,
#LtdAmort.face,
#LtdAmort.NumOfPmtYr,
#LtdAmort.NumOfMth,
#LtdAmort.IntRate,
#LtdAmort.EffRate,
#LtdAmort.AdjPrn,
#LtdAmort.AmortExp,
#LtdAmort.UnAmortBal,
#LtdAmort.IntPaid,
#LtdAmort.IntExp,
ltd.descriptionx,
ltd.portfolio,
ltd.curr_code,
cmp.NAME CMP_NAME,
pf.NAME PF_NAME,
ccy.curr_desc CCY_NAME
FROM #LtdAmort
INNER JOIN ltdx ltd ON
ltd.company_id = #LtdAmort.cmp AND
ltd.issue_id = #LtdAmort.ref
LEFT OUTER JOIN company cmp ON cmp.company_id = ltd.company_id
LEFT OUTER JOIN portcode pf ON pf.portfolio = ltd.portfolio
LEFT OUTER JOIN currency ccy ON ccy.curr_type = ltd.curr_code
DROP TABLE #LtdAmort
END
GO
May 8, 2008 at 7:45 am
I see Jeff has already smacked himself on the hand about using the assignment syntax for aliasing, so I won't pile on...:)
It's actually in the "deprecated file" called "in some (as yet undetermined) future version of SQL Server", so you'll get at least 3 if not six years out of that syntax yet....which isn't bad if you can keep your nose out of a given procedure for that long:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply