none
batch processing using SSIS

    Question

  • Hello everyone

                          Ranking should be done on SCHOOL (column) from STUDENT table and GRADE (column) from MATH table and READING table.

    There are 442 schools, 3,4,5,6,7,8,9,10 grades and 200000 students in all the schools. So, for the students of each school i need to calculate the ranking for 3-10 grades.

    Example: for suppose 6211 school have 3rd grade students, 4th grade students untill 10th grade students. you need to calculate ranking for all grade students of 6211. Then you need to calculate ranking for all grade students of 6212 same way. Then 6213, 6214, 6215, ................

    Is this possible through batch processing. If we can do this through batch processing, then how?

    STUDENT table contains student_id,school columns.

    MATH table contains student_id,grade,dss score columns.

    READING table contains student_id,grade,dss score columns.

    I need to calculate ranking for MATH  and READING for all the students, then load into PERCENTILE table.

    Tuesday, September 08, 2009 7:56 PM

All replies

  • You need to be a bit clearer.  Is ranking the average of all scores for a school?  If so, you could do something like this:

    INSERT Percentile (School, Grade, Ranking, Subject)
    SELECT st.school, m.Grade, AVG(m.[dss score]), 'Math'
    FROM    Student AS s
    JOIN     Math AS m ON s.Student_ID = m.Student_ID
    GROUP BY st.School, m.Grade

    INSERT Percentile (School, Grade, Ranking, Subject)
    SELECT st.school, r.Grade, AVG(r.[dss score]), 'Reading'
    FROM    Student AS s
    JOIN     Reading AS m ON s.Student_ID = r.Student_ID
    GROUP BY st.School, r.Grade
    Aaron Alton | thehobt.blogspot.com
    MCITP: Database Administrator
    Wednesday, September 09, 2009 1:57 AM
  • Ranking will be done on SCHOOL (column) from STUDENT table and GRADE (column) from MATH table and READING table.

    STUDENT table contains student_id,school columns. 

    MATH table contains student_id,grade,dss score columns. 

    READING table contains student_id,grade,dss score columns.

    There are 442 schools, 3,4,5,6,7,8,9,10 grades and 200000 students in all the schools. So, for the students of each school i need to calculate the ranking for 3-10 grades.

    Example: for suppose 6211 school have 3rd grade students, 4th grade students untill 10th grade students. you need to calculate ranking for all grade students of 6211. Then you need to calculate ranking for all grade students of 6212 same way. Then 6213, 6214, 6215, ................

    NOTE: STUDENT table is in one database and MATH table, READING table are in another database.

    I need to calculate ranking for MATH  and READING for all the students, then load into PERCENTILE table.                  

    Student_ID column is in STUDENT table, MATH table and READING table. But i need to take Student_ID column from STUDENT table only, because school column is in STUDENT table.

    PERCENTILE table is having 3 columns. Student_ID, MATH_RANKING, READING_RANKING

    So, i need 3 columns Student_ID, MATH_RANKING, READING_RANKING as output. So,that i can load that output to PERCENTILE table using SSIS package.

    As you did previously using rank function, it works either for MATH or for READING. Can you give me a suggestion, How can we get both the rankings in a single query output.

    In the below table, percentile_second_pass is the final rank, i need to get that percentile_second_pass for both MATH and READING in a single query output.

    Wednesday, September 09, 2009 2:20 AM
  • Ranking will be done on SCHOOL (column) from STUDENT table and GRADE (column) from MATH table and READING table.

    STUDENT table contains student_id,school columns. 

    MATH table contains student_id,grade,dss score columns. 

    READING table contains student_id,grade,dss score columns.

    There are 442 schools, 3,4,5,6,7,8,9,10 grades and 200000 students in all the schools. So, for the students of each school i need to calculate the ranking for 3-10 grades.

    Example: for suppose 6211 school have 3rd grade students, 4th grade students untill 10th grade students. you need to calculate ranking for all grade students of 6211. Then you need to calculate ranking for all grade students of 6212 same way. Then 6213, 6214, 6215, ................

    NOTE: STUDENT table is in one database and MATH table, READING table are in another database.

    I need to calculate ranking for MATH  and READING for all the students, then load into PERCENTILE table.                  

    Student_ID column is in STUDENT table, MATH table and READING table. But i need to take Student_ID column from STUDENT table only, because school column is in STUDENT table.

    PERCENTILE table is having 3 columns. Student_ID, MATH_RANKING, READING_RANKING

    So, i need 3 columns Student_ID, MATH_RANKING, READING_RANKING as output. So,that i can load that output to PERCENTILE table using SSIS package.

    As you did previously using rank function, it works either for MATH or for READING. Can you give me a suggestion, How can we get both the rankings in a single query output.

    In the below table, percentile_second_pass is the final rank, i need to get that percentile_second_pass for both MATH and READING in a single query output.

    Calculation for Percentile

     

    1.       Ranking will be done on school from the STUDENT and GRADE from the MATH or READ

    2.       Select school and grade level

    3.       Eliminate any students with a DSS (SSS develop) score of blank or zero.

    4.       Sort by DSS score (lowest to highest)

    5.       Take the row (record position and subtract 0.5.

    6.       Take the new value and divide by the number of rows (total students) for the school and grade level.

    7.       Multiple by 100.

    8.       Round off to a whole integer. 

    9.       A second pass through the table will be necessary for students with the same scores. Starting from the top of the table check the first student to the next student. If they have the same DSS score move the percentile ranking of the first student to the second. Continue the checking, moving to the second student and compare to the third student. Continue until you have reached the end of the table.

     


     Thanks

    Wednesday, September 09, 2009 2:20 AM