Previous Row + Current Row calculation

Question

Previous Row + Current Row calculation

Viewing 7 posts - 16 through 22 (of 22 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Okay. I think you provided incomplete info in your posts, unless I missed it, as I didn't see anything resetting @FinalData to 1 on an employee or year change. I did see your additional post regarding the year change, but that was after I had already worked on your problem based on the original post.

I think you should read this article: http://www.sqlservercentral.com/articles/Best+Practices/61537/ .

Follow its suggestions closely, and you should get better responses to your questions/problems. I will try looking at your problem a little more today if i have some extra time.

😎

karthik M One Orange Chip Points: 29139 More actions · Answer 2

karthik M

One Orange Chip

Points: 29139

May 19, 2008 at 7:04 am

#817144

I do agree my mistake.

karthik

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Here is my updated version. Please note that there is now a clustered index on EmpNo and PerDate. This is required for this to work. It is based on information from Jeff Moden's article: Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5, link -- http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

create table dbo.Emp (

EmpNo int,

PerDate datetime,

PerformanceData decimal(16,8),

GrossData decimal(16,8),

FinalData decimal(16,8),

primary key (EmpNo, PerDate)

)

insert into dbo.Emp (

EmpNo,

PerDate,

PerformanceData,

GrossData

)

select 1,'20061001',1.4534,4.3421 union all

select 1,'20061101',1.7656,7.2322 union all

select 1,'20060210',4.5673,2.5422 union all

select 1,'20071012',3.6785,3.1223 union all

select 1,'20070531',7.2132,5.4343 union all

select 2,'20061001',1.4534,4.3421 union all

select 2,'20061101',1.7656,7.2322 union all

select 2,'20060210',4.5673,2.5422 union all

select 2,'20071012',3.6785,3.1223 union all

select 2,'20070531',7.2132,5.4343

select * from dbo.Emp

declare @empno int,

@year int

set @empno = 0

set @year = 0

declare @FinalData decimal(16,8)

set @FinalData = 1.0

update dbo.Emp set

@FinalData = FinalData = round(case when (@empno <> EmpNo or @year <> year(PerDate)) then 1 else @FinalData end * (1 + PerformanceData)/50,8),

@empno = EmpNo,

@year = year(PerDate)

select * from dbo.Emp

drop table dbo.Emp

Edit: Made a slight change to my code, moved some lines from the top to closer to the update statement for clarity.

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

My only problem is may not have the math right. I can't get the same values when using Calculator, so please double check the equation used and provide feedback on the calculation.

thanks.

Edit: Actually, I guess it was just me and using Calculator. I was finally able to get the same values as SQL after doing the steps independently.

😎

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

Only thing left is to ask Karthik if he understands how it works and why... 😉

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

He should be able to if he takes the time to read your article. That is where I came up with the idea for this solution.

😎

karthik M One Orange Chip Points: 29139 More actions · Answer 7

Thanks for you reply.

Only thing left is to ask Karthik if he understands how it works and why...

As i haven't studied that article i am not in the position to explain the code. I will get back to you very shortly with my understanding.

🙂

karthik