Query for establishing BMI weight loss in iSAMS MIS - compares last two measurements per student and selects where latest is less than previous, displaying total loss and days between. Ideally to be used as base for SSRS Report.

BMILoss.sql

IF OBJECT_ID( 'tempdb..#TempBMI'
            )IS NOT NULL
    BEGIN
        DROP TABLE #tempbmi;
    END; 

WITH cte
    AS ( SELECT txtschoolid , 
                txtbmidate , 
                intweight , 
                rownum = ROW_NUMBER(
                                   )OVER( ORDER BY txtschoolid , txtbmidate
                                        )
           FROM tblpupilmanagementhealthbmi b
       )
    SELECT prev.intweight PreviousWeight , 
           prev.txtschoolid PreviousTxtSchoolID , 
           prev.txtbmidate PreviousBMIDate , 
           cte.intweight , 
           cte.txtbmidate , 
           cte.txtschoolid INTO #tempbmi
      FROM
           cte LEFT JOIN cte prev
           ON prev.rownum
              = 
              cte.rownum - 1
               LEFT JOIN cte nex
           ON nex.rownum
              = 
              cte.rownum + 1; 

SELECT previousweight , 
       previoustxtschoolid , 
       CONVERT( datetime , previousbmidate
              ) , 
       schoolid , 
       intweight , 
       txtbmidate , 
       daterange , 
       totalloss , 
       p.txtfullname , 
       p.txtsurname , 
       p.txtforename
  FROM
       ( 
         SELECT previousweight , 
                previoustxtschoolid , 
                previousbmidate , 
                txtschoolid AS SchoolID , 
                txtbmidate , 
                intweight , 
                DATEDIFF( day , previousbmidate , txtbmidate
                        )AS DateRange , 
                ROUND( intweight - previousweight , 2
                     )AS totalLoss , 
                rowid = ROW_NUMBER(
                                  )OVER( PARTITION BY txtschoolid ORDER BY txtschoolid , txtbmidate DESC
                                       )
           FROM #tempbmi
       )t INNER JOIN tblpupilmanagementpupils p
       ON t.schoolid
          = 
          p.txtschoolid
  WHERE rowid = 1
    AND previoustxtschoolid
        = 
        t.schoolid
    AND previousweight
        > 
        intweight; 

DROP TABLE #tempbmi;