Join Question 1

[sbutcher]

I am trying to Join data from two different tables where there is not a direct link. I need the first entry from Table 2 that occurs after the entry in table 1 where an equipment ID matches. The data sources are from 2 systems that are not directly linked and Table 2 is only populated upon final consumption of a particualr item

For example. There is an entry in Table 1 at 5:00 pm for equipment A. Table 2 may then be populated at 5:02 for Equipment B, 5:10 for Equipment C and then maybe not until 5:15 for Equipment A. There will be additional entries after this for all pieces of equipment. I want to be able to join the entry in table 1 with the 5:15 entry in Table 2.

All attempts to date have resulted in many thousands of entries more than are in Table1.

Any guidance you could give would be greatly appreciated.

Thanks!

 

[gmmastros]

Can you show some sample data from both tables and also what the expected results should be? This will make it easier for use to help you.

-George

"the screen with the little boxes in the window." - Moron

 

[LNBruno]

Time constraint prevents me from doing more, but here's a start:

create table #table1
   (
   equipID     varchar(1)
   , equipTime datetime
   , t1Text  varchar(10)
   )

insert into #table1 (equipID, equipTime, t1Text) VALUES ('A', '5:00 PM', 'first 1')
insert into #table1 (equipID, equipTime, t1Text) VALUES ('B', '5:02 PM', 'second 1')
insert into #table1 (equipID, equipTime, t1Text) VALUES ('C', '5:10 PM', 'third 1')
insert into #table1 (equipID, equipTime, t1Text) VALUES ('D', '5:15 PM', 'fourth 1')

create table #table2
   (
   equipID     varchar(1)
   , equipTime datetime
   , t2Text  varchar(10)
   )

insert into #table2 (equipID, equipTime, t2Text) VALUES ('D', '5:00 PM', 'first 2')
insert into #table2 (equipID, equipTime, t2Text) VALUES ('B', '5:02 PM', 'second 2')
insert into #table2 (equipID, equipTime, t2Text) VALUES ('C', '5:10 PM', 'third 2')
insert into #table2 (equipID, equipTime, t2Text) VALUES ('A', '5:15 PM', 'fourth 2')

SELECT t1.equipID, t1.equipTime, t1Text
   , t2.equipID, t2.equipTime, t2Text
FROM #table1 t1
LEFT JOIN #table2 t2
   ON t2.equipID = t1.equipID
WHERE t2.equipTime > t1.equipTime
   -- AND... this is as far as I got...

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[sbutcher]

Here is some sample data:

Table 1 - Error Table

ID EventDateTime ReelStand Speed
1511 2007-08-15 02:22:56.000 1 60000
1512 2007-08-15 03:32:06.000 3 30000
1513 2007-08-15 12:17:32.000 4 45000
1513 2007-08-15 12:35:32.000 3 22000



Table 2 - Paper Consumption Table

ID TransactionDateTime ReelStand ReelArm PaperSize Barcode
2441 2007-08-15 02:23:06.000 2 0 56 1234A
2442 2007-08-15 02:35:07.000 4 1 55 5524F
2443 2007-08-15 02:38:42.000 1 1 32 44865
2444 2007-08-15 03:02:44.000 5 0 22 858E4
2445 2007-08-15 03:54:42.000 3 1 55 5478A
2446 2007-08-15 03:54:42.000 1 0 32 6924G
...........
2580 2007-08-15 12:18:48.000 3 0 55 514FA
2581 2007-08-15 12:33:42.000 4 0 55 G5579
2582 2007-08-15 12:36:42.000 2 1 56 H4544
2583 2007-08-15 12:55:52.000 3 0 55 J5468
2584 2007-08-15 13:01:42.000 4 1 56 55579


Results
EventDateTime TransactionDateTime ReelStand ReelArm PaperSize Barcode Speed
2007-08-15 02:22:56 2007-08-15 02:38:42 1 1 32 44865 60000
2007-08-15 03:32:06 2007-08-15 03:54:42 3 1 55 5478A 30000
2007-08-15 12:17:32 2007-08-15 12:33:42 4 0 55 G5579 45000
2007-08-15 12:35:32 2007-08-15 12:55:52 3 0 55 J5468 22000

Bruno, I will try your example.

Thanks.

Shane

 

[ESquared]

Correlated subquery:

SELECT
   E.EventDateTime,
   P.TransactionDateTime,
   P.ReelStand,
   P.ReelArm,
   P.PaperSize,
   P.Barcode,
   E.Speed
FROM
   Errors E
   INNER JOIN PaperConsumption P ON
      E.ReelStand = P.ReelStand
      AND P.TransactionDateTime >= E.TransactionDateTime
WHERE
   P.TransactionDateTime = (
      SELECT Min(TransactionDateTime)
      FROM PaperConsumption X
      WHERE
         E.ReelStand = X.ReelStand
         AND X.TransactionDateTime >= E.TransactionDateTime
   )

Derived tables (one method):

SELECT
   OrderID = identity(int, 1, 1),
   E.ReelStand,
   E.EventDateTime,
   EndTime = Convert(datetime, NULL)
INTO #Err
FROM Errors E
ORDER BY
   E.ReelStand,
   E.EventDateTime

UPDATE E1
SET E1.EndTime = IsNull(E2.EventDateTime, '9999-12-31')
FROM
  #Err E1
  LEFT JOIN E2 ON E1.ReelStand = E2.ReelStand AND E1.OrderID + 1 = E2.OrderID

SELECT
   E.EventDateTime,
   P.TransactionDateTime,
   P.ReelStand,
   P.ReelArm,
   P.PaperSize,
   P.Barcode,
   E.Speed
FROM
   Errors E
   INNER JOIN ( 
      SELECT
         ET.ReelStand,
         ET.EventDateTime,
         MinTran = Min(TransactionDateTime)
      FROM
         #Err ET
         INNER JOIN PaperConsumption P ON
            ET.ReelStand = P.ReelStand
            AND P.TransactionDateTime >= ET.EventDateTime
            AND P.TransactionDateTime < ET.EndTime
      GROUP BY
         E.ReelStand,
         E.EventDateTime
   ) X ON E.ReelStand = X.ReelStand AND E.EventDateTime = X.EventDateTime
   INNER JOIN PaperConsumption P ON
      E.ReelStand = P.ReelStand
      AND X.MinTran = P.TransactionDateTime

My wild guess is that the former will perform better for small queries where the conditions are mostly on the Errors table, and that the latter, despite seeming wildly complicated, will perform better for the entire thing at once (assuming the tables have an appreciable number of rows, say thousands or tens of thousands).

These queries might need tweaking... no testing done, all here typed from my brain.

The second method using the derived tables could possibly be improved with experimentation. Instead of using the temp table/identity column/ordered insert trick to match up each EventDate with the next one for the same ReelStand, one could use a correlated subquery or even a derived table grouped join on time >= time. That might be more efficient than actually pushing all the data to a temp table. Another variation might involve putting the EndTime column into your Errors table directly. Even more variations with Row_IDs and Common Table Expressions in SQL 2005 are possible.



[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[sbutcher]

The correlated subquery worked! Thank you ESquared and others for your responses and help!! I really appreciate it.

Shane

 

[LNBruno]

How about clicking on the link that says:

Thank ESquared
for this valuable post!

then?

[small]It's all some of us have to live for...[/small]

;-)

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[ESquared]

If you are concerned about performance at all you might consider the derived table method. And a correction:

LEFT JOIN E2 -> LEFT JOIN #Err E2

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]