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