Tsql datetime set except operation

Question

I have a "detected presence" table with datetime couples and a "expected presence" tables with datetime couples... I would like to know when i expected the presence but there was not... I think this is set difference with datetimes (in tsql Except operand), but i don't know how to solve with sql.

An example if I was not clear:

    CREATE TABLE [dbo].[DetectedPresence](
        [entrance] [datetime] NULL,
        [exit] [datetime] NULL
    )

    CREATE TABLE [dbo].[ExpectedPresence](
        [entrance] [datetime] NULL,
        [exit] [datetime] NULL
    )

INSERT INTO ExpectedPresence VALUES ('2012-11-16 08.15','2012-11-16 12.00')
INSERT INTO ExpectedPresence VALUES ('2012-11-16 17.00','2012-11-16 18.00')
INSERT INTO DetectedPresence VALUES ('2012-11-16 08.00','2012-11-16 12.00')
INSERT INTO DetectedPresence VALUES ('2012-11-16 15.00','2012-11-16 18.00')

ExpectedPresence values:

entrance               exit
2012-11-16 08.00       2012-11-16 12.00
2012-11-16 15.00       2012-11-16 18.00

DetectedPresence values:

entrance               exit
2012-11-16 08.15       2012-11-16 12.00
2012-11-16 15.00       2012-11-16 17.00

I would like to have datetime set difference (gaps):

2012-11-16 08.00       2012-11-16 08.15
2012-11-16 17.00       2012-11-16 18.00

Can you help me? thank you.

The values you've shown are not `datetime` values, so it's not clear what you're really asking. I suggest that you post a small, self-contained example including the `CREATE TABLE` and `INSERT` statements necessary to set up some test data, and the result you expect to get. — Pondlife, Nov 15 '12 at 17:56

Beth · Answer 1 · 2012-11-16T14:34:11.677

something along the lines of

case when detectedStart > expectedStart then 
   expectedStart + '-' + detectedStart 
else null end as startDiff,
case when detectedEnd < expectedEnd then 
   detectedEnd + '-' + expectedEnd 
else null end as endDiff

or

    insert into difference (expectedStart, detectedStart)
    Select
        expectedStart,detectedStart 
    from 
        ExpectedPresence e inner join
        DetectedPresence d on
        e.staffID = d.staffID
    where
       detectedStart > expectedStart;

    insert into difference (expectedEnd, detectedEnd)
    Select  
        expectedEnd,detectedEnd 
    from 
        ExpectedPresence e inner join
        DetectedPresence d on
        e.staffID = d.staffID
    where
        detectedEnd < expectedEnd

I'm not sure this is the solution... I need a datetime gap table as result. — Tobia, Nov 16 '12 at 07:19

Tsql datetime set except operation

1 Answers1