inner join with 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 2008 Forums

Transact-SQL (2008)

inner join with date ranges

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-02-16 : 07:05:35

I got one table with the events start and stop date
With these fields:

id dd_start dd_end userid

I got another table with the start and stop date when a user is absent.
id startdate stopdate userid

Now what i am trying to do is select all the events from the eventtable and extract the dateranges from the absent table...

how do i do this ? :o

tx alot in advance you sql guru's

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-16 : 07:44:01

Could you please give example data and wanted result?

No, you're never too old to Yak'n'Roll if you're too young to die.

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-16 : 08:47:19

quote:
Originally posted by doubleotwo

Now what i am trying to do is select all the events from the eventtable and extract the dateranges from the absent table...

I was not sure about your question. To me, you appear to ask: Get absent dates for employees and show them next to all events.

I thought you might have meant: Get dates where employees were absent during events.

I gave both solutions. My apologies for any misunderstanding on my part.


CREATE TABLE #Events
(ID           INTEGER
,dd_start     DATE
,dd_end       DATE
,userid       INTEGER
,CONSTRAINT pk_Events PRIMARY KEY (ID)
)
go

INSERT INTO #Events
(ID
,dd_start
,dd_end
,userid
)
SELECT 1, '2011-01-01', '2011-01-05', 1 UNION
SELECT 2, '2011-01-10', '2011-01-15', 1 UNION
SELECT 3, '2011-01-01', '2011-01-05', 2 UNION
SELECT 4, '2011-01-10', '2011-01-15', 2
go

CREATE TABLE #Absences
(ID           INTEGER
,dd_start     DATE
,dd_end       DATE
,userid       INTEGER
,CONSTRAINT pk_Absences PRIMARY KEY (ID)
)
go

INSERT INTO #Absences
(ID
,dd_start
,dd_end
,userid
)
SELECT 1, '2011-01-02', '2011-01-03', 1 UNION
SELECT 2, '2011-01-14', '2011-01-15', 2
go

-- Get absent dates for employees and show them next to all events.

  SELECT E1.ID
        ,A1.dd_start
        ,A1.dd_end
    FROM #Events AS E1
         CROSS JOIN
         #Absences AS A1

-- Get dates where employees were absent during events.

  SELECT E1.ID
        ,E1.userid
        ,A1.dd_start
        ,A1.dd_end
    FROM #Events AS E1
         INNER JOIN
         #Absences AS A1
         ON A1.userid = E1.userid
        AND A1.dd_start < E1.dd_end
        AND A1.dd_end > E1.dd_start
go

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-02-16 : 09:24:01

thx alot but thats not what i was looking for...

i want to get all events except for the events that lie in the rangers of a user thats absent

for example i got 3 events

id dd_start dd_end userid
1 '2011-01-01 10:00' '2011-01-01 12:00' 44
2 '2011-01-02 10:00' '2011-01-02 12:00' 44
3 '2011-01-03 10:00' '2011-01-03 12:00' 44

and in my absent table i got
id start end userid
1 '2011-01-02 00:00' '2011-01-02 23:59' 44

then i want to get only event with id 1 and 3....

note: there can be multiple records of beeing absent of a userid ofc

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2011-02-16 : 17:46:43

IF OBJECT_ID('tempdb..#Events') IS NOT NULL DROP TABLE #Events;
IF OBJECT_ID('tempdb..#Absences') IS NOT NULL DROP TABLE #Absences;

CREATE TABLE #Events
(ID INTEGER
,dd_start DATE
,dd_end DATE
,userid INTEGER
,CONSTRAINT pk_Events PRIMARY KEY (ID)
)
go

INSERT INTO #Events
(ID
,dd_start
,dd_end
,userid
)
SELECT 1, '2011-01-01', '2011-01-05', 1 UNION
SELECT 2, '2011-01-10', '2011-01-15', 1 UNION
SELECT 3, '2011-01-01', '2011-01-05', 2 UNION
SELECT 4, '2011-01-10', '2011-01-15', 2
go

CREATE TABLE #Absences
(ID INTEGER
,dd_start DATE
,dd_end DATE
,userid INTEGER
,CONSTRAINT pk_Absences PRIMARY KEY (ID)
)
go

INSERT INTO #Absences
(ID
,dd_start
,dd_end
,userid
)
SELECT 1, '2011-01-02', '2011-01-03', 1 UNION
SELECT 2, '2011-01-14', '2011-01-16', 2
go

-- Get events where employees were NOT absent during events.

SELECT E1.ID
,E1.userid
,E1.dd_start
,E1.dd_end
FROM #Events AS E1
LEFT JOIN
#Absences AS A1
ON A1.userid = E1.userid
AND A1.dd_start < E1.dd_end
AND A1.dd_end > E1.dd_start
WHERE A1.ID IS NULL;
go
IF OBJECT_ID('tempdb..#Events') IS NOT NULL DROP TABLE #Events;
IF OBJECT_ID('tempdb..#Absences') IS NOT NULL DROP TABLE #Absences;

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-02-17 : 03:55:08

thx alot all of you, u helped my again :)

i luv this forum :p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources