February 13, 2008 at 12:02 pm
I am about to cause some serious damage at my workplace. This CTE is driving me NUTS.
I'm trying to update a column in a table with a calculated value from a CTE, only the column gets updated with one of the two calculated values, not the SUM. It doesn't matter how I try to do this, it simply doesn't work!!!!
Here's my code. Any help would be appreciated.
Declare @CalMonth tinyint, @SelecNum tinyint, @MyCnt tinyint;
Set @MyCnt = 0
Set @SelecNum = 4;
Set @CalMonth = 1;
--I've got 72 months repeated 4 times over
--(This is what the SelecNum value indicates).
--For each combination of SelecNum / CalMonth, the Incurred values are different.
--Select Distinct Sum(t1.Incurred + t2.Cumulative) as Cum, t1.Incurred,
t2.Cumulative, t1.CalMonth, t1.SelecNum--,
----row_Number() OVER (Order By t1.SelecNum, t1.CalMonth) as Test
--from LossesInc t1
--join LossesInc t2
--on t1.SelecNum = t2.SelecNum
--and t1.CalMonth = (t2.CalMonth - 1)
--where t1.SelecNum = @SelecNum
--and t1.CalMonth = @CalMonth
--Group By t1.Incurred, t2.Cumulative, t1.CalMonth, t1.SelecNum
----THIS CODE tells me that "doublecheck" is the correct value
While @MyCnt <= 72
Begin
With LossCalcs AS
(Select Distinct Sum(t1.Incurred + t2.Cumulative) as doublecheck,
t1.Incurred, t2.Cumulative, t1.CalMonth, t1.SelecNum,
row_Number() OVER (Order By t1.SelecNum, t1.CalMonth) as Test
from LossesInc t1
join LossesInc t2
on t1.SelecNum = t2.SelecNum
and t1.CalMonth = (t2.CalMonth - 1)
where t1.SelecNum = @SelecNum
and t1.CalMonth = @CalMonth
Group by t1.Incurred, t2.Cumulative, t1.CalMonth, t1.SelecNum )
Update li
Set li.Cumulative = (lc.Incurred + lc.Cumulative)
from LossesInc li
join LossCalcs lc
on li.CalMonth = lc.CalMonth
and li.SelecNum = lc.SelecNum
where li.CalMonth = @CalMonth
and li.SelecNum = @SelecNum;
--This updates Cumulative to the Incurred value of the "current"
-- calendar month plus the last month's Cumulative value. Only, it's
--actually updating the Cumulative column to the "Current" month's
--Incurred value instead of the sum of the two
Set @CalMonth = @CalMonth + 1;
Set @MyCnt = @MyCnt + 1;
END;
Maybe there's a better way to do this? I tried doing the CTE without the WHILE loop, but couldn't figure it out.
Is there not a way to do this with a CTE?
February 13, 2008 at 12:19 pm
Okay, this doesn't appear to be a CTE problem so much as it is a confusing addition problem.
ARGH.
I've changed this to a WHILE loop only and it's still giving me the same stuff. Here's the code and below the code is a few sample rows of data.
Declare @CalMonth tinyint, @SelecNum tinyint, @MyCnt tinyint;
Set @MyCnt = 0
Set @SelecNum = 4;
Set @CalMonth = 1;
/*
Select Distinct Sum(t1.Incurred + t2.Cumulative) as Cum, t1.Incurred,
t2.Cumulative, t1.CalMonth, t1.SelecNum--,
--row_Number() OVER (Order By t1.SelecNum, t1.CalMonth) as Test
from LossesInc t1
join LossesInc t2
on t1.SelecNum = t2.SelecNum
and t1.CalMonth = (t2.CalMonth - 1)
--where t1.SelecNum = @SelecNum
--and t1.CalMonth = @CalMonth
Group By t1.SelecNum,t1.CalMonth, t1.Incurred, t2.Cumulative
*/
While @SelecNum <= 4
Begin
While @MyCnt <= 72
Begin
Select li.CalMonth, li.SelecNum, li.Incurred, lc.CUM
from LossesInc li
join
(Select Distinct Sum(t1.Incurred + t2.Cumulative) as CUM, t1.Incurred,
t2.Cumulative, t1.CalMonth, t1.SelecNum
from LossesInc t1
join LossesInc t2
on t1.SelecNum = t2.SelecNum
and t1.CalMonth = (t2.CalMonth - 1)
where t1.SelecNum = @SelecNum
and t1.CalMonth = @CalMonth
Group by t1.Incurred, t2.Cumulative, t1.CalMonth, t1.SelecNum) lc
on li.CalMonth = lc.CalMonth
and li.SelecNum = lc.SelecNum
where li.CalMonth = @CalMonth
and li.SelecNum = @SelecNum
and li.CalMonth > 0;
Set @CalMonth = @CalMonth + 1;
Set @MyCnt = @MyCnt + 1;
END
Set @SelecNum = @SelecNum + 1
END;
--All cumulatives start off as zero except for CalMonth 0 which is
-- equal to its own Incurred
CalMonth Incurred Cumulative
0 0.00020979460.0002097946
1 0.01593378560.0000000000
2 0.03716121860.0000000000
3 0.03964429000.0000000000
4 0.04172909860.0000000000
5 0.04338725870.0000000000
Now the Cumulative for CalMonth 1 should equal .0161435802 and the cum for CalMonth 2 should equal .0533047988, etc. Does that make sense?
What the heck am I doing that it's setting the Cumulative column equal to the value in the incurred column?
Thanks,
February 13, 2008 at 12:28 pm
Do you need to reset mycount after the 72 records have been processed? Otherwise mycount will still be 72 for the second loop through @selectnum
February 13, 2008 at 12:30 pm
Cumulative is nothing but a high-falutin' way to say "running total". cumulative = (running total by seqnum of incurred over months)
considering how small the data sounds like it is (4 x 72 rows?) - you could use the triangular join method or Jeff's method with no deleterious side effects.
http://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx
----------------------------------------------------------------------------------
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?
February 13, 2008 at 12:37 pm
DOH! Found my error. I had t2.CalMonth - 1 and should have had t2.CalMonth + 1.
I feel sooooo stupid.
Thanks, guys. I appreciate it. As far as a triangular join goes, I've never done one of those. How does that work?
February 13, 2008 at 12:57 pm
you've seen them before, just not named this way. In Official Microsoft training speak, they're called "Range-based non-EquiJoins" or "comparison-based non-EquiJoin". They're dangerous because they cause really high cardinality on the set to be evaluated (in a true triangle join, 10,000 rows would require looking at (10000*(10000+1)/2) rows, or 50Million give or take 50 thousand rows).
A triangle join involves using in a join, instead of the =. But I'm now paraphrasing the article (with pretty pictures and all....) Read up on the other method - it's proprietary - but it's solid from what I can tell (with the caveats listed).
That being said, for running totals, they're the "official set-based way to handle", so on SMALL datasets like yours, they're not the end of the world. Just remember the scalability issue and you'll be fine.
In your case it would be:
UPDATE LossesInc
set cumulative = (select sum(Incurred ) from LossesInc Lin
where lin.selecnum=LossesInc.selecnum
and lin.CalMonth<=LossesInc.CalMonth)
The Correlated sub-query to outer query is a triangular join, due to the <=
----------------------------------------------------------------------------------
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?
February 14, 2008 at 3:49 am
Thanks, Matt. I appreciate the clarification.
Looks interesting. I'm going to have to test this one and see if it does any better or quicker than what I eventually came up with.
February 14, 2008 at 5:13 am
Wow, guys, you've been a big help.
I have another table in which I keep triangulated totals (not the same as a triangular join). I have to basically add the numbers of two different tables together, but there can be different combinations. So, for the first table, if I use the first set of totals (SelecNum = 1), I can have 4 different sets depending on if I use the first, second, third or fourth set of numbers from table two. It gets really really interesting.
So I created a stored procedure which runs in under 7 seconds. I plug in to variables A & B telling it what set of totals I want each table to use. So if I want totals 1 from both tables I do Exec uspMyProc 1,1 or Exec uspMyProc 1,4 if I want totals 1 from the first table and totals 4 from the second.
Anyway, this is all by way of saying I needed a running total on this table also for each distinct combination of totals. With your help, and all the references you guys gave me, I found a quick & dirty method of adding in a WHILE loop to the stored procedure which will get me the running totals depending on the combination of totals I'm using.
Table structure of LossesRptedTriangle is
CREATE TABLE [dbo].[LossesRptedTriangle](
[CalMonth] [tinyint] NULL,
[LossesRpt] [decimal](13, 10) NULL,
[LossesIncNum] [tinyint] NULL,
[ClmRptLagNum] [tinyint] NULL,
[Cumulative] [decimal](13, 10) NOT NULL
CONSTRAINT [DF_LossesRptedTriangle_Cumulative]
DEFAULT ((0.0000000000))
) ON [PRIMARY]
And the code I've put at the bottom of my stored procedure is
Update LossesRptedTriangle
Set Cumulative = LossesRpt
where CalMonth = 0
and LossesIncNum = @IncSelection
and ClmRptLagNum = @RptSelection;
--Initialize month zero Cumulative number for this "grouping"
Declare @CalMonth tinyint, @PrevCum decimal(13,10),
@RptSelection tinyint, @IncSelection tinyint;
--@RptSelection & @IncSelection get declared in the stored procedure
Set @IncSelection = 4; ---For troubleshooting
Set @RptSelection = 4; ---For troubleshooting
--These values actually get passed into the main stored procedure
Set @PrevCum = (Select Distinct Cumulative from LossesRptedTriangle
where LossesIncNum = @IncSelection
and ClmRptLagNum = @RptSelection
and CalMonth =0); --Initialize this with month zero Cumulative
--Select @PrevCum --For troubleshooting
Set @CalMonth = 1;--initialize the Calendar month
--I don't start with 0 because that's the base cumulative number
While @CalMonth <= 72
Begin
Update lr
Set @PrevCum = lr.Cumulative =
@PrevCum + lr.LossesRpt
from LossesRptedTriangle lr
where LossesIncNum = @IncSelection
and ClmRptLagNum = @RptSelection
and CalMonth = @CalMonth
Set @CalMonth = @CalMonth + 1;
END;
AND IT WORKS! It's not quite the Merry-Go-Round Index procedure Jeff Modem mentioned in his article, but it's quick (for this small data set) and efficient and I can pop it into my Proc without too much trouble on processing time or manual running after the fact.
Thanks, All! You've been incredibly helpful.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply