June 8, 2014 at 11:48 am
Hi everyone, I'm new to Cursors, I just have idea how they work..recently i'm working on cursors and planning to re-write code...but i'm finding difficulty . Please give your suggestions and ideas much appreciated.thanks in advance.
DECLARE @ID varchar(12)
DECLARE @Volume int, @Paid int, @Denied int
DECLARE @PaidAmount money, @DeniedAmount money
DECLARE @CID varchar(12), @CStatus varchar(2), @amt_paid money
SET @Volume = 0
SET @Paid = 0
SET @Denied= 0
SET @PaidAmount = 0.00
SET @DeniedAmount = 0.00
DECLARE MainTableCursor cursor FOR
SELECT distinct root_Num FROM T1
OPEN MainTableCursor
IF @@cursor_rows = 0
GOTO CloseCursor
FETCH next from MainTableCursor into @ID
WHILE @@fetch_status = 0
BEGIN
DECLARE SubtableCursor cursor
FOR
SELECT CID,CStatus, Amt_paid FROM T1
WHERE root_num=@ID AND CStatus= '02'
OPEN SubtableCursor
FETCH next from SubtableCursor into @CID,@CStatus,@amt_paid
WHILE @@fetch_status =0
BEGIN
SET @volume = @volume + 1
if @CStatus='02' and @amt_paid > 0
BEGIN
SET @Paid = @Paid + 1
SET @PaidAmount = @PaidAmount + @amt_paid
END
if @CStatus='02' and @amt_paid <= 0
BEGIN
SET @Denied = @Denied + 1
SET @DeniedAmount = @DeniedAmount + @amt_paid
END
FETCH next from SubtableCursor into @CID,@CStatus,@amt_paid
END
CLOSE SubtableCursor
DEALLOCATE SubtableCursor
INSERT INTO Table2
SELECT @ID, @volume as V, @Paid,@Denied, @PaidAmount,@DeniedAmount
SET @volume = 0
SET @Paid = 0
SET @Denied= 0
SET @PaidAmount = 0.00
SET @DeniedAmount = 0.00
FETCH next from MainTableCursor into @ID
END
CLOSE MainTableCursor
CloseCursor:
DEALLOCATE MainTableCursor
June 8, 2014 at 11:59 am
I'm betting some may get irritated that you spamed this post 3 times. In my limited experince posting it once is enough.:-P
***SQL born on date Spring 2013:-)
June 8, 2014 at 12:05 pm
Yeah.Sry.I'm new, don't know where exactly need to post..!! I never used cursors as well :..can you please guide me how to delete..thanks
June 8, 2014 at 12:13 pm
Looking at the code you posted, I have no idea where to start. There actually isn't much to work with.
You can start by reading these articles:
June 8, 2014 at 12:17 pm
Thanks for your reply and suggestions.!! 25million rows in source table and its taking more than 5hrs to run..is there any way to load all data into temp table? and do changes..finally loading into 'Final' Table..
June 8, 2014 at 12:55 pm
First, I reformatted your code:
DECLARE @ID varchar(12)
DECLARE @Volume int, @Paid int, @Denied int
DECLARE @PaidAmount money, @DeniedAmount money
DECLARE @CID varchar(12), @CStatus varchar(2), @amt_paid money
SET @Volume = 0
SET @Paid = 0
SET @Denied= 0
SET @PaidAmount = 0.00
SET @DeniedAmount = 0.00
DECLARE MainTableCursor cursor FOR
SELECT distinct root_Num FROM T1
OPEN MainTableCursor
IF @@cursor_rows = 0
GOTO CloseCursor
FETCH next from MainTableCursor into @ID
WHILE @@fetch_status = 0
BEGIN
DECLARE SubtableCursor cursor
FOR
SELECT CID, CStatus, Amt_paid FROM T1
WHERE root_num = @ID AND CStatus = '02'
OPEN SubtableCursor
FETCH next from SubtableCursor into @CID, @CStatus, @amt_paid
WHILE @@fetch_status =0
BEGIN
SET @volume = @volume + 1
if @CStatus ='02' and @amt_paid > 0
BEGIN
SET @Paid = @Paid + 1
SET @PaidAmount = @PaidAmount + @amt_paid
END
if @CStatus ='02' and @amt_paid <= 0
BEGIN
SET @Denied = @Denied + 1
SET @DeniedAmount = @DeniedAmount + @amt_paid
END
FETCH next from SubtableCursor into @CID, @CStatus, @amt_paid
END
CLOSE SubtableCursor
DEALLOCATE SubtableCursor
INSERT INTO Table2
SELECT @ID, @volume as V, @Paid,@Denied, @PaidAmount,@DeniedAmount
SET @volume = 0
SET @Paid = 0
SET @Denied= 0
SET @PaidAmount = 0.00
SET @DeniedAmount = 0.00
FETCH next from MainTableCursor into @ID
END
CLOSE MainTableCursor
CloseCursor:
DEALLOCATE MainTableCursor
This made it easier to read. You really need to take the time to format for readability.
Second, with no information regarding the tables involved and using only the code you posted I was able to come up with the following:
INSERT INTO Table2
select
root_Num,
count(root_Num) as Volume,
sum(case when Amt_paid > 0 then 1 else 0 end) as Paid,
sum(case when Amt_paid <= 0 then 1 else 0 end) as Denied,
sum(case when Amt_paid > 0 then Amt_paid else 0 end) as PaidAmt,
sum(case when Amt_paid <= 0 then Amt_paid else 0 end) as DeniedAmt
from
T1
where
CStatus = '02'
group by
root_Num;
I will not guarantee that this code works since I have nothing to test against. You should also take the time to read the first article I reference below in my signature block regarding what you should post and how to post it for the best possible answers and get tested code in return.
June 8, 2014 at 10:02 pm
Thank you for your valuable explanation.!!...I'm trying to implement same concept and get back to you..!! once gain thank you..!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply