CODEWITHSUNDEEP
sqlsql-serversql-server-2008timebetween
Matt
Matt

Reputation: 15061

SQL time in & time out clashes between any ids in table

I am trying to create two fields in my table that shows whether a vid's time clashes with ANY another vid's time, and which vid's clash.

SAMPLE DATA:

SELECT vid, timein, timeout
FROM mytable

vid timein                      timeout
1   2015-04-15 06:00:00.000     2015-04-16 17:00:00.000
2   2015-04-17 03:00:00.000     2015-04-17 18:00:00.000
3   2015-04-16 16:00:00.000     2015-04-17 06:00:00.000
4   2015-04-12 12:00:00.000     2015-04-12 22:00:00.000
5   2015-03-25 01:00:00.000     null

DESIRED OUTPUT:

vid timein                      timeout                     Clash   Clashwith
1   2015-04-15 06:00:00.000     2015-04-16 17:00:00.000     CLASH   3
2   2015-04-17 03:00:00.000     2015-04-17 18:00:00.000     CLASH   3
3   2015-04-16 16:00:00.000     2015-04-17 06:00:00.000     CLASH   1, 2
4   2015-04-12 12:00:00.000     2015-04-12 22:00:00.000     OK
5   2015-03-25 01:00:00.000     null                        OK

WHAT I HAVE TRIED:

SELECT vid, timein, timeout,
CASE WHEN (SELECT tin.timein 
           FROM mytable tin 
           WHERE tin.vid = mytable.vid 
           AND mytable.timeout IS NOT NULL) 
           BETWEEN mytable.timein AND mytable.timeout
     THEN 'CLASH' 
     ELSE 'OK' 
     END AS inclash,
CASE WHEN (SELECT tout.timeout 
           FROM mytable tout 
           WHERE tout.vid = mytable.vid 
           AND mytable.timeout IS NOT NULL) 
           BETWEEN mytable.timein AND mytable.timeout
     THEN 'CLASH' 
     ELSE 'OK' 
     END AS outclash
FROM mytable

This doesn't work as it is giving a clash for all results, also i have no idea how to get the clash with column written.

Upvotes: 2

Views: 92

Answers (1)

roman
roman

Reputation: 117520

You can do it with one simple apply(), and to concatenate all clashing ids you can use xml trick:

select
    t1.vid,
    t1.[timein],
    t1.[timeout],
    case when c.Clashwith is not null then 'CLASH' else 'OK' end as Clash,
    c.Clashwith
from mytable as t1
    outer apply (
        select
            stuff(
                (
                    select ',' + cast(t2.vid as nvarchar(max))
                    from mytable as t2
                    where
                        t2.vid <> t1.vid and
                        t2.[timein] <= t1.[timeout] and
                        t2.[timeout] >= t1.[timein]
                     for xml path(''), type
                ).value('.', 'nvarchar(max)')
            ,1,1,'') as Clashwith
    ) as c

sql fiddle demo

Upvotes: 3

Related Questions