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.

I have come up with a way to get this information (see SELECT below) by
selecting just one employee but I need the report to include all
employees and it is not practical to hard code each employee.

I need some kind of FOR EACH statement. I have never used Cursors so I
have not tried this route.

I have created sample data, my data is much more involved with many more
employees. If I can get a query to return this data I will be able to
use it.

Thanks



--drop table #employeedata
create table #employeedata
( empno varchar(5),
yeara INT,
perioda INT,
value money
)

INSERT INTO #employeedata VALUES ('10',2010,1,100)
INSERT INTO #employeedata VALUES ('10',2010,2,100)
INSERT INTO #employeedata VALUES ('10',2010,3,100)
INSERT INTO #employeedata VALUES ('10',2010,4,100)
INSERT INTO #employeedata VALUES ('10',2010,5,100)
INSERT INTO #employeedata VALUES ('10',2010,6,100)
INSERT INTO #employeedata VALUES ('10',2010,7,100)
INSERT INTO #employeedata VALUES ('10',2010,8,100)
INSERT INTO #employeedata VALUES ('10',2010,9,100)
INSERT INTO #employeedata VALUES ('10',2010,10,100)
INSERT INTO #employeedata VALUES ('20',2010,1,100)
INSERT INTO #employeedata VALUES ('20',2010,2,100)
INSERT INTO #employeedata VALUES ('20',2010,3,100)
INSERT INTO #employeedata VALUES ('20',2010,4,100)
INSERT INTO #employeedata VALUES ('20',2010,5,100)
INSERT INTO #employeedata VALUES ('20',2010,6,100)
INSERT INTO #employeedata VALUES ('20',2010,7,100)
INSERT INTO #employeedata VALUES ('20',2010,8,100)
INSERT INTO #employeedata VALUES ('20',2010,9,100)
INSERT INTO #employeedata VALUES ('20',2010,10,100)
INSERT INTO #employeedata VALUES ('30',2010,1,100)
INSERT INTO #employeedata VALUES ('30',2010,2,100)
INSERT INTO #employeedata VALUES ('30',2010,3,100)
INSERT INTO #employeedata VALUES ('30',2010,4,100)
INSERT INTO #employeedata VALUES ('30',2010,5,100)
INSERT INTO #employeedata VALUES ('30',2010,6,100)
INSERT INTO #employeedata VALUES ('30',2010,7,100)
INSERT INTO #employeedata VALUES ('30',2010,8,100)
INSERT INTO #employeedata VALUES ('30',2010,9,100)
INSERT INTO #employeedata VALUES ('30',2010,10,100)
INSERT INTO #employeedata VALUES ('40',2010,1,100)
INSERT INTO #employeedata VALUES ('40',2010,2,100)
INSERT INTO #employeedata VALUES ('40',2010,3,100)
INSERT INTO #employeedata VALUES ('40',2010,4,100)
INSERT INTO #employeedata VALUES ('40',2010,7,100)
INSERT INTO #employeedata VALUES ('40',2010,9,100)
INSERT INTO #employeedata VALUES ('50',2010,1,100)
INSERT INTO #employeedata VALUES ('50',2010,2,100)
INSERT INTO #employeedata VALUES ('50',2010,3,100)
INSERT INTO #employeedata VALUES ('50',2010,4,100)
INSERT INTO #employeedata VALUES ('50',2010,5,100)
INSERT INTO #employeedata VALUES ('50',2010,6,100)
INSERT INTO #employeedata VALUES ('50',2010,10,100)

SELECT TOP 5 empno, yeara * 100 + perioda AS 'period', value
FROM #employeedata
WHERE empno = '10'
ORDER BY yeara * 100 + perioda DESC

SELECT TOP 5 empno, yeara * 100 + perioda AS 'period', value
FROM #employeedata
WHERE empno = '50'
ORDER BY yeara * 100 + perioda DESC

Jane TT (janett (AT) aol (DOT) com) writes:
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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx