March 28, 2014 at 7:29 am
I have a table of employee numbers and dates. Each record represents a date that the employee was in a certain department and is based on when they clock in (the clock system stores the department). So basically it tracks every time the employee clocks in as a member of that department. I have a report that uses this table that simply queries the date range to see if the employee was a member of that department across those dates.
In theory, once an employee is a member of this department, they either stay there or are removed permanently. I cannot think of any cases where they are removed then put back at a later date. But I would not say that this is impossible.
What I would like to do, to conserve space, is change this table into a "range" (for lack of a better term) table with EmployeeNumber, StartDate, and EndDate. Ideally, I would want each range, i.e. if someone was in the department from 6/1/2013 to 11/30/2013 then removed, then put back at 3/1/2014. I would want 2 records.
123456 6/1/2013 11/30/2013
123456 3/1/2014 NULL
But given the nature of the data, I don't know if this is possible. There will only be a record for each time they clocked in so would each "gap" i.e. weekend, holidays, vac, etc show up as new records?
The other alternative I could think of is a table that just tracks the Start and End, (so basically the MIN and MAX clock in dates). This would not catch any of the gaps though.
Anyone have any ideas?
PK
March 28, 2014 at 7:43 am
Not enough information to offer much help here. Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 8:22 am
Here is some sample data:
IF OBJECT_ID('TempDB..#Agents','U') IS NOT NULL
DROP TABLE #Agents
CREATE TABLE #Agents (
EmployeeNumber [varchar](8) NOT NULL,
Location [varchar](10) NULL,
ClockDate [datetime] NOT NULL,
CONSTRAINT [PK_Agents] PRIMARY KEY CLUSTERED
(
[EmployeeNumber] ASC,
[ClockDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO #Agents
(EmployeeNumber
,Location
,ClockDate)
SELECT N'111111' AS [EmployeeNumber], N'NE' AS [Location], N'2014-02-25 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'111111' AS [EmployeeNumber], N'NE' AS [Location], N'2014-02-26 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'111111' AS [EmployeeNumber], N'NE' AS [Location], N'2014-02-27 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'111111' AS [EmployeeNumber], N'NE' AS [Location], N'2014-02-28 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-01 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-02 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-03 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-06 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-07 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-08 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-09 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-10 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-13 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-14 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-15 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-16 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-17 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-20 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-21 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-22 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-23 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-24 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-27 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-28 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-29 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-30 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-31 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-03 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-04 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-05 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-06 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-07 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-10 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-11 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-12 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-13 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-14 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-17 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-18 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-19 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-20 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-21 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-24 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-25 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-26 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-27 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'222222' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-28 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-01 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-02 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-03 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-06 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-07 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-08 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-09 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-10 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-13 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-14 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-15 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-16 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-17 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-18 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-20 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-21 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-22 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-23 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-24 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-27 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-28 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-29 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-30 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-01-31 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-03 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-04 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-05 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-06 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-07 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-08 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-10 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-11 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-12 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-13 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-14 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-17 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-18 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-19 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-20 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-21 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-24 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-25 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-26 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-27 00:00:00.000' AS [ClockDate] UNION ALL
SELECT N'333333' AS [EmployeeNumber], N'JC' AS [Location], N'2014-02-28 00:00:00.000' AS [ClockDate]
March 28, 2014 at 10:21 am
Merge would probably work, but you'll need to run this for each historical day to get caught-up then each day after that. Here's an example given your dates are from 2013-01-01 to 2013-02-28:
CREATE TABLE #Agents2 (EmployeeNumber [varchar](8) NOT NULL,
Location [varchar](10) NULL,
ClockDateFrom [datetime] NOT NULL,
ClockDateTo [datetime]);
DECLARE @loopDate DATETIME = '2014-01-01';
WHILE @loopDate <= '2014-02-28'
BEGIN
MERGE #Agents2 AS target
USING (SELECT * FROM #Agents WHERE ClockDate = @loopDate) AS source
ON (target.EmployeeNumber = source.EmployeeNumber AND target.Location = source.Location)
WHEN MATCHED THEN
UPDATE SET ClockDateTo = source.ClockDate
WHEN NOT MATCHED THEN
INSERT (EmployeeNumber, Location, ClockDateFrom,ClockDateTo)
VALUES (source.EmployeeNumber, source.Location, ClockDate, '2999-01-01');
SET @loopDate = DATEADD(D,1,@loopDate);
END
Running this gives you these results:
EmployeeNumberLocationClockDateFromClockDateTo
222222JC2014-01-01 00:00:00.0002014-02-28 00:00:00.000
333333JC2014-01-01 00:00:00.0002014-02-28 00:00:00.000
111111NE2014-02-25 00:00:00.0002014-02-28 00:00:00.000
And if a person goes to another Location this should comp for that. You'd need to do some experimentation on different scenarios though.
Hope this helps ..
Sam
March 28, 2014 at 10:50 am
This looks to be a pretty traditional islands and gaps problem. Take a look at this article. http://www.sqlservercentral.com/articles/T-SQL/71550/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 10:52 am
samalex (3/28/2014)
Merge would probably work, but you'll need to run this for each historical day to get caught-up then each day after that. Here's an example given your dates are from 2013-01-01 to 2013-02-28:
CREATE TABLE #Agents2 (EmployeeNumber [varchar](8) NOT NULL,
Location [varchar](10) NULL,
ClockDateFrom [datetime] NOT NULL,
ClockDateTo [datetime]);
DECLARE @loopDate DATETIME = '2014-01-01';
WHILE @loopDate <= '2014-02-28'
BEGIN
MERGE #Agents2 AS target
USING (SELECT * FROM #Agents WHERE ClockDate = @loopDate) AS source
ON (target.EmployeeNumber = source.EmployeeNumber AND target.Location = source.Location)
WHEN MATCHED THEN
UPDATE SET ClockDateTo = source.ClockDate
WHEN NOT MATCHED THEN
INSERT (EmployeeNumber, Location, ClockDateFrom,ClockDateTo)
VALUES (source.EmployeeNumber, source.Location, ClockDate, '2999-01-01');
SET @loopDate = DATEADD(D,1,@loopDate);
END
Running this gives you these results:
EmployeeNumberLocationClockDateFromClockDateTo
222222JC2014-01-01 00:00:00.0002014-02-28 00:00:00.000
333333JC2014-01-01 00:00:00.0002014-02-28 00:00:00.000
111111NE2014-02-25 00:00:00.0002014-02-28 00:00:00.000
And if a person goes to another Location this should comp for that. You'd need to do some experimentation on different scenarios though.
Hope this helps ..
Sam
Easy there...this is a performance timebomb. There is no need to resort to a while loop for this. I am pretty swamped at the moment but if nobody else comes along to post a set based solution to this I will take a look at it tonight after work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply