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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Transform Dates Table Into Range Table

Author  Topic 

paulkem
Starting Member

28 Posts
Posted - 2014-03-28 : 09:27:36
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

ScottPletcher
Aged Yak Warrior

550 Posts
Posted - 2014-03-28 : 16:11:42
I'm confused. If this is the department for which the employee works, what does clocking in have to do with that? A separate table should contain the employee's work department, and only change when necessary.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2014-03-28 : 16:44:34
Sample data & expected output?
Go to Top of Page

paulkem
Starting Member

28 Posts
Posted - 2014-03-31 : 07:48:38
I did not design the source table. This is what i have been provided with as a source.

Here is some sample data.

I don't think there is any way to do this without capturing all of those little "breaks" such as weekends, holidays, etc as there is no way for me to tell if the the break is due to time off or due to not being in that department at that time.

PK


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-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]
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site