0

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?

4
  • So far, I have only tried to include an "IN" clause within the "Where" clause of the second query, where I basically paste in the first entire query. I have found similar solutions on this and other forums that lead me in that direction. However, it doesn't work.
    – pbean
    Mar 8, 2016 at 17:05
  • What version of SQL Server are you using? Please add the tag as that may help narrow the answers. Have you tried a CTE? Mar 8, 2016 at 17:10
  • I'm Using SQL-SERVER_2012 Professional, Sorry for the delay, I'm adding correct tag now.
    – pbean
    Mar 8, 2016 at 17:16
  • I Have not tried a CTE. I am relatively new to SQL, so I may need some hand holding on implementing some solutions. Thanks.
    – pbean
    Mar 8, 2016 at 17:26

1 Answer 1

0

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
9
  • Well, I just ran the query and it didn't break, o you are one step closer to being a magician in my eyes. Next I will verify to make sure that the results are accurate. THANKS!
    – pbean
    Mar 8, 2016 at 17:34
  • The solution is almost complete. My only problem now is that the dates don't match. The output from the solution needs to include only the output values (first 12 char of Name), as well as only the matching time stamps. Simple solution?
    – pbean
    Mar 8, 2016 at 17:53
  • Should it match BSCv1.timestamp or BSCv2.timestamp? Mar 8, 2016 at 18:06
  • I dont think it should matter, because BSCV1 and BSCV2 timestamps are already programmed to match in output.
    – pbean
    Mar 8, 2016 at 18:09
  • I edited the added inner join to: inner join Answer on (Answer.Name = LEFT(BSCV1.Value, 12) and Answer.Timestamp = BSCV1.Timestamp). Which didn't produce anything. THen I changed the "=" to "<=" and also tried a ">=". Both produced legitimate results, and I think there may be some issues with the timing of the data syncs for the two table pairs. In a perfect world, there should be matching time-stamps for all records from both table pairs.
    – pbean
    Mar 8, 2016 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.