🚀 go-pugleaf

What is the platform you are trying to perform this task?

Suppose it is VB environment and use ado as data connection

1. Create connection cn1 for database1 and cn2 for database 2
2. Create recordset rs1 for retrieve times from cn1 and rs2 to retrieve data
from cn2
(you must have DSNs for ODBC1 and ODBC2 to open cn1 and cn2)
3. rs1.open "SELECT LotID, Lot.Transaction, Route, Resource.Time
FROM Lot, Resource
WHERE LotId like '0105M3'
AND (Route=1 or route = 2)
AND Lot.time=Resource.time", cn1
4.
if rs1!route = 1 then time1 = rs1!time; rs1.movenext; time2 = rs1!time
else time2 = rs1!time; rs1.movenext; time1 = rs1!time

5. rs2.open "SELECT DateTime, B1G, B1S, B2W, B3G
FROM Runtime.dbo.AnalogWideHistory
WHERE DateTime >= '" &time1 & "'"
AND DateTime <='"& time2 &"'"
AND wwresolution=100", cn2

"Runtime.dbo.AnalogWideHistory" Is this your table name? If not put the real
name there.


Now you can do whatever you want with rs2.

Good luck!

Peder <psh@nst.com> wrote in message
news:lZY47.157$XK6.25220@news.get2net.dk...
> Dear group,
>
> I hope someone can help me out...
>
> I call a SQL-database to get starttime and endtime for a process on a
given
> product (=LotID)
>
> To find the starttime:
>
>   SELECT LotID, Lot.Transaction, Route, Resource.Time
>    FROM Lot, Resource
>   WHERE LotId like '0105M3'
>    AND Route=1
>    AND Lot.time=Resource.time
>
> The result could be:
>
>   LotID    Transaction      Route          Time
>   0105M3    START            1             22-06-01 13:14:34
>
> To find the endtime:
>
>   SELECT LotID, Lot.Transaction, Route, Resource.Time
>    FROM Lot, Resource
>   WHERE LotId like '0105M3'
>    AND Lot.Transaction='COMPLETE'
>    AND Lot.time=Resource.time
>
> The result of this could be:
>
>   LotID   Transaction     Route           Time
>   0105M3    COMPLETE       2               22-06-01 14:10:53
>
> Then I use these times (Time) to call another database on the same
> server. For this I need to use ODBC:
>
>   SELECT * FROM OpenQuery(INSQL,"
>   SELECT DateTime, B1G, B1S, B2W, B3G
>    FROM Runtime.dbo.AnalogWideHistory
>   WHERE DateTime >= '22-06-01 13:14:34'
>    AND DateTime <= '22-06-01 14:10:53'
>    AND wwresolution=100
>   ")
>
> The result are the columns of data that I need:
>
>   DateTime   B1G   B1S   B2W    B3G
>
>
> I need to do this maneuvre in one go, and not via many calls. Is there a
> way to
> do this?
>
> mvh Peder / www.nst.com