SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Transform Dates Table Into Range Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paulkem
Starting Member

25 Posts

Posted - 03/28/2014 :  09:27:36  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
410 Posts

Posted - 03/28/2014 :  16:11:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/28/2014 :  16:44:34  Show Profile  Reply with Quote
Sample data & expected output?
Go to Top of Page

paulkem
Starting Member

25 Posts

Posted - 03/31/2014 :  07:48:38  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000