question

naraypl avatar image
naraypl asked

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][1] [1]: /storage/temp/2715-table-input-output.jpg
stored-proceduresuseruser-defined-functiontable-valuedsample-databases
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sule avatar image Sule commented ·
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.
0 Likes 0 ·
naraypl avatar image naraypl commented ·
Oops.. sorry for that.. what you said is correct.. the input should be like![Correct Input2][1] [1]: /storage/temp/2719-correct.jpg
0 Likes 0 ·
correct.jpg (17.5 KiB)
Sule avatar image Sule commented ·
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?
0 Likes 0 ·
naraypl avatar image naraypl commented ·
ok.. All i need is the stratified sample records. but need to pick randomly and should not pick records which started at first.
0 Likes 0 ·

1 Answer

·
Sule avatar image
Sule answered
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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

naraypl avatar image naraypl commented ·
Thanks for your assistance.. Well.. i does work.. is there something were the stratified sample picks the random records?
0 Likes 0 ·
Sule avatar image Sule naraypl commented ·
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
0 Likes 0 ·
naraypl avatar image naraypl naraypl commented ·
Thanks... works well.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.