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
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.
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?
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
Thanks for your assistance.. Well.. i does work.. is there something were the stratified sample picks the random records?
4 People are following this question.
