SQLTeam.com | Weblogs | Forums

Getting rows from different projects based on percent

Hi,
i have a table
id int identity,
ProjectId,
Description,
Status bit --0 means row not proceesed yes

for example:
1,44,check door,1
1,32,bla bla,0
1,44,xxxx,0
1,44,xxx,0
1,44,ffffff,0
1,32,aaaaaa,1
1,55,dddddaaaaaa,0

i look for 2 options, where in each of them i want to get X rows from the DB :

  1. to get all Project's (in status==0),
    where from each project i will take the equal number of rows for each project from the total batch,for example
    if X==10
    and i have
    Projectid=20 with 25 rows status 0
    Projectid=44 with 10 rows status 0
    then i will get 5 and 5 (because there are 2 projects)
  2. like #1, but in % relative to the total rows in status 0
    example
    if X==10
    and i have
    Projectid=20 with 30 rows status 0 (means its 75% of total not processed)
    Projectid=44 with 10 rows status 0 (means its 25% of total not processed)
    then i will get "7.5" and "2.5" (because there are 2 projects, on of course to round it)

thanks

create table #projects(id int identity(1,1) , projectID int, Description varchar(50), Status bit)

insert into #projects
select distinct  44, name, 0  From sys.columns where object_id between 1 and 10
union
select distinct  20, name, 0  From sys.columns where object_id between 1 and 25

declare @x int = 10

;with src
as
(
select * , ROW_NUMBER() OVER (
      PARTITION BY projectID
      ORDER BY projectID
   ) row_num
  From #projects
)

select * From src where row_num between 1 and @x


drop table #projects


thanks