Get last 12 records per group - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Get last 12 records per group

Author

Topic

spikev2
Starting Member

6 Posts

Posted - 2009-06-29 : 11:41:18

Im quite new to sql server and having problems getting my head round this.

I have a table with the following compound key

EmployeeNo nchar(8)
weekno(int)

I want to extract by using a parameter against weekno, the last 12 records per employee to be used further in a calculation.

So, if I used week 13 then i would expect to see most records numbered 2-13 unless an employee had been off sick in week 13 then the record would not exist for that week and we would have 1-12 instead.

I have been trying to use select count but without much success.

Any assistance would be greatly appreciated

Any

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 12:36:16

[code]SELECT TOP 12 * FROM YourTable WHERE EmployeeNo = @EmpNo AND weekno<=@weekno
ORDER BY WeekNo DESC[/code]

spikev2
Starting Member

6 Posts

Posted - 2009-06-30 : 04:00:36

Many thanks for prompt reply.

What I was trying to obtain was the last 12 records for all employees.

The query that you have given me will generate 12 employee records only.

What I am trying to obtain is all employee records with the last 12 weekno's

so records in the table may look like the following if weekno was passed as a parameter:

empno weekno pay
12 2 100.00
12 3 100.00
15 2 200.00
15 3 300.00
15 4 200.00
etc

I have been looking at trying a self-join but without success.
Apologies if my original posting was unclear.

Hans1963
Starting Member

2 Posts

Posted - 2009-06-30 : 04:28:35

Do you have to anticipate on years as well? I mean. If you want the previous 12 weeks in week 6 (early february), do you want the records for weeks 1-6 or for weeks 46-6?

spikev2
Starting Member

6 Posts

Posted - 2009-06-30 : 08:31:11

That is correct. I have a payrun date added but due to testing some of the values are null. The table will accumulate information over a period of time.

I would envisage the query using payrun date instead of weekno at a later stage.

The logic is that a value is calculated for each employee based on their previous 12 wages records.

Many thanks for your assistance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources