April 12, 2012 at 9:12 am
Hi,
Please suggest me how do i update the below workorderidtemp table colums claimid,cliamseq without using below cursor
I had a workorderid's which is having child id's as parentid's , i don't know how many parentid's does one workorderid have i need to update claimid as 1 those parentid's which comes under that workorderid.
At the same i need to update claimseq like if one workorderid have 10 parentid's the latest parentid has a claimseq 10 and workorderid has a claimseq 1.
i have sample table with data like
CREATE TABLE [dbo].[workorderidtemp](
[WorkOrderID] [int] NOT NULL,
[ParentID] [int] NULL,
[ClaimID] [int] NULL,
[ClaimSeq] [int] NULL,
[calldate] [datetime] NULL
) ON [PRIMARY]
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1','0','0','0',convert(datetime,'Jan 1 2001 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('11','1','0','0',convert(datetime,'Jan 1 2002 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('12','1','0','0',convert(datetime,'Jan 1 2003 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('13','1','0','0',convert(datetime,'Jan 1 2003 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('111','11','0','0',convert(datetime,'Jan 2 2004 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('112','11','0','0',convert(datetime,'Jan 5 2004 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('113','12','0','0',convert(datetime,'Jan 3 2004 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('114','13','0','0',convert(datetime,'Jan 3 2003 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('115','12','0','0',convert(datetime,'Jan 2 2004 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1111','111','0','0',convert(datetime,'Jan 2 2005 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1112','111','0','0',convert(datetime,'Jan 2 2006 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1113','113','0','0',convert(datetime,'Jan 2 2007 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1114','112','0','0',convert(datetime,'Jan 3 2005 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1115','113','0','0',convert(datetime,'Jan 8 2007 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1116','114','0','0',convert(datetime,'Jan 12 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('1117','115','0','0',convert(datetime,'Feb 2 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('21','2','0','0',convert(datetime,'Feb 23 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('22','2','0','0',convert(datetime,'Feb 25 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('23','2','0','0',convert(datetime,'Mar 2 2009 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('221','21','0','0',convert(datetime,'Dec 2 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('222','21','0','0',convert(datetime,'Feb 3 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('223','22','0','0',convert(datetime,'Feb 2 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('224','23','0','0',convert(datetime,'Feb 3 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('225','22','0','0',convert(datetime,'Feb 4 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2221','222','0','0',convert(datetime,'Feb 5 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2222','222','0','0',convert(datetime,'Feb 6 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2223','223','0','0',convert(datetime,'Feb 7 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2224','222','0','0',convert(datetime,'Feb 8 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2225','223','0','0',convert(datetime,'Feb 9 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2226','224','0','0',convert(datetime,'Feb 10 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2227','225','0','0',convert(datetime,'Feb 11 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('3','0','0','0',convert(datetime,'Mar 2 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('4','4','0','0',convert(datetime,'Apr 2 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('5','5','0','0',convert(datetime,'May 2 2008 12:00AM'))
INSERT workorderIdTemp(WorkOrderID,ParentID,ClaimID,ClaimSeq,calldate) VALUES('2','0','0','0',convert(datetime,'Feb 2 2008 12:00AM'))
where i need to update the claimid and claimseq
For one workorderid and those parentid will have claimid has a '1' and next workorderid and those parentid will have claimid has a '2'
At the same i need to update claimseq where one workorderid strats with claimseq '1' and its latest parentis will have latest claimseq
I have a cursor to updated these columns
update WorkOrderidtemp set ClaimId = Workorderid, ClaimSeq = 1 where parentid is null
declare @count int
declare @workorderid as int
declare @claimid as int
-- deallocate cur_WO
DECLARE cur_WO CURSOR FOR select workorderid, claimid from WorkOrderidTemp where claimId is not null and parentid is null
Open cur_WO
FETCH NEXT from cur_WO into @workorderid, @claimid
WHILE @@FETCH_STATUS = 0
Begin
select @count = 1
while @count > 0
begin
update WorkOrderidTemp set claimId = @claimid where parentid
in (select workorderid from WorkOrderidTemp where claimid = @claimid)
select @count = count(*) from WorkOrderidTemp where
parentid in (
select workorderid from WorkOrderidTemp where claimid = @claimid
) and claimId is null
end
FETCH NEXT from cur_WO into @workorderid, @claimid
End
But i need to update 12 lakhs records in my database ,
please is there any othere solution to update these columns
If i use these cursor it is taking too much of time
April 12, 2012 at 9:20 am
Can you provide a second table with the expected results based on the sample data you provided?
By the way, thank you for posting the DDL and sample data, that will help greatly.
April 12, 2012 at 9:30 am
Here is second table where i need to updated to claimid ,claimseq to claim table
Looking forward to ur answer
=================================================================================================================================
===Updating the ClaimID
--update WorkOrderidtemp set ClaimId = Workorderid, ClaimSeq = 1 where parentid is null
declare @count int
declare @workorderid as int
declare @claimid as int
-- deallocate cur_WO
DECLARE cur_WO CURSOR FOR select workorderid, claimid from WorkOrderidTemp where claimId is not null and parentid is null
Open cur_WO
FETCH NEXT from cur_WO into @workorderid, @claimid
WHILE @@FETCH_STATUS = 0
Begin
select @count = 1
while @count > 0
begin
update WorkOrderidTemp set claimId = @claimid where parentid
in (select workorderid from WorkOrderidTemp where claimid = @claimid)
select @count = count(*) from WorkOrderidTemp where
parentid in (
select workorderid from WorkOrderidTemp where claimid = @claimid
) and claimId is null
end
FETCH NEXT from cur_WO into @workorderid, @claimid
End
=============================================================================================================================================
CREATE TABLE [dbo].[claimtemp](
[ClaimID] [int] NOT NULL,
[ClaimSeq] [int] NULL,
[TotalAuthAmt] [money] NULL,
[TotalInvAmt] [money] NULL,
[Insert_TimeStamp] [datetime] NULL,
[Update_TimeStamp] [datetime] NULL,
[LastModifiedEmployeeID] [int] NULL
) ON [PRIMARY]
=============================================================================================================================================
===Updating the Claim sequence, Authamount and billamount
declare @count int
declare @workorderid as int
declare @claimid as int
declare @TotalAuthAmt money
declare @TotalInvAmt money
deallocate cur_cseq
DECLARE cur_cseq CURSOR FOR select workorderid,claimid from workorderidtemp
where claimid is not null order by claimid ,workorderid
OPEN cur_cseq
declare @prevClaimID as int
declare @id as int
set @id=0
set @prevClaimID=0
FETCH NEXT from cur_cseq into @workorderid, @claimid
WHILE @@FETCH_STATUS = 0
Begin
IF @prevClaimID = @ClaimID
set @id=@id+1
ELSE
Begin
set @id=1
select @TotalAuthAmt = sum(AuthAmount) from workcomponentTemp wc
where workid in (select workorderid from workorderidTemp where claimid = @ClaimID)
select @TotalInvAmt = sum(BillAmount) from VendorInvoiceWorkOrdertemp wc
where workOrderid in (select workorderid from workorderidTemp where claimid = @ClaimID)
declare @claimseqCount integer
select @claimseqcount = count(WorkorderID) from workorderidTemp where claimid=@claimid
insert into claim(claimid,claimseq,TotalAuthAmt,TotalInvAmt,Insert_Timestamp,Update_Timestamp)
values (@ClaimID,@claimseqcount,@TotalAuthAmt,@TotalInvAmt,getdate(),Null)
end
update workorderidtemp set claimseq=@id where workorderid=@workorderid
set @prevClaimID=@ClaimID
FETCH NEXT from cur_cseq into @workorderid, @claimid
End
=====================================================
But i need to update 12 lakhs records in my database ,
please is there any othere solution to update these columns
If i use these cursor it is taking too much of time
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply