CODEWITHSUNDEEP
sqlsql-serverdateoverlapping
user1446930
user1446930

Reputation: 47

SQL Server overlapping dates

I need help updating a calendar table. I have 2 tables a transaction table (table T) and a calendar table (Table C). i am trying to update then calendar table with application id (app_id) and emp_Id once the dates are available, if the dates overlap it should not be updated. e.g

Table T 

app_id  emp_id stdate      eddate      priority 
-----------------------------------------------     
1       15     2015-01-03  2015-01-05   1
2       18     2015-01-04  2015-01-06   2

Table C

dates        app_id    empid
----------------------------
2015-01-03     null    null
2015-01-04     null    null
2015-01-05     null    null

After the update is run the outcome should be

Table C

dates        app_id    empid
----------------------------
2015-01-03     1        15
2015-01-04     1        15
2015-01-05     1        15

Upvotes: 2

Views: 70

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

I would be inclined to do this with apply:

update c
    set app_id = t.app_id,
        empid = t.empid
    from table_c c apply
         (select top 1 t.*
          from table_t t
          where tc.dates >= t.stdate and
                tc.dates <= t.eddate
          order by t.priority
         ) t;

Upvotes: 1

Andrew
Andrew

Reputation: 7880

I wrote this off the top of my head, I hope it works for you:

UPDATE TC
SET app_id = (SELECT TOP 1 app_id FROM TABLE_T TT WHERE TC.dates BETWEEN TT.stdate AND tt.eddate ORDER BY TT.priority ASC),
empid = (SELECT TOP 1 emp_id FROM TABLE_T TT WHERE TC.dates BETWEEN TT.stdate AND tt.eddate ORDER BY TT.priority ASC)
FROM TABLE_C TC

You can debug this first with this query:

SELECT TC.*,
new_app_id = (SELECT TOP 1 app_id FROM TABLE_T TT WHERE TC.dates BETWEEN TT.stdate AND tt.eddate ORDER BY TT.priority ASC),
new_empid = (SELECT TOP 1 emp_id FROM TABLE_T TT WHERE TC.dates BETWEEN TT.stdate AND tt.eddate ORDER BY TT.priority ASC)
FROM TABLE_C TC

I used emp_id and empid as in your example.

Upvotes: 0

Related Questions