How to Missing Date range??
-
Hi Folks,
I have one query:
1. I have a date range in a table.
EmpCode, Start Date and End Date. (one row per employee)
Sample Date:
1 01-Jan-2006 31-Dec-2007
2 10-Oct-2006 31-Dec-2006
etc
2. Another set of date range is avaiable another table (From Date and To Date).
EmpCode, FromDate, ToDate (there are multiple rows per employee).
Sample Data:
1 01-Jan-2006 31-Jan-2006
1 15-Jun-2006 31-Oct-2007
1 10-Oct-2006 31-Dec-2007
2 01-Nov-2006 30-Nov-2006
2 01-Dec-2006 30-Jun-2007
Now, what I need is the missing ranges.
1 01-Feb-2006 14-Jun-2006
2 01-Nov-2006 31-Nov-2006
2. 10-Oct-2006 31-Oct-2006
----
Need some help from you guys in solving the above problem.
-
>>Now, what I need is the missing ranges.
How do you figure that November 1 through 30 of 2006 is a missing date when it clearly appears in both tables for the same empcode? I think we need a little more help on what you're looking for.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It seems overlapping is OK, he only wants the date ranges where the date is completely missing.
This was asked as SQLTeam.com recently, I just can't remember the topic ID.
N 56°04'39.16"
E 12°55'05.25" -
Not sure that's it, Peter... he lists
Now, what I need is the missing ranges.
1 01-Feb-2006 14-Jun-2006
2 01-Nov-2006 31-Nov-2006
2. 10-Oct-2006 31-Oct-2006
But, 01 - 30 Nov show up in both tables... no wonder he can't figure it out
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I guess typos are geting more advanced each day.
N 56°04'39.16"
E 12°55'05.25" -
Had some spare time, here's a solution to your problem.
I think results are correct, hope solution isn't too long winded.
results selected are:
empid Missing dates description
----------- ------------- -----------
1 01 Feb 2006 From
1 14 Jun 2006 To
2 01 Jan 2006 From
2 31 Oct 2006 To
2 01 Jul 2007 From
2 31 Dec 2007 To
SET NOCOUNT ON
CREATE TABLE #ED(empid int, start datetime, stop datetime)
--insert sample data
INSERT INTO #ED(empid, start, stop)
SELECT 1, '01-Jan-2006', '31-Jan-2006' UNION ALL
SELECT 1, '15-Jun-2006', '31-Oct-2007' UNION ALL
SELECT 1, '10-Oct-2006', '31-Dec-2007' UNION ALL
SELECT 2, '01-Nov-2006', '30-Nov-2006' UNION ALL
SELECT 2, '01-Dec-2006', '30-Jun-2007'
--now need to create days table, hold each empid along with each date for the range we're interested in
CREATE TABLE #Days(empid int, DayToCheck datetime)
DECLARE @MinDate datetime, @MaxDate datetime, @DaysToInsert int, @DayCounter int
--determine min and max date in our dates
SELECT @MinDate = MIN(start), @MaxDate = MAX(stop) FROM #ED
SET @DaysToInsert = DATEDIFF(day, @MinDate, @MaxDate) + 1
SET @DayCounter = 0
WHILE @DayCounter < @DaysToInsert
BEGIN
INSERT INTO #Days(empid, DayToCheck)
SELECT E.empid, D.DayToCheck FROM (SELECT DATEADD(day, @DayCounter, @MinDate) DayToCheck) D INNER JOIN (SELECT DISTINCT empid FROM #ED) E ON 1 = 1
SET @DayCounter = @DayCounter + 1
END
--create another table based on #Days (not really necessary....) which will hold what data we know is missing
CREATE TABLE #MissingDays(empid int, DayToCheck datetime, pos tinyint)
INSERT INTO #MissingDays(empid, DayToCheck)
SELECT d.empid, d.DayToCheck FROM #Days d LEFT JOIN #ED ed ON d.empid = ed.empid and d.DayToCheck >= ed.start and d.DayToCheck <= ed.stop WHERE ed.empid IS NULL
--keep track of where data is: 0: has both prev and next, 1: has next, 2: has prev
UPDATE md
SET md.pos = CASE WHEN mdb.empid IS NOT NULL and mda.empid IS NOT NULL THEN 0 WHEN mdb.empid IS NOT NULL THEN 2 ELSE 1 END
FROM
#MissingDays md LEFT JOIN
#MissingDays mda ON md.empid = mda.empid AND DATEADD(day, 1, md.DayToCheck) = mda.DayToCheck LEFT JOIN
#MissingDays mdb ON md.empid = mdb.empid AND DATEADD(day, -1, md.DayToCheck) = mdb.DayToCheck
--delete unwanted records (where day before and day after exists)
DELETE md
FROM
#MissingDays md INNER JOIN
#MissingDays mdb ON md.empid = mdb.empid and DATEADD(day, -1, md.DayToCheck) = mdb.DayToCheck INNER JOIN
#MissingDays mda ON md.empid = mda.empid and DATEADD(day, 1, md.DayToCheck) = mda.DayToCheck
--finally, select the missing days (inclusive)
SELECT empid, CONVERT(varchar(11), DayToCheck, 106) as [Missing dates], [description] = CASE pos WHEN 1 THEN 'From' WHEN 2 THEN 'To' ELSE 'On' END FROM #MissingDays ORDER BY empid, DayToCheck
-- done, get rid of tables
DROP TABLE #MissingDays
DROP TABLE #Days
DROP TABLE #ED
-
Thanks buddy, this is what I am looking for.
gr8.
-
Heh... 5 rows for 2 users generates 1,460 rows in the #Days table to start with. Wonder what's going to happen when you have 10,000 rows for 100 users?
The first thing is that the OP, who has not yet posted back with a correct requirement
, has two tables to compare to each other. And, his requirements don't explain why all of November is included in the result list when only 1 day is not covered in November for employee #2.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply