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.