May 1, 2007 at 4:02 am
Hi Folks,
I am hoping someone can shed some light on a query I have where I am trying to loop through a set of records and select the top the 2 records for each employee for each day of the week.
For example, I am running the following query:
Select * from EmployeeHours order by employeeID asc, Mon desc
This returns something like so:
EmpID, Mon, Task ID
14 5 002
14 2.5 003
14 .5 004
14 0 005
15 3 001
15 3 002
15 2 003
16 7 001
16 .5 002
16 0 003
This query returns all the hours for each employee, but the plan is to select the top 2 records each day for each employee and extend this over a week so the eventual record set produced will look something like this
EmpIDTaskIDMonTues Wed
142546
143231
151467
1523.520.75
1633.556.75
1643.52.51.75
So far I've tried looping through the records for each employee and running the query outlined above for each day, but that ended in a SQL disaster and me tied up in knots trying to figure out what was going on.
Sorta hoping someone can help me untangle this mess please?
Cheers!
Morris
May 1, 2007 at 4:42 am
PIVOTing is best done at the presentation level, however,
can you post the DDL for the EmployeeHours table
What does "Mon" signify?
Is it Monday's value?
If so where is Tue,Wed etc?
What is the definition of TOP 2, value/taskid asc/desc?
Can you post what you have so far
and post results based on the supplied input, it is difficult to validate the result when different data is used
Far away is close at hand in the images of elsewhere.
Anon.
May 2, 2007 at 3:20 am
SQL 2005 row_number might help:
--top 2 Biggest orders by salesperson
SELECT rank, SalesPersonID, SalesOrderID, TotalDue
FROM (SELECT CAST(ROW_NUMBER()
OVER(PARTITION BY SalesPersonID
ORDER BY TotalDue DESC) AS INT) AS rank
,SalesOrderID, SalesPersonID, TotalDue
FROM AdventureWorks.Sales.SalesOrderHeader
) AS iq
WHERE rank <= 2
rank SalesPersonID SalesOrderID TotalDue
----------- ------------- ------------ ---------------------
1 NULL 43697 3953.9884
2 NULL 43702 3953.9884
1 268 51830 149861.0659
2 268 57136 114361.9377
1 275 47395 198628.3054
2 275 53621 142942.0068
1 276 51822 174496.8155
2 276 47355 173438.7092
1 277 46660 162629.7468
2 277 43884 154912.0712
1 278 44534 115285.4404
2 278 43890 99923.8654
1 279 44518 169579.4422
2 279 43875 163975.1393
1 280 47033 125254.4859
2 280 67297 125144.0084
1 281 51131 247913.9138
2 281 55282 227737.7215
1 282 53573 157212.8539
2 282 51823 153845.2355
1 283 46643 149897.3647
2 283 51711 136046.4425
1 284 51837 98405.0752
2 284 59064 96243.7996
1 285 46616 207058.3754
2 285 46607 162324.4146
1 286 46981 201490.4144
2 286 51858 186878.2646
1 287 69508 140734.4875
2 287 50297 137108.3865
1 288 53485 85652.3297
2 288 53502 45338.7554
1 289 51761 146293.234
2 289 55254 140521.5049
1 290 51814 90908.6384
2 290 53566 84825.8951
(36 row(s) affected)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply