SQL Query to join two tables by aggregating a column over a time range-CodePudding

I'm trying to run a query between that joins two tables by aggregating a calculated column from "Table B" over a time range, per record from "Table A"

Table A:

 Asset       Part              LastChanged        
 A11         RearBearing       2021/04/11
 A30         FrontBearing      2021/01/07
 A54         Shaft             2020/11/19
 A54         FrontBearing      2021/04/18

Table B

 Asset      EventStart             EventEnd              Revs     Load     Temp
 A54       2021-09-20 19:20:04     2021-09-20 20:31:33   2993     54       189
 A54       2021-09-20 20:40:14     2021-09-20 20:58:23   780      49       155
 A54       2021-09-20 21:05:53     2021-09-20 22:44:21   4728     66       142
 ...       ...................     ...................   ....     ..       ...

"Table B" is a collection of load events that all assets have undergone.

I'm trying to see if it's possible to run a SQL query per every record from Table A that will aggregate a calculated column from Table B using EventStart as the column to filter by.

For example, for the Front Bearing of asset A54, since the date it was last changed (2021/04/18) until today, I'd like to calculate two things from Table B.

-The sum of revolutions
-The sum of (revolutions*load)/temp

Then I'd like to repeat this for every Asset/Part record from Table A from their respective Last Changed dates.

If this is very hard to do in SQL I have the option of doing it in code and re-writing another table every 10 minutes or so, but ideally if this could be done directly in SQL I can have an almost real time API set up.

Thank you very much in advance to anyone who might be able to help.

CodePudding user response：

This sounds like a job for OUTER APPLY:

SELECT
    A.Asset,
    A.Part,
    A.LastChanged,
    B.Revs,
    B.RLT
FROM
    [Table A] As A
    OUTER APPLY
    (
        SELECT SUM(Revs) As Revs, SUM(1. * Revs * Load / Temp) As RLT
        FROM [Table B] As B
        WHERE B.Asset = A.Asset
        And B.EventStart >= A.LastChanged
    ) As B
;

(The 1. * is only there to avoid integer division; if your column types are actually floating-point numbers, you can omit this.)

Demo
Using Apply

CodePudding user response：

I suspect that you want the ratio of the two values. OUTER APPLY is a good approach. The code would look like:

SELECT A.*, B.Revs, B.Revs_Load,
       B.Revs * 1.0 / B.Revs_Load as ratio
FROM TableA A OUTER APPLY
     (SELECT SUM(Revs) As Revs, SUM(Revs * Load) As Revs_Load
      FROM TableB B
      WHERE B.Asset = A.Asset AND
            B.EventStart >= A.LastChanged
     ) B;