Asked by:
Fetching rows from different projects based on percent or total amount
Question

Hi,
i have a table
id int identity,
ProjectId,
Description,
Status bit 0 means row not proceesed yesfor 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,0i 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 Moved by Lydia ZhangMicrosoft contingent staff, Moderator Friday, August 23, 2019 5:51 AM more appropriate
All replies

Hi want 2 learn,
Please try
create table Project ( id int identity(1,1), ProjectId int, [Description] varchar(64), [Status] bit ) insert into Project values (44,'check door',1), (32,'bla bla',0), (44,'xxxx',0), (44,'xxx',0), (44,'ffffff',0), (32,'aaaaaa',1), (55,'dddddaaaaaa',0), (55,'dddddaaa3aa',0) select * from Project /* 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) */ ;WITH CTE AS ( SELECT T.id,T.ProjectId,T.[Description],T.[Status] FROM Project T CROSS APPLY (SELECT COUNT(id) AS SUB_COUNT FROM Project WHERE [Status]=0 AND ProjectId=T.ProjectId ) T1 WHERE T.[Status]=0 AND T1.SUB_COUNT>=2 This is equal to X ), CTE_SUB AS ( SELECT ROW_NUMBER() over (partition by ProjectId order by id) as rn, id,ProjectId,[Description],[Status] FROM CTE ) SELECT id,ProjectId,[Description],[Status] FROM CTE_SUB WHERE rn<=(SELECT 2/COUNT(DISTINCT ProjectId) FROM CTE_SUB) /* 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) */ ;WITH CTE AS ( SELECT T.id,T.ProjectId,T.[Description],T.[Status],T1.SUB_COUNT, COUNT(T.id) OVER () AS Total_Count FROM Project T CROSS APPLY (SELECT COUNT(id) AS SUB_COUNT FROM Project WHERE [Status]=0 AND ProjectId=T.ProjectId ) T1 WHERE T.[Status]=0 AND T1.SUB_COUNT>=2 This is equal to X ) SELECT DISTINCT ProjectId, CAST(2*SUB_COUNT*1.0/Total_Count AS NUMERIC(8,1)) AS [% relative of the total] FROM CTE
Best Regards,
Will
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Proposed as answer by Will_KongMicrosoft contingent staff, Moderator Wednesday, August 28, 2019 7:13 AM

For the first problem, check this too:
select top(@X)
*,
rank() over ( partition by ProjectId order by NEWID() ) as r  or 'order by Id'
from MyTable
where [Status] = 0
order by r
For the second problem, maybe use
select top (@X) percent
. . .
 Edited by Viorel_MVP Friday, August 23, 2019 4:29 AM
 Proposed as answer by Will_KongMicrosoft contingent staff, Moderator Wednesday, August 28, 2019 7:13 AM