none
Fetching rows from different projects based on percent or total amount RRS feed

  • Question

  • 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
    Thursday, August 22, 2019 7:10 AM

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.

    Friday, August 23, 2019 3:37 AM
    Moderator
  • 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

       . . .

    Friday, August 23, 2019 3:52 AM