Anil Diggiwal - Software Developer Jaipur India

alter proc ItemgroupwiseReport
as
begin
declare @same as nvarchar(100)
set @same=''
declare @GROUPNAME as nvarchar(100)
DECLARE @ITEMNAME as nvarchar(100)
DECLARE @TOTAL as numeric(15, 2)
DECLARE @TOATLITEM as numeric(15, 2)

DECLARE CURSOR1 cursor for
select ITEMGROUP,ITEMNAME,BALANCE from view_itemtotal  where procode=1 order by ITEMGROUP

OPEN CURSOR1
FETCH NEXT FROM CURSOR1 INTO @GROUPNAME,@ITEMNAME,@TOATLITEM
WHILE @@FETCH_STATUS <> -1
BEGIN

DECLARE @TEMPTOTAL as numeric(15, 2)
select @TEMPTOTAL=sum(BALANCE) from view_itemtotal  where procode=1 and ITEMGROUP=@GROUPNAME  group by ITEMGROUP

if(@GROUPNAME!=@same)
begin
insert into temp_itemgroup(GROUPNAME,ITEMNAME,TOATLITEM,TOTAL)values(@GROUPNAME,'',null,@TEMPTOTAL)
insert into temp_itemgroup(GROUPNAME,ITEMNAME,TOATLITEM,TOTAL)values('',@ITEMNAME,@TOATLITEM,@TOTAL)
set @same=@GROUPNAME
end
else
begin
insert into temp_itemgroup(GROUPNAME,ITEMNAME,TOATLITEM,TOTAL)values('',@ITEMNAME,@TOATLITEM,null)
end

FETCH NEXT FROM CURSOR1 INTO @GROUPNAME,@ITEMNAME,@TOATLITEM
END
CLOSE CURSOR1
DEALLOCATE CURSOR1
select * from temp_itemgroup
truncate table temp_itemgroup

end