x

User defined Stratification Sample

Can anyone help in writing a script for User defined Stratification Sample. I have attached the input and output table (image). Also the user defined number will be in a separate table which i import everytime for pulling out the stratified sample. alt text

more ▼

asked Aug 18, 2015 at 10:22 AM in Default

avatar image

naraypl
1 1 1 4

Input2 has a bad structure. First row contains column names or data? For example, second column in first row has word 'Jack', is that column name or what?

I will suggest you to divide input from user in two part. First will be person name (e.g. Jack) and second part will be table for filtering data with columns Status1, Sub_Status1 and Stratified Sample Count. After that, create stored procedure with one classic input parameter for person name and one input parameter like Table-Valued Parameter.

Or use only Table-Valued Parameter, where you need to add one more column in Input2 for person name.

Aug 18, 2015 at 12:58 PM Sule

Oops.. sorry for that.. what you said is correct.. the input should be likeCorrect Input2

correct.jpg (17.9 kB)
Aug 18, 2015 at 07:30 PM naraypl

i'm curious, what if you have more then 2 rows that satisfied your condition from user input2? For examle, what if first row of input2 is

'Jack','Yes','Match',1 (not 2)

How do you know which row to show from input1 because you have 2 rows that match with input2 and count value is only 1?

Aug 19, 2015 at 07:34 AM Sule

ok.. All i need is the stratified sample records. but need to pick randomly and should not pick records which started at first.

Aug 19, 2015 at 09:17 AM naraypl
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Please, first read my commet (question) on your question. Try this:

 CREATE TABLE #Input1
 (
 Name varchar(100),
 Status1 varchar(100),
 Sub_Status1 varchar(100),
 Column1 char(3),
 Column2 char(3),
 Column3 char(3),
 Column4 char(3),
 Column5 char(3),
 Column6 char(3),
 )
 
 CREATE TABLE #Input2
 (
 Name varchar(100),
 Status1 varchar(100),
 Sub_Status1 varchar(100),
 StratCount int
 )
 
 INSERT INTO #Input1 (Name, Status1,Sub_Status1,Column1, Column2, Column3, Column4, Column5, Column6)
 SELECT 'Jack'    ,'Yes'    ,'Match'    ,'xxx','yyy','zzz','aaa','bbb','ccc' UNION ALL
 SELECT 'Danny'    ,'Yes'    ,'No_Match'    ,'yyy','xxx','aaa','bbb','bbb','zzz' UNION ALL
 SELECT 'Bob'    ,'No'    ,'Missing'    ,'yyy','xxx','aaa','bbb','bbb','zzz' UNION ALL
 SELECT 'Bob'    ,'Yes'    ,'No_Match'    ,'yyy','xxx','aaa','bbb','bbb','zzz' UNION ALL
 SELECT 'Bob'    ,'No'    ,'Incorrect','yyy','xxx','aaa','bbb','bbb','zzz' UNION ALL
 SELECT 'Jacklyn','No'    ,'Incorrect','yyy','xxx','aaa','yyy','yyy','xxx' UNION ALL
 SELECT 'Melissa','Yes'    ,'Match'    ,'yyy','xxx','aaa','yyy','yyy','xxx' UNION ALL
 SELECT 'Jack'    ,'Yes'    ,'No_Match'    ,'xxx','aaa','yyy','yyy','yyy','xxx' UNION ALL
 SELECT 'Jack'    ,'Yes'    ,'Match'    ,'aaa','yyy','aaa','yyy','xxx','xxx' UNION ALL
 SELECT 'Jack'    ,'No'    ,'Incorrect','aaa','yyy','aaa','yyy','xxx','xxx' UNION ALL
 SELECT 'Jack'    ,'No'    ,'Missing'    ,'aaa','yyy','aaa','yyy','xxx','xxx'
 
 INSERT INTO #Input2(Name,Status1,Sub_Status1,StratCount)
 SELECT 'Jack','Yes'    ,'Match'    ,2 UNION ALL
 SELECT 'Jack','Yes'    ,'No_Match'    ,1 UNION ALL
 SELECT 'Jack','No'    ,'Incorrect',1 
 
 SELECT * FROM #Input1
 SELECT * FROM #Input2
 
 
 ;WITH CTE (Name, Status1, Sub_Status1,Column1,Column2,Column3,Column4,Column5,Column6,RowNumber) AS
 (
 SELECT    Name,
         Status1,
         Sub_Status1,
         Column1,
         Column2,
         Column3,
         Column4,
         Column5,
         Column6,
         ROW_NUMBER() OVER(PARTITION BY Name,Status1,Sub_Status1 ORDER BY Name,Status1,Sub_Status1) AS RowNumber
 FROM #Input1
 )
 
 SELECT    c.Name,
         c.Status1, 
         c.Sub_Status1,
         c.Column1,
         c.Column2,
         c.Column3,
         c.Column4,
         c.Column5,
         c.Column6
 FROM CTE c
 INNER JOIN #Input2 i
     ON c.Name = i.Name
     AND c.Status1 = i.Status1
     AND c.Sub_Status1 = i.Sub_Status1
     AND c.RowNumber <= i.StratCount
more ▼

answered Aug 19, 2015 at 07:36 AM

avatar image

Sule
1.1k 3 4

Thanks for your assistance.. Well.. i does work.. is there something were the stratified sample picks the random records?

Aug 19, 2015 at 09:48 AM naraypl

If you want random rows then replace this:

ROW_NUMBER() OVER(PARTITION BY Name,Status1,Sub_Status1 ORDER BY Name,Status1,Sub_Status1) AS RowNumber

With this:

ROW_NUMBER() OVER(PARTITION BY Name,Status1,Sub_Status1 ORDER BY NEWID()) AS RowNumber

Aug 19, 2015 at 10:04 AM Sule

Thanks... works well.

Aug 19, 2015 at 11:27 AM naraypl
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476
x30
x29
x19
x-6

asked: Aug 18, 2015 at 10:22 AM

Seen: 686 times

Last Updated: Aug 19, 2015 at 11:27 AM

Copyright 2017 Redgate Software. Privacy Policy