CTE Problem

Question

Post reply

CTE Problem

Henrico Bekker

One Orange Chip

Points: 27652
More actions
March 1, 2012 at 11:33 pm

#254164

Hi all, been struggling with this a while and suspect I'm missing something very obvious....
The code below is not the actual code, but based on the results I want, and the logic as well....
create table test(currmonth datetime,
nextmonth datetime,
monthly decimal,
startval decimal,
result decimal)
insert into test (currmonth,nextmonth, monthly,startval)
Values ('2007-01-31', '2007-02-28', -0.59, 28.33),
('2007-02-28', '2007-03-31', -0.22, 28.33)
--result will be = current month 'monthly * 2'
--next row result will be = previous month 'result * monthly *2)
I've tried cte in a few ways, but can't seem to get the second row's result based on the first row's result value.
I need to update the same column with the previous result, not add another column.
Any suggestions, pulling my hair out, as I know this should be a simple piece of code...
thanks in advance.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

I took the liberty of adding an IDENTITY column to the base table.

Here is my stab at the issue

; with cte as

(

select id,currmonth,nextmonth, monthly,startval , cast( (monthly * 2.00) as decimal(23,4)) as ResultA

from #test

where id = 1

union all

select base.id , base.currmonth , base.nextmonth , base.monthly, base.startval

, cast( (rec.ResultA * base.monthly * 2.00) as decimal(23,4)) Result

from cte rec

inner join #test base

on base.id = rec.id + 1

)

select * from cte

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

This is a rolling balance problem ? correct ?

Quirky updates[/url] or a cursor are the way on those until sql2012.

Clear Sky SQL
My Blog[/url]

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

ColdCoffee (3/1/2012)
I took the liberty of adding an IDENTITY column to the base table.
Here is my stab at the issue
; with cte as
(
select id,currmonth,nextmonth, monthly,startval , cast( (monthly * 2.00) as decimal(23,4)) as ResultA
from #test
where id = 1
union all
select base.id , base.currmonth , base.nextmonth , base.monthly, base.startval
, cast( (rec.ResultA * base.monthly * 2.00) as decimal(23,4)) Result
from cte rec
inner join #test base
on base.id = rec.id + 1
)
select * from cte

Thanks, you are definitely onto something here, although the results for the second row calc to 0...

idcurrmonth nextmonth monthlystartvalResultA

12007-01-31 00:00:00.0002007-02-28 00:00:00.000-1 28 -0.200

22007-02-28 00:00:00.0002007-03-31 00:00:00.000 0 28 0.000

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Henrico Bekker (3/2/2012)
Thanks, you are definitely onto something here, although the results for the second row calc to 0...
idcurrmonth nextmonth monthlystartvalResultA
12007-01-31 00:00:00.0002007-02-28 00:00:00.000-1 28 -0.200
22007-02-28 00:00:00.0002007-03-31 00:00:00.000 0 28 0.000

That is because of of the way you set up the sample table. It uses just DECIMAL, defaulting it to precision - 18 and scale - 0. Thus decimal values are not shown.

create table test(currmonth datetime,

nextmonth datetime,

monthly decimal,

startval decimal,

result decimal)

Change your table to something like this

create table #test( id int identity,

currmonth datetime,

nextmonth datetime,

monthly decimal(23,4),

startval decimal(23,4),

result decimal(23,4))

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 5

Ok perfect, lets add a twist to it..

I've got different Codes as well.

The cte so far only returns rows for the same Code.

Keep in mind my ID, CODES do not always run in sequence.

Some codes mixed in the table as they are loaded in monthly batches, so they do not always follow on each other.

example attached.

the monthly loads need to calculate based on previous months last record, but they wont always be in sequence as other codes also exist....

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Henrico,

Heh... you've been around long enough... Post some data for folks to use. 😉 See the first link in my signature for how to make it easy for folks to help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)