Using Partial String match from one query results in where clause for another SQL Server 2012

Question

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?

Answer 1

You could try using CTE. This will take your first query as base for the CTE and then use it to join on the second query. I have not tested this code but I think it may work:

WITH Answer (Field_ID, Timestamp, Name, Value, ID)
AS
(
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 --Order by not needed in CTE
)
SELECT BSCv1.Timestamp AS "Field Timestamp"
    , BSCV1.Value AS Correlation_Pair 
    , BSCV2.Value AS "Percent Change"
    , BSCF1.Name AS "Field Name"
    , BSCF1.ID AS "DEV Name ID"
    , BSCV1.Field_ID AS "Field ID"
    , BSCF2.Name AS "Value Name"
    , BSCV2.Field_ID AS "Value ID"
    , BSCF2.ID AS "V ID"
    , BSCv2.Timestamp AS "Value Timestamp"
FROM [BabyCare].[dbo].[BSC_v3_values] AS BSCV1
INNER JOIN Answer 
    ON Answer.Name = LEFT(BSCV1.Value, 12)-- This may be the link you are seeking
        AND DATEADD(MINUTE, -10, Answer.Timestamp) = BSCv1.Timestamp
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