sql server - Joining data with different timestamp intervals in sql

Question

I have two tables different with each other. The first table has this form

        Date1                 NGVolA
2020-11-20 12:05:19.000        10461
2020-11-20 12:14:11.000        15692
2020-11-20 12:25:53.000        20627

The second table has this form

          char_time               disch_time         FUR       char_temp         disch_temp
2020-11-20 12:06:56.600    2020-11-20 12:08:28.193    A           59               1150
2020-11-20 12:07:02.693    2020-11-20 12:09:12.177    B           61               1140
2020-11-20 12:12:18.350    2020-11-20 12:13:46.350    A           77               1160
2020-11-20 12:16:16.070    2020-11-20 12:20:38.333    A           49               1156
2020-11-20 12:19:15.520    2020-11-20 12:21:22.317    A           75               1180
2020-11-20 12:27:10.513    2020-11-20 12:30:41.287    B           147              1165
2020-11-20 12:30:11.593    2020-11-20 12:32:33.257    A           72               1195

What I want is to get data from the second table and aggregate them based on time intervals of the first table. e.g in the time interval between 12:05:19 and 12:10:19 of first table find how many char_time events happened and average the char_temp of them. Equally for the disch_time and disch_temp.

What I want is to get is a table like this

           Date1       Avg_Char_Temp_A   Char_events_A  Disch_events_A  Avg_DisCh_Temp_A      NGVolA  FUR
2020-11-20 12:05:19.000    68               2.0            1.0               1155             10461    A
2020-11-20 12:14:11.000    62               2.0            2.0               1168             15692    A
2020-11-20 12:25:53.000    72               1.0            1.0               1195             20627    A

What i have done so far is

DECLARE @StartDate nvarchar(20)
DECLARE @EndDate nvarchar(20)
     
SET @StartDate ='2020-11-20 12:00:00'
SET @EndDate =  '2020-11-20 23:59:59'
        
SELECT  
[Date1]=  a.[_TimeStamp],
[Avg_Charg_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[charge_temperature],'0.0')) else null end),
[Char_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end),
[Disch_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end)
[Avg_DisCh_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[ave_disch_temp],'0.0')) else null end),
[NGVolA]=sum(CASE WHEN a.[Furnace] ='A' then convert(real,isnull (a.[NG_AVG_MEAS_FLOW],'0.0')) else 0.0 end ) 
      
 FROM (select a.*, (select min(aa.[_TimeStamp])
            from fix.dbo.IBA_Data aa 
            where aa.[_TimeStamp] > a.[_TimeStamp])as next_time_2
    from fix.dbo.IBA_Data a) a 
    Left JOIN ADB.dbo.Temp_Aims b on b.[charge_time] >= a.[_TimeStamp] and b.[charge_time] < a.[next_time_2]    

WHERE 
     CONVERT(datetime, a.[_TimeStamp], 20)  BETWEEN CONVERT(datetime, @StartDate , 20) AND CONVERT(datetime, @EndDate , 20)     

GROUP BY
a.[_TimeStamp],

ORDER BY 
  a.[_TimeStamp]

The issue with the above query is that Char_events_A and Disch_events_A are overestimated(i am getting double what i am expecting) and I cannot aggregate Avg_DisCh_Temp_A on _TimeStamp and thus i cannot calculate Avg_DisCh_Temp_A. Also keep in mind that the server i getting my data has a compatibility level SQL server 2000. Any suggestion will be appreciated.

@Chuma i am getting an error " 'MasterDetailLink' is not a recognized GROUP BY" and "Incorrect syntax near 'WorkingData'. "

With MasterDetailLink as
(select 
a.[_TimeStamp], a.[NGVolA], b.[charge_time], b.[discharge_time] 
from fix.dbo.Fce_IBA_Data a inner join ALPHADB.dbo.Mill_Temp_Aims b on a.[_TimeStamp] between b.[charge_time] and b.[discharge_time] ),

WorkingData as
(select 
a.[_TimeStamp], a.[NGVolA], b.*

from MasterDetailLink a inner join ALPHADB.dbo.Mill_Temp_Aims b on a.[charge_time]=b.[charge_time] and a.[charge_time]=b.[discharge_time] )

select 
a.[_TimeStamp], 
[Avg_Charg_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[charge_temperature],'0.0')) else null end),
[Char_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end),
[Disch_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end),
[Avg_DisCh_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[ave_disch_temp],'0.0')) else null end),
[NGVolA]=sum(CASE WHEN a.[Furnace] ='A' then convert(real,isnull (a.[NG_AVG_MEAS_FLOW],'0.0')) else 0.0 end ) 
from WorkingData

group by a.[charge_time]

With MasterDetailLink as
(
select a.[charge_time], a.[NGVolA], b.[charge_time], b.[discharge_time] 
from fix.dbo.Fce_IBA_Data a inner join ALPHADB.dbo.Mill_Temp_Aims b on a.[_TimeStamp] between b.[charge_time] and b.[discharge_time] )
 
 WorkingData as
(
select 
a.[_TimeStamp], a.[NGVolA], b.*
from MasterDetailLink a inner join ALPHADB.dbo.Mill_Temp_Aims b on a.[charge_time]=b.[charge_time] and a.[charge_time]=b.[discharge_time]) 

select 
a.[_TimeStamp], 
[Avg_Charg_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[charge_temperature],'0.0')) else null end),
[Char_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end),
[Disch_events_A]=sum(case when b.[FURNACE] ='A' then 1.0 else null end),
[Avg_DisCh_Temp_A]=avg(CASE WHEN b.[FURNACE] ='A' then convert(real,isnull (b.[ave_disch_temp],'0.0')) else null end),
[NGVolA]=sum(CASE WHEN a.[Furnace] ='A' then convert(real,isnull (a.[NG_AVG_MEAS_FLOW],'0.0')) else 0.0 end ) 
from WorkingData
group by a.[_TimeStamp]

question from:https://stackoverflow.com/questions/65943239/joining-data-with-different-timestamp-intervals-in-sql

Answer 1

For this, for simplicity, we will break it down step by step. Our initial task should be to link the times between the parent table and child table. Since I don't have their actual names, I will call them master and detail. So linking the times in master and detail

With MasterDetailLink as
(
select a.Date1, a.NGVolA, b.char_time, b.disch_time 
from master a inner join detail b
on a.Date1 between b.char_time and b.disch_time 
) 

Now we'll just join this to the regular detail table and go from there

WorkingData as
(
select a.Date1, a.NGVolA, b.*
from MasterDetailLink a
 inner join detail b on a.char_time=b.char_time and a.char_time=b.disch_time 
)

Then from there, you can do any aggregations you need.

select Date1, <Aggregations here e.g. Sum(field1)>
from WorkingData
group by Date1

Putting it all together

With MasterDetailLink as
(
select a.Date1, a.NGVolA, b.char_time, b.disch_time 
from master a inner join detail b
on a.Date1 between b.char_time and b.disch_time 
), WorkingData as
(
select a.Date1, a.NGVolA, b.*
from MasterDetailLink a
 inner join detail b on a.char_time=b.char_time and a.char_time=b.disch_time 
) 
select Date1, <Aggregations here e.g. Sum(field1)>
from WorkingData
group by Date1