SQL Server Query using results from table to Call function

user_10290849 0
Viewed 65

I have a problem with SQL Server. I have a table with machine statements, and another one with production data. The time between both tables are not the same to get the corresponding data. I don't know how to Join these tables. Also, I need to filter just the code 17 from Table A.

For example:

Table a:

Machine  Code  Time
----------------------------------
EPSX     17    2018-08-28 12:09:32
EPSA     19    2018-08-28 12:09:39
EPSC     17    2018-08-28 11:10:58

Table b:

Machine  Waste(meters)  Time
-------------------------------------------
EPSX     57m            2018-08-28 12:09:38
EPSA     128m           2018-08-28 12:09:43
EPSC     12m            2018-08-28 11:11:02

Desired output:

Machine  Code  Waste(meters)
----------------------------
EPSX     17    57m
EPSC     17    12m

I'm thinking to get the relation with the following statement:

a.Machine = b.machine (this is easy)
AND a.Time > b.Time 
AND a.Time <= DATEADD(MINUTE, 2, b.Time)

How can I solve this? Maybe using function? I can get the correct directly from the function from table b. But can I call the Table a and use a.Time in function call?

sql-server sql
3 Answers

If you know that the time stamps are always only out by a little bit then you could round the times to the nearest minute (google it) and use the rounded time as your FK between the tables.

user_16102900

The time in table a for EPSX is 2018-08-28 12:09:32, in table b 2018-08-28 12:09:38, i.e. a.time < b.time, but you are testing a.Time > b.Time AND a.Time <= DATEADD(MINUTE, 2, b.Time). This cannot work. Change it to

a.time BETWEEN DATEADD(MINUTE, -2, b.Time) AND b.time

or you can turn it around and test

b.time BETWEEN a.time AND DATEADD(MINUTE, 2, a.Time)

Together

SELECT a.Machine, a.Code, b.[Waste(meters)]
FROM
    a
    INNER JOIN b
        ON a.Machine = b.Machine AND
           b.time BETWEEN a.time AND DATEADD(MINUTE, 2, a.Time)
WHERE
    a.Code = 17
user_8809900 
select *
from tableA a cross apply (
    select top 1 waste from tableB b
    where b.Machine = a.Machine and b.Time >= a.Time
    order by b.Time
) c
where a.Machine = 17;

I generally try to avoid top but I'll go ahead and offer it as an easy solution.

user_13222680
Related