From: esquel@sommarskog.se   
      
   Jane TT (janett@aol.com) writes:   
   > Hi, hope someone can help.  Although I have been using SQL for about 10   
   > years this problem has really got me stumped.   
   >   
   > I am using SQL Server 2000.   
   >   
   > I want to return the last five weeks data for each employee.  Not all   
   > employees are paid each week so I cannot call a set number of weeks.   
      
   We start with the (for the moment) useless advice:   
      
      WITH numbered AS (   
         SELECT empno, yeara, period, value,   
                rowno = row_number() OVER(PARTITION BY empno   
                                          ORDER BY yeara DESC, period DESC)   
      )   
      SELECT empno, yeara, period, value   
      FROM   tbl   
      WHERE  rn <=5   
      
   Or   
      
      SELECT a.empno, b.yeara, b.perioda, b.data   
      FROM   employees a   
      CROSS  APPLY (SELECT TOP 5 b.yeara, b.perioda, b.data   
                    FROM   employeedata b   
                    WHERE  a.empno = b.empno   
                    ORDER  BY b.yeard DESC, b.perioda DESC)   
      
   The reason that this is useless, is that both queries reqiures SQL 2005.   
      
   You can easily emulate row_number() on SQL 2000:   
      
      SELECT empno, yeara, period, value.   
      FROM   (SELECT a.empno, a.yeara, a.period, a.value,   
                      rowno = (SELECT COUNT(*)   
                               FROM   tbl b   
                               WHERE  b.empno = a.empno   
                                 AND  b.yeara <= a.yeara   
                                 AND  NOT (b.yeara = a.yeara AND   
                                           b.period > a.yeara))   
              FROM  tbl a) AS x   
      WHERE  rowno <= 5   
      
   There is a problem though: performance is likely to be if there is any   
   volume. And is not so much in terms of employees, but number of rows   
   per employeee.   
      
   One possibility is insert data into a temp table with an IDENTITY column,   
   and then use that for row number, and hope that ordering follows the   
   ORDER BY clause. It's someone uncertain whether you can rely on this on   
   SQL 2000. But here it goes:   
      
   INSERT #temp(empno, yeara, perioda, value)   
      SELECT empno, year, perioda, value   
      FROM   tbl   
      ORDER  BY empno, yeara, perioda   
      
   DELETE #temp   
   FROM   #temp a   
   WHERE  a.ident < (SELECT MAX(b.ident)   
                     FROM   #temp b   
                     WHERE  b.empno = a.empno)   
      
   #temp then has your data.   
      
   And when nothing else works, sigh, we can use a cursor.   
      
   DECLARE cur CURSOR STATIC LOCAL FOR   
      SELECT empno, year, perioda, value   
      FROM   tbl   
      ORDER  BY empno, yeara DESC, perioda DESC   
      
   OPEN cur   
      
   SELECT @rowno = 1, @old_empno = ''   
      
   WHILE 1 = 1   
   BEGIN   
      FETCH cur INTO @empno, @year, @perioda, @value   
      IF @@fetch_status <> 0   
         BREAK   
      
      IF @empno = @old_tempno   
      BEGIN   
         IF @rowno > 5   
            CONTINUE   
      
         INSERT #temp(empno, year, perioda, value)   
            VALUES (@empno, @year, @perioda, @value)   
      
         SELECT @rowno = @rowno + 1   
      END   
      ELSE   
         SELECT @rowno = 1, @old_empno = @empno   
   END   
      
   DEALLOCATE cur   
      
      
   Note: no code in this post is actually tested.   
      
   But the cursor above can be written smarter. Just loop over the employess   
   and run the TOP 5. But that is left as an exercise to the reader!   
      
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)