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.
| 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
|
|
|
|
|
|
|
Get last 12 records per group