May 16, 2008 at 8:26 am
All,
I have one senario.
I have a table called Emp.
EmpNo PerDate PerformanceData GrossData FinalData
1 01/10/2006 1.4534 4.3421
1 01/11/2006 1.7656 7.2322
1 10/02/2006 4.5673 2.5422
1 12/10/2007 3.6785 3.1223
1 31/5/2007 7.2132 5.4343
My Procedure:
Create procedure p1
(
@dt datetime,
@eno int
)
as
begin
Declare @eno int,@date datetime,@pdata decimal(5,8),@gdata decimal(5,8)
Declare @FinalData decimal(5,8)
set @total = 1
Declare cursor c1
for select EmpNo,PerDate,PerformanceData,GrossData
from emp
for update
fetch c1 into @EmpNo,@PerDate,@PerformanceData,@GrossData
while @@fetch_status = 0
Begin
select @FinalData = round(@FinalData*(1+@performancedata)/50,8)
--Initially @FinalData value is 1
--During the second fetch previous value will be taken for @FinalData
--so calculations will be done by using the previous value
-- I.e previous @FinalData value + current @PerformanceData value
Update emp set FinalData = @FinalData
where EmpNo = @EmpNo
and PerData = @PerData
fetch c1 into @EmpNo,@PerDate,@PerformanceData,@GrossData
End
End.
My Requirement is :
I want to do this without using CURSOR. Please help me to get it resolved.
karthik
May 16, 2008 at 8:32 am
Please could you post an example of what you want your results to look like?
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 16, 2008 at 8:42 am
I want to update the below blank column by using the mentioned formula posted earlier.
EmpNo PerDate PerformanceData GrossData FinalData
1 01/10/2006 1.4534 4.3421
1 01/11/2006 1.7656 7.2322
1 10/02/2006 4.5673 2.5422
1 12/10/2007 3.6785 3.1223
1 31/5/2007 7.2132 5.4343
Say for example, i have updated the first row by using
declare @finaldata decimal(5,8)
set @finaldata = 1
so @FinalData = round(@FinalData *(1+@performanceData)/50,8)
= round(1*(1+1.4534)/50,8)
= 0.04906800
The above result will be stored in @FinalData
Now cursor will fetch the second row,
so @FinalData = round(@FinalData *(1+@performanceData)/50,8)
-- Here we took previous value of @FinalData
= round(0.04906800*(1+1.4534)/50,8)
= 0.002407670
like that we need to update FinalData column.
karthik
May 16, 2008 at 8:55 am
Sorry! A little modification in the procedure.
Create procedure p1
(
@dt datetime,
@eno int
)
as
begin
Declare @eno int,@date datetime,@pdata decimal(5,8),@gdata decimal(5,8)
Declare @FinalData decimal(5,8), @FinalData1 decimal(5,8)
set @FinalData = 1,@FinalData1 = 1
Declare cursor c1
for select EmpNo,PerDate,PerformanceData,GrossData
from emp
for update
fetch c1 into @EmpNo,@PerDate,@PerformanceData,@GrossData
while @@fetch_status = 0
Begin
if Datepart(yy,@perdata) = Datepart(@dt)-1 -- Assume @dt = getdate()
Begin
select @FinalData = round(@FinalData*(1+@performancedata)/50,8)
--Initially @FinalData value is 1
--During the second fetch previous value will be taken for @FinalData
--so calculations will be done by using the previous value
-- I.e previous @FinalData value + current @PerformanceData value
Update emp set FinalData = @FinalData
where EmpNo = @EmpNo
and PerData = @PerData
End
if Datepart(yy,@perdata) = Datepart(@dt)-2 -- Assume @dt = getdate()
Begin
select @FinalData1 = round(@FinalData1*(1+@performancedata)/50,8)
--Initially @FinalData1 value is 1
--During the second fetch previous value will be taken for @FinalData1
--so calculations will be done by using the previous value
-- I.e previous @FinalData1 value + current @PerformanceData value
Update emp set FinalData = @FinalData1
where EmpNo = @EmpNo
and PerData = @PerData
End
fetch c1 into @EmpNo,@PerDate,@PerformanceData,@GrossData
End
End.
karthik
May 16, 2008 at 8:55 am
Hi,
This might help you get a start in the right direction
;With MyCTE(ROWNUM,EmpNo,PerDate,PerformanceData,GrossData)
as
(SELECT
ROW_NUMBER() OVER (PARTITION BY EmpNo ORDER BY EmpNo,PerDate)
,EmpNo,PerDate,PerformanceData,GrossData
FROM @Vtbl)
SELECT
[1].ROWNUM
,[1].EmpNo
,[1].PerDate
,[1].PerformanceData
,[1].GrossData
,[2].PerformanceData
FROM MyCTE [1]
LEFT JOIN MyCTE [2]
ON [1].ROWNUM = [2].ROWNUM +1
I have made FinalData simple = the PerformanceData from the previous row ...
Hope that helps you get started.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 16, 2008 at 9:00 am
Christoper,
sorry for not including, i am using Sql2000.
karthik
May 16, 2008 at 9:01 am
he he he he
Sorry I should have read the forum heading 🙁
Have you tried using a table variable or derived table?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 16, 2008 at 9:03 am
Christoper,
sorry for not including, i am using Sql2000.
karthik
May 16, 2008 at 9:05 am
Have you tried using a table variable or derived table?
will it work out ?
karthik
May 16, 2008 at 9:34 am
Hi Christopher,
Here time is 9:00 PM ,So i have planned to leave. Please do post your reply.
karthik
May 16, 2008 at 9:48 am
Here is the sample data you provided:
EmpNo PerDate PerformanceData GrossData FinalData
1 01/10/2006 1.4534 4.3421
1 01/11/2006 1.7656 7.2322
1 10/02/2006 4.5673 2.5422
1 12/10/2007 3.6785 3.1223
1 31/5/2007 7.2132 5.4343
Being a visual type of person, it would help me if you would provide the expected output of your procedure as well. in other words, fill in the FinalData column with the appropriate output from your proedure so we have something to compare to. I've looked at your code, and I'm lost trying to actually figure out what you are doing.
😎
May 16, 2008 at 10:07 am
HI There,
Sorry about that.
Well you could use a temp table or table variable it's just another way of looping.
Why don't you want to use a cursor?
I don't like cursors which is why I wouldn't use it.
I'm sure there is a set-based way to do it in 2000 just haven't worked it out yet 😉
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 16, 2008 at 10:32 am
HI there,
Here is what I think is a set-based working copy.
(if it isn't please could someone tell me why it isn't set-based)
That aside this will work in 2000 without a loop (I think :hehe:)
DECLARE @VtblTemp TABLE
(ROWNUM INT IDENTITY PRIMARY KEY CLUSTERED
,EmpNo INT
,PerDate DATETIME
,PerformanceData DECIMAL(10,8)
,GrossData DECIMAL(10,8)
,FinalData DECIMAL(10,8))
INSERT INTO @VtblTemp
(EmpNo
,PerDate
,PerformanceData
,GrossData
,FinalData)
SELECT
EmpNo
,PerDate
,PerformanceData
,GrossData
,PerformanceData
FROM MyTable
SELECT
[1].ROWNUM
,[1].EmpNo
,[1].PerDate
,[1].PerformanceData
,[1].GrossData
--,[1].FinalDAta as[Current]
,(SELECT SUM(FinalData) FROM @VtblTemp [2] WHERE [2].ROWNUM <=[1].ROWNUM) as FinalData
FROM @VtblTemp [1]
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 16, 2008 at 10:49 am
I think this is what you are asking for, but realize that @FinalData will not reset to 1 when EmpNo changes. If that is also a requirement (not stated in your original post), then we will need to revisit this and use a running total type of approach.
create table dbo.Emp (
EmpNo int,
PerDate datetime,
PerformanceData decimal(16,8),
GrossData decimal(16,8),
FinalData decimal(16,8)
)
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
select * from dbo.Emp
declare @FinalData decimal(16,8)
set @FinalData = 1.0
update dbo.Emp set
@FinalData = FinalData = round(@FinalData*(1 + PerformanceData)/50,8)
select * from dbo.Emp
drop table dbo.Emp
before
EmpNo PerDate PerformanceData GrossData FinalData
----------- ----------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1 2006-10-01 00:00:00.000 1.45340000 4.34210000 NULL
1 2006-11-01 00:00:00.000 1.76560000 7.23220000 NULL
1 2006-02-10 00:00:00.000 4.56730000 2.54220000 NULL
1 2007-10-12 00:00:00.000 3.67850000 3.12230000 NULL
1 2007-05-31 00:00:00.000 7.21320000 5.43430000 NULL
after
EmpNo PerDate PerformanceData GrossData FinalData
----------- ----------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1 2006-10-01 00:00:00.000 1.45340000 4.34210000 0.04906800
1 2006-11-01 00:00:00.000 1.76560000 7.23220000 0.00271405
1 2006-02-10 00:00:00.000 4.56730000 2.54220000 0.00030220
1 2007-10-12 00:00:00.000 3.67850000 3.12230000 0.00002828
1 2007-05-31 00:00:00.000 7.21320000 5.43430000 0.00000465
Second Edit: Should also be noted that this is dependent on how SQL accesses the data as I didn't put a clustered index on dbo.Emp, as this was not information provided either. All code is provided as is with no warranty or guarantees!
😎
May 19, 2008 at 4:58 am
Thanks a lot Lynn Patel !
but realize that @FinalData will not reset to 1 when EmpNo changes
No, It has to reset for each employee. Also it has to rest for each employee different year.
I hope you have understood my requirement.If not,Please let me know.
Christoper,
Thanks a lot for giving your reply. I think your code will work in Sql2005. If i am wrong,Kindly let me know.
karthik
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply