Sampling : Select the sample records

• komal145

  SSCrazy Eights

  Points: 9915

  More actions

  January 27, 2023 at 8:41 pm

  #4142578

  hi,

  I have a data like below where i need to randomize data by review type

  Employee   Samplesize    disputeid  review type                               randomize data by review type

  Betty                      5             123                  invoice and payment                     1

  betty                      5             124                   invoice and payment                    2

  Betty                      5             125                     Formal batch cretaion                   1

  Betty                      5             126                     Formal batch creataion                2

  Betty                      5             127                     Formal batch creataion                3

  Betty                      5             128                     negotitation                                        1

  SInce i have sample size as 5. I need to pull 1 record for each  review type until sample size is met

  so, i will be pulling

  Employee   Samplesize    disputeid              review type                 randomize by review type        PULL randomly

   Betty                      5             123                  invoice and payment                     1                                        4

  betty                      5             124                   invoice and payment                    2                                        1

  Betty                      5             125                     Formal batch cretaion                   1                                      5

  Betty                      5             126                     Formal batch creataion                2                                      2

  Betty                      5             127                     Formal batch creataion                 3

  Betty                      5             128                     negotitation                                        1                                3

  please let me know. how can i achieve this ?

  • This topic was modified 1 year, 10 months ago by  komal145.
  • This topic was modified 1 year, 10 months ago by  komal145.
  • This topic was modified 1 year, 10 months ago by  komal145.

• Steve Collins

  SSCrazy

  Points: 2160

  More actions

  January 28, 2023 at 2:30 am

  #4142648

  Presumably there are other employees besides Betty?  If so it would be convenient to start with a table containing (unique) employees and the sample size.  In the case where you're only interested in Betty you could maybe try something like this

  declare 
    @employee     nvarchar(54)=N'Betty',
    @sample       int=5;
  
  with
  rand_cte as (
      select *, row_number() over (partition by review_type 
                                       order by newid()) rand_rn
      from your_table
      where employee=@employee)
  select top(@sample) *
  from rand_cte
  order by rand_rn;

  The ROW_NUMBER function uses NEWID to randomly order the partitioned rows.  Per  your example the code selects all of the rows where rand_rn=1 (there are 3), then all of the rows where rand_rn=2 (there are 2), etc. until the sample total is reached.  It uses TOP(n) and ORDER BY rand_rn to select across the partitioned random numbers

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• February 7, 2023 at 6:18 am

  This was removed by the editor as SPAM