0

Newbie to this page. I need help with data linking in Microsoft SQL. I have two data tables both rather large data sets which I need to link somehow. My 1st data set shows the start and end date/time of a group and has a unique ID. The 2nd set does have a unique id but its not linkable to the first set. The only fields that I could link are on date /Time & Group. I am trying to have the 2nd table show the ID from the first table so that I can then join the data sets. Its a 1 time update and will not need to be refreshed.

I need to base link on the below:

IF ds2.Time is between ds1.Start_Time and ds1.End Time then ds1.ID

ds=data source

Data Source 1

ID  Start Date  End Date    Start Time  End Time    Group
1   12/01/2010  12/01/2010  12:45        13:10        A
2   12/01/2010  12/01/2010  12:00        14:55        B
3   12/01/2010  12/01/2010  12:55        13:45        C
4   12/01/2010  12/01/2010  13:55        14:25        A
5   12/01/2010  12/01/2010  15:02        15:55        B

Data Source 2

Date          Time  Group   Total   ID
12/01/2010  12:45   A         13    ?
12/01/2010  12:46   A         15    ?
12/01/2010  12:47   A          8    ?
12/01/2010  12:48   B         10    ?
12/01/2010  13:55   B          8    ?
12/01/2010  13:57   B          9    ?
12/01/2010  13:58   B         10    ?
12/01/2010  13:59   B         12    ?
12/01/2010  14:01   B         14    ?

As I have posted I will post final answer if not able to find on page to help close out for others.

Mark
  • 171
  • 11
  • Whence is the `total` column in your table "Data Source 2"? There has to be some way to logically match the records between your two tables or else the problem cannot be solved. – Tim Biegeleisen Mar 19 '15 at 09:45
  • Thanks Tim. Total only exists in Data Source 2. Looking at ID 1 on Source 1 I need it to link with the first 3 lines from source 2 as source 2 data is between the date from source 1. – Mark Mar 19 '15 at 09:55
  • Continuing with your example, to what would you link ID 4 in Source 1, seeing how you already matched the first 3 lines from source 2? Please articulate your matching algorithm. – Tim Biegeleisen Mar 19 '15 at 10:26
  • Based on the sample data I have shown its only ID 1 and 2 which will appear on dS2. DS1 is really the hierarchy of DS2 which is the detail. – Mark Mar 19 '15 at 10:35

1 Answers1

0

You could JOIN Table1 and Table2 tables and use UPDATE on Table2 table. 

UPDATE Table2
SET Id = (
          SELECT Id
          FROM Table1 t1 
          JOIN Table2 t2
             ON (t1.Group = t2.Group) AND
                (t1.StartDate = t2.Date) AND
                (t1.StartTime = t2.Time)
          )

Or something like this:

UPDATE Table2 t2
JOIN Table1 t1
ON (t1.Group = t2.Group) AND
   (t1.StartDate = t2.Date) 
SET t2.Id = t1.Id
WHERE t2.time BETWEEN t1.StartTime AND t1.EndTime
Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
  • Thanks for your help. Data on source 1 has a start and end time so I have to include that otherwise I would have null values in my data. If you see the 2nd line on source 2 this would fail to link with code above. – Mark Mar 19 '15 at 09:57
  • UPDATE Table2 t2 JOIN Table1 t1 ON (t1.Group = t2.Group) AND (t1.StartDate = t2.Date) AND (t1.EndDate = t2.Date) AND (Between t1.startTime and t1.EndTime = t2.Time) SET t2.Id = t1.Id --- Adjusted your logic but this still does not work. Any help here is apprecaiated – Mark Mar 19 '15 at 10:16
  • @Mark check updated answer, try to specify IF clause with `BETWEEN` – Stanislovas Kalašnikovas Mar 19 '15 at 10:33
  • thanks sorry cant upvote as don't have high enough rep. need it too be 15. thanks again. – Mark Mar 19 '15 at 12:37