Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  T-sql filtering

Author  Topic  leoroy Starting Member 3 Posts Posted - 2009-07-21 : 10:26:43 Hi, I have a table like this. S1 ---------- SID ClassID Offset(depicts the value for the current month)===> for example 1304 denotes september 2008 .column already calculated GradeType testscore (int) sheetsused(int) The new table (already created) -------------- SID ClassID gradetype offset Gcompletelevel The records have to be partitioned by SID,ClassID, Gradetype order by offset Now here, Gcompletelevel has to be calculated in this way. =last record(S1.offset)- first record's(s1.offset) - [200 - sheetused(previous months sheets used) divided by first records (s1.sheetsused) + [200 - sheetsused( previous months sheets used )] Plus or + [200 - Last (s1.sheetsused) divided by sheetsused( first record of the next gradetype ) + [200 - Last (s1.sheetsused) ] fron table S1 partitioned by SID,ClassID, Gradetype To make it clear i will try to give an example. SID ClassID Offset Gradetype sheetsused 1 10 1300 A 100 (offset denotes the number of months from JAN 1900) 1 10 1301 A 150 1 11 1302 B 70 . . 1 11 1306 B 190 1 12 1307 C 50 The formula for gcompletelevel would be somethin like this gcompletelevel for (gradetype B)= 4 - (200-150) + (200-190) -------- -------- ====>3.60 70+(200-150) 50(200-190) to calculate the gcompletelevel for the (1,11,B) combination- . 4 =Difference between 1302-1306 , 200 is a constant . (200-150) --------------- 70+(200-150) 200= constant 150= sheetsused of the last record of th previous Gradetype(here,its A) 70=sheetsused of the first record of the gradetype B (200-190) ------------- 50(200-190) 190=sheetsused of the first record of the gradetype C 50=sheetsused of the last record of the gradetype B 1300= 06/2008 1301=07/2008 both have the gradetype A (student took 2 months to finish this gradetype) 1302=08/2008 Gradetype changed to B. Student advanced to next grade. 1306= Student Completed gradetype B.( in the example i used 4, for this reason (1306-1302) 1307 = Student advances to C gradetype The Example i used shows the levelcompletion for a SID,classid,GRADETYPE=B combination. This has to be completed for all the SID,CLASSID,GRADETYPE combination for each student . Which will help in finding the gradetype advancement . OUTPUT: SID ClassID gradetype offset Gcompletelevel 1 11 B 1302 3.60