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 |
|
bushfoot
Yak Posting Veteran
53 Posts |
 Posted - 2010-07-15 : 09:37:34
|
| I have employee scheduling data. I want to assign a shift number starting at 1 for each shift an emp has on the same day.For example, Ian June 14 8-5Mike June 14 8-11 Mike June 14 4-8I want to assign a shift number so that mike has shift num 1 for 8-11 and shift num2 for 4-8. Ian would have a shift num of 1 as he only has one shift on June 14.I found a way to do a running value with an update statement http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspxUPDATE @SalesTbl SET @RunningTotal = RunningTotal = @RunningTotal + SalesFROM @SalesTbl The only thing this doesnt do is restart at 1 for each emp. When I use this I get 1, 2,3 .Any suggestions? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-07-15 : 10:00:56
|
SELECT *, ROW_NUMBER() OVER (PARTITION BY Employer, Date, Shiftnum ORDER BY SomeCol) AS recIDFROM Table1
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
bushfoot
Yak Posting Veteran
53 Posts |
Posted - 2010-07-15 : 10:32:57
|
| Thanks. Works like a charm! |
|
|
|
|
|
|
Running value - reset on each group