Look for gaps in date ranges - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Look for gaps in date ranges

Author

Topic

martalex
Starting Member

17 Posts

Posted - 2007-06-04 : 16:37:40

I have the following table.

CREATE TABLE #EMP_POS (
[emp_POS_id] [int] IDENTITY (1, 1) NOT NULL ,
[emp_id] [int] NOT NULL ,
[start_dt] [datetime] NOT NULL ,
[end_dt] [datetime] NOT NULL,

) ON [PRIMARY]
GO

INSERT INTO #EMP_POS VALUES(1, '2007-01-01', '2007-01-31')
INSERT INTO #EMP_POS VALUES(1, '2007-02-01', '2007-02-28')
INSERT INTO #EMP_POS VALUES(1, '2007-03-01', '2007-03-31')
INSERT INTO #EMP_POS VALUES(2, '2007-01-01', '2007-01-15')
INSERT INTO #EMP_POS VALUES(2, '2007-01-16', '2007-02-15')
INSERT INTO #EMP_POS VALUES(2, '2007-04-01', '2007-04-23')

How can I determine if any employees are missing a time period within a given date range?

In this case employee 2 has a gaps between 2007-02-15 and 2007-04-01.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-04 : 16:58:21

Something like this might help:


Declare @Emp table ([emp_POS_id] [int] IDENTITY (1, 1) NOT NULL ,
[emp_id] [int] NOT NULL ,
[start_dt] [datetime] NOT NULL ,
[end_dt] [datetime] NOT NULL)

INSERT INTO @Emp VALUES(1, '2007-01-01', '2007-01-31')
INSERT INTO @Emp VALUES(1, '2007-02-01', '2007-02-28')
INSERT INTO @Emp VALUES(1, '2007-03-01', '2007-03-31')
INSERT INTO @Emp VALUES(2, '2007-01-01', '2007-01-15')
INSERT INTO @Emp VALUES(2, '2007-01-16', '2007-02-15')
INSERT INTO @Emp VALUES(2, '2007-04-01', '2007-04-23')

select * , Diff = Datediff(dd, End_dt,(Select min(Start_Dt) from @Emp E2 Where Emp_id = E1.Emp_Id and E1.emp_POS_id < E2.emp_POS_id)) -1
from @emp E1

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources