?
Solved

Intersecting 2 recordsets based only on one field common to both

Posted on 2012-03-28
2
Medium Priority
?
466 Views
Last Modified: 2012-06-21
Without going into detail about how I arrived at the 2 queries below, what I need to do is identify records that meet these 2 criteria:
1. TestTypeID2 must appear in both recordsets (that is to say, tests were run on both machines 101 and 102).
2. The sum of the total failures between BOTH machines must be 4 or greater.

So, in the sample screenshot attached, you can see in the returned recordsets that:
- Only TestTypeID2 = 32 qualifies
- TestTypeID2 = 33 only had 3 failures total
- TestTypeID2 = 35 only had failures on machine 102

I believe I need some sort of intersection, but only based on the TestTypeID2 field.

Here are the queries:

SELECT PH.MachineID
      ,PTD2.TestTypeID AS TestTypeID2
      ,COUNT(PTD2.TestData) AS TotalFails
  FROM [Comtech_HFV6].[dbo].[tbl_PartHistory] PH
  LEFT JOIN tbl_PartTestData PTD
  ON PH.PartHistoryKey = PTD.PartHistoryKey
  LEFT JOIN tbl_PartTestData PTD2
  ON (PTD.PartHistoryKey = PTD2.PartHistoryKey) AND (PTD.TestTypeID + 6 = PTD2.TestTypeID )
  WHERE PH.SerialNumber = 'A22222222'
      AND PH.MachineID = 101
      AND PTD.TestTypeID BETWEEN 26 and 31
      AND PH.PassedStatus < 0
      AND CAST(PTD2.TestData AS REAL) = 0
      AND CAST(PTD.TestData AS REAL) < 900
    GROUP BY PTD2.TestTypeID, PH.MachineID
 
 SELECT PH.MachineID
      ,PTD2.TestTypeID AS TestTypeID2
      ,COUNT(PTD2.TestData) AS TotalFails
  FROM [Comtech_HFV6].[dbo].[tbl_PartHistory] PH
  LEFT JOIN tbl_PartTestData PTD
  ON PH.PartHistoryKey = PTD.PartHistoryKey
  LEFT JOIN tbl_PartTestData PTD2
  ON (PTD.PartHistoryKey = PTD2.PartHistoryKey) AND (PTD.TestTypeID + 6 = PTD2.TestTypeID )
  WHERE PH.SerialNumber = 'A22222222'
      AND PH.MachineID = 102
      AND PTD.TestTypeID BETWEEN 26 and 31
      AND PH.PassedStatus < 0
      AND CAST(PTD2.TestData AS REAL) = 0
      AND CAST(PTD.TestData AS REAL) < 900
    GROUP BY PTD2.TestTypeID, PH.MachineID
Sample-Recordsets.jpg
0
Comment
Question by:jdallen75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 2000 total points
ID: 37779386
Just replace your queries in the YourQuery1 and YourQuery2 and it will provide the result:

SELECT a.TestTypeID2 FROM (yourQuery1) a
JOIN (yourQuery2) b on b.TestTypeID2
and a.TotalFails + b.TotalFails >= 4
0
 

Author Closing Comment

by:jdallen75
ID: 37779644
Just needed slight modification:

SELECT a.TestTypeID2 FROM (yourQuery1) a
JOIN (yourQuery2) b
ON a.TestTypeID2 = b.TestTypeID2
WHERE a.TotalFails + b.TotalFails >= 4

But otherwise, nice and concise... thanks!
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question