Declare @MaxSal bigint,
@NoOfRange int,@lowerlimit bigint,@higherlimit bigint,
@Initial int,
@RangeQuery nvarchar(max),@No_Of_Employee int,@UpdateQuery nvarchar(max)
set @Initial=0
set @MaxSal=(select Max(sal) from tbl_Salary)
--print @MaxSal
set @NoOfRange=(@MaxSal/999)+1
--print @NoOfRange
if OBJECT_ID('tempdb..#tblRange') is not null
drop table #tblRange
create Table #tblRange(LowerRange varchar(max),HigherRange varchar(max),
No_Of_Employee int)
--set @RangeQuery='insert into #tblRange(LowerRange,HigherRange) values '
while(@Initial<@NoOfRange)
begin
set @lowerlimit=@Initial*1000
set @higherlimit=@Initial*1000+999
--print @lowerlimit
--print @higherlimit
--set @RangeQuery=@RangeQuery+'('+@lowerlimit+','+@higherlimit+'),'
insert into #tblRange(LowerRange,HigherRange) values(@lowerlimit,@higherlimit)
print @RangeQuery
set @Initial=@Initial+1
end
--select * from #tblRange
Declare C1 Cursor for select LowerRange,HigherRange from #tblRange
open C1
fetch next from C1 into @lowerlimit,@higherlimit
while(@@FETCH_STATUS=0)
begin
set @No_Of_Employee=(select count(*) from tbl_Salary where sal between @lowerlimit and @higherlimit)
print @No_Of_Employee
--set @UpdateQuery='Update #tblRange set No_Of_Employee = '+@No_Of_Employee+' where LowerRange ='+@lowerlimit+' and HigherRange ='+@higherlimit+''
--print @UpdateQuery
--exec (@UpdateQuery)
Update #tblRange set No_Of_Employee =@No_Of_Employee where LowerRange=@lowerlimit and HigherRange=@higherlimit
fetch next from C1 into @lowerlimit,@higherlimit
end
close C1
deallocate C1
select LowerRange+'-'+HigherRange as Range, No_Of_Employee from #tblRange
--select LowerRange,HigherRange, No_Of_Employee from #tblRange
@NoOfRange int,@lowerlimit bigint,@higherlimit bigint,
@Initial int,
@RangeQuery nvarchar(max),@No_Of_Employee int,@UpdateQuery nvarchar(max)
set @Initial=0
set @MaxSal=(select Max(sal) from tbl_Salary)
--print @MaxSal
set @NoOfRange=(@MaxSal/999)+1
--print @NoOfRange
if OBJECT_ID('tempdb..#tblRange') is not null
drop table #tblRange
create Table #tblRange(LowerRange varchar(max),HigherRange varchar(max),
No_Of_Employee int)
--set @RangeQuery='insert into #tblRange(LowerRange,HigherRange) values '
while(@Initial<@NoOfRange)
begin
set @lowerlimit=@Initial*1000
set @higherlimit=@Initial*1000+999
--print @lowerlimit
--print @higherlimit
--set @RangeQuery=@RangeQuery+'('+@lowerlimit+','+@higherlimit+'),'
insert into #tblRange(LowerRange,HigherRange) values(@lowerlimit,@higherlimit)
print @RangeQuery
set @Initial=@Initial+1
end
--select * from #tblRange
Declare C1 Cursor for select LowerRange,HigherRange from #tblRange
open C1
fetch next from C1 into @lowerlimit,@higherlimit
while(@@FETCH_STATUS=0)
begin
set @No_Of_Employee=(select count(*) from tbl_Salary where sal between @lowerlimit and @higherlimit)
print @No_Of_Employee
--set @UpdateQuery='Update #tblRange set No_Of_Employee = '+@No_Of_Employee+' where LowerRange ='+@lowerlimit+' and HigherRange ='+@higherlimit+''
--print @UpdateQuery
--exec (@UpdateQuery)
Update #tblRange set No_Of_Employee =@No_Of_Employee where LowerRange=@lowerlimit and HigherRange=@higherlimit
fetch next from C1 into @lowerlimit,@higherlimit
end
close C1
deallocate C1
select LowerRange+'-'+HigherRange as Range, No_Of_Employee from #tblRange
--select LowerRange,HigherRange, No_Of_Employee from #tblRange
No comments:
Post a Comment