November 22, 2010 at 11:35 am
I have several records of the same employee with multiple sign-ins (and sign-outs for each day. I have to assign a consecutive number or 'shift' to each sign-in (1 for the first time, 2 for the second, and so on, for any given day.
Can anyone help? I'm an intermediate SQL report writer in Reporting Services 2008.
Thanks!
jaffie
November 22, 2010 at 11:44 am
Are you inserting the sign-ins through a stored procedure? If so, can you copy that onto this forum? If so, we can probably help you more effectively.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 11:47 am
No stored procedure. Just simple records with each sign-in transaction counting as one record for either one day or multiple days.
Thank you.
November 22, 2010 at 11:48 am
Does the shift number need to be stored in the database, or can it just be a number in the report?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 11:50 am
End result should look something like this:
Emp 111/17/2010Wed17:57:57 AM1:00:26 PM5:02
Emp 111/17/2010Wed21:30:39 PM5:37:21 PM4:06
Emp 111/17/2010Wed36:03:52 PM8:03:54 PM2:00
The '1' '2' and '3' are the 'shifts' that I need to calculate into the query.
Thanks again.
November 22, 2010 at 12:03 pm
Right. Reporting services can do line-numbering for you, if I'm not mistaken, or the query can use the Row_Number() function, or you can modify the insert statements for how the data gets into the database in the first place.
The easiest will probably be to use the Row_Number() function in the query. Take a look at that, see if it'll accomplish what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 12:36 pm
The ROW_NUMBER() partially works and it numbers all records, regardless of date. I need to number for each instance of a date for each employee. I need to figure it into a grouping....???
November 22, 2010 at 12:45 pm
You can partition it based on employee ID and on the date, then order by the time.
Something like this:
-- Test Data: Ignore this and use your table
CREATE TABLE #T (
ID INT IDENTITY PRIMARY KEY,
EmployeeID INT,
CIN DATETIME);
INSERT INTO #T (EmployeeID, CIN)
SELECT 1, '1/1/2000 1 pm' UNION ALL
SELECT 1, '1/1/2000 2 pm' UNION ALL
SELECT 2, '1/1/2000 1 pm';
-- Sample Query: Modify for your table
SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID, DATEADD(DAY, DATEDIFF(DAY, 0, CIN), 0) ORDER BY CIN), *
FROM #T;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 1:19 pm
It worked!
Thank you so much!
Since I'm new to posting, do I get to rate your help somehow?
fergusoj (aka jaffie)
November 22, 2010 at 1:24 pm
You're welcome.
No ratings on this site.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply