SQLTeam.com | Weblogs | Forums

Query Help


#1

Any replacement of this query.
go
Declare @MaxCount int=1--(chnage param from 1 to 11)
/*
(I need to return the average records based on the Input param @MaxCount )
*/
Declare @DistCount int
Declare @Diff int

declare @dept table (id int,name varchar(10),pincode int)

declare @temp table (sno int,id int,name varchar(10),pincode int)

insert @dept select 1,'IT',1
insert @dept select 1,'IT',1
insert @dept select 1,'IT',1
insert @dept select 2,'ITES',1
insert @dept select 2,'ITES',1
insert @dept select 2,'ITES',1
insert @dept select 3,'HR',1
insert @dept select 3,'HR',1
insert @dept select 3,'HR',1
insert @dept select 4,'ADMIN',1
insert @temp
select Row_number()over(Partition by id,name order by id desc)SNo ,* from @dept

set @DistCount =(select COUNT(distinct name) from @dept)

 set @Diff=@MaxCount/@DistCount

  
 if( @Diff<=0)
 set  @Diff=1     
  
 if(@MaxCount>=@DistCount)
 Begin 
      select * from @temp
       Where SNo<=@Diff
       
        End
   else
   Begin
       select * from (select  *,Row_number() over( 
       order by len(name) desc )RNO from @temp
        Where  SNO=1) A Where RNO<=@MaxCount           
   End

#2

How will the average change? How will you use @MaxCount to do that? Please post some before the change and after the change examples of output


#3

Thanks gbritton

I've posted all my SQL query.Just change the IPut value Declare @MaxCount int=1--(chnage param from 1 to 11) you will find the my expected resultset.