I need results field of query1 to drive
I have one query that produces a table of several columns, namely "Time_stamp", "Value", and "Name" from two existing tables.
Select TOP 20
MshiftV1.Field_ID
,MshiftV1.Timestamp
,LEFT(MshiftF1.Name,12) as Name
,MshiftV1.Value
,MShiftF1.ID
From
[BabyCare].[dbo].[Meanshift_All_Tags_Alarms_Values_5Min_RealTime_V6] as MshiftV1
Inner Join
[BabyCare].[dbo].[Meanshift_All_Tags_Alarms_Fields_5Min_RealTime_V6] as MshiftF1 on MshiftV1.Field_ID = MshiftF1.ID
Where
MshiftV1.Quality = 1
and MshiftV1.Value not like '%INF%'
and MshiftV1.Value not like '%IND%'
and MshiftF1.Name not like '%_Splice_%'
order by
MshiftV1.Timestamp, ABS(convert (float, MshiftV1.Value)) desc
I have another, separate query for two different tables from the first, and it produces Fields like "Time_Stamp", "Correlation_Pair", and "Percent_Change".
select
BSCv1.Timestamp "Field Timestamp"
,BSCV1.Value as Correlation_Pair
,BSCV2.Value "Percent Change"
,BSCF1.Name "Field Name"
,BSCF1.ID "DEV Name ID"
,BSCV1.Field_ID "Field ID"
,BSCF2.Name "Value Name"
,BSCV2.Field_ID "Value ID"
,BSCF2.ID "V ID"
,BSCv2.Timestamp "Value Timestamp"
From
[BabyCare].[dbo].[BSC_v3_values] as BSCV1
inner join
[BabyCare].[dbo].[BSC_v3_fields] as BSCF1 on BSCV1.Field_ID = BSCF1.ID
inner join
[BabyCare].[dbo].[BSC_v3_values] as BSCV2 on BSCv1.Timestamp = BSCv2.timestamp
inner join
[BabyCare].[dbo].[BSC_v3_fields] as BSCF2 on BSCV2.Field_ID = BSCF2.ID
and BSCV1.Quality = 1
and BSCF1.Name like '%Dev_N%'
and BSCF2.Name like '%Dev_V%'
AND RIGHT (SUBSTRING(BSCF1.Name,5,5),3) = RIGHT (SUBSTRING(BSCF2.Name,5,5),3)
and BSCv1.Timestamp = BSCv2.Timestamp
where
BSCV1.Quality = 1
and BSCF1.Name like '%Dev_N%' and BSCF2.Name like '%Dev_V%'
AND RIGHT (SUBSTRING(BSCF1.Name,5,5),3) = RIGHT (SUBSTRING(BSCF2.Name,5,5),3)
and BSCv1.Timestamp = BSCv2.Timestamp
order by
ABS(convert(float,BSCV2.Value)) desc, BSCv1.Timestamp Desc
Both queries work great, but I have a recent need to combine results so that output from first query drives the second query. Specifically, I need the second query to only return records where the first 12 characters in the "BSCV1.Value" field are equal to the first 12 characters of the output of the first queries field "MshiftF1.Name", AND where "Timestamps" are equal.
Any thoughts?