pulling hours between 2 dates and totalling

[KLewisMFL]

Hi All,

I have a table that can have up to 3 records per day with runtime hours, shift and Line name.

01/07/19 - Line 1 - AM - 7.5
01/07/19 - Line 1 - PM - 7.5
01/07/19 - Line 1 - NIGHTS - 7.5

I have a table that has Assets linked to Line name with a start date and an end date.

What I would like to achieve is that for each record in the Asset table, it would be able to take the runtime hours and total them for between the start and end date.

Line 1 - Asset 1 - 01/07/19 - 31/07/19 - 450
Line 1 - Asset 2 - 30/06/19 - 14/07/19 - 280
Line 1 - Asset 3 - 01/07/19 - 31/07/19 - 450
Line 1 - Asset 4 - 13/07/19 - 31/07/19 - 170

I am at a bit of a loss how to do this for multiple records. I can create a single query that will filter 1 Asset and the dates, sum the runtime hours and give me the answer.. However I would like to be able to do it for all assets at once.

If anyone has any ideas would be greatly received!

Thanks

 

[arnelgp]

use the asset table as the base of your query:

select a.line, a.asset, 
          a.[date], 
          a.dateFrom, 
          a.dateTo, 
(select sum(b.[runtimhrs]) from runtimeTable as b 
   where b.line = a.line and 
         (b.runtimeDate between a.dateFrom and a.dateTo)) as runtimeHours
from asset as a;

 

[KLewisMFL]

use the asset table as the base of your query:

select a.line, a.asset, 
          a.[date], 
          a.dateFrom, 
          a.dateTo, 
(select sum(b.[runtimhrs]) from runtimeTable as b 
   where b.line = a.line and 
         (b.runtimeDate between a.dateFrom and a.dateTo)) as runtimeHours
from asset as a;

Thankyou very Much.. Worked like a charm!