Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

SQL query help

I have this SQL query:

Select       
      TR.Serial_Number,      
      MAX(CASE
        WHEN (TR.Test_Result LIKE '%PASS%' ) THEN  'Pass'
        ELSE ''      
      END)  as 'ChamberResults',
      MIN(TR.Date_Time) as 'ChamberDate'
FROM
      dbo.Test_Results TR WITH (NOLOCK)  
WHERE       
      (TR.Date_Time BETWEEN '2013-12-23 00:32:35.757' AND '2013-12-23 23:32:35.757')       
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14
      AND (System_ID LIKE '%Chamber%')       
Group By
      TR.Serial_Number



I want to modify the above query to pick only  the first unique serial_number based on the Date_Time ascending and do CASE statement check on the first serial_number...


For e.g: C037197 2089 13 appears 3 times , but I need the first row to evaluate CASE statement..ignore the other 2 rows...



Input Data is attached..

Any idea?

Thanks
Capture.PNG
0
mani_sai
Asked:
mani_sai
  • 7
  • 5
1 Solution
 
Surendra NathTechnology LeadCommented:
THis can be achieved by using the row_number() function and a CTE

;WITH CTE AS 
(
SELECT ROW_NUMBER() OVER(partition by Serial_Number ORDER BY date_time) RN, * 
FROM dbo.test_results WITH (NOLOCK)
)
Select       
      TR.Serial_Number,      
       CASE WHEN rn = 1 THEN
       CASE  
        WHEN (TR.Test_Result LIKE '%PASS%' ) THEN  'Pass' 
        ELSE ''      
      END 
      ELSE '' END )  as 'ChamberResults',
      TR.Date_Time as 'ChamberDate'
FROM 
      CTE TR   
WHERE       
      (TR.Date_Time BETWEEN '2013-12-23 00:32:35.757' AND '2013-12-23 23:32:35.757')       
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14 
      AND (System_ID LIKE '%Chamber%')       

Open in new window

0
 
mani_saiAuthor Commented:
Thanks

how to run this from Query Analyzer?
0
 
Surendra NathTechnology LeadCommented:
just copy and paste it in the query analyzer as you do it for all select queries....

No special thing required
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
mani_saiAuthor Commented:
I am  using SQL Server 2000   (8.0.2066)
0
 
Surendra NathTechnology LeadCommented:
Then the above solution wont work, as you have sql 2005 and sql 2008 in your question, I assumed you are SQL 2005 or 2008, let me re-write this for SQL 2000 it will take a while.
0
 
mani_saiAuthor Commented:
Sorry for that.. Thanks.
0
 
Surendra NathTechnology LeadCommented:
Try the below one

SELECT TR1.*,TR2.ChamberResults 
FROM dbo.Test_Results TR1 WITH (NOLOCK)  
LEFT JOIN (
Select       
      TR.Serial_Number,      
      MAX(CASE 
        WHEN (TR.Test_Result LIKE '%PASS%' ) THEN  'Pass' 
        ELSE ''      
      END)  as 'ChamberResults',
      MIN(TR.Date_Time) as 'ChamberDate'
FROM 
      dbo.Test_Results TR WITH (NOLOCK)  
WHERE       
      (TR.Date_Time BETWEEN '2013-12-23 00:32:35.757' AND '2013-12-23 23:32:35.757')       
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14 
      AND (System_ID LIKE '%Chamber%')       
Group By 
      TR.Serial_Number 
) AS TR2
ON   TR1.Serial_Number = TR2.Serial_Number
AND TR1.date_time = TR2.ChamberDate

Open in new window


Check the above out.
0
 
mani_saiAuthor Commented:
It ran for 45 seconds and pulled all the data from dbo.Test_Results  table instead of just the filtered result...

normally the query runs in 2 seconds or less...
0
 
Surendra NathTechnology LeadCommented:
My apologies, I missed the statement Ignore the other 2 rows in your question above

try the below
SELECT TR1.*,TR2.ChamberResults 
FROM dbo.Test_Results TR1 WITH (NOLOCK)  
JOIN (
Select       
      TR.Serial_Number,      
      MAX(CASE 
        WHEN (TR.Test_Result LIKE '%PASS%' ) THEN  'Pass' 
        ELSE ''      
      END)  as 'ChamberResults',
      MIN(TR.Date_Time) as 'ChamberDate'
FROM 
      dbo.Test_Results TR WITH (NOLOCK)  
WHERE       
      (TR.Date_Time BETWEEN '2013-12-23 00:32:35.757' AND '2013-12-23 23:32:35.757')       
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14 
      AND (System_ID LIKE '%Chamber%')       
Group By 
      TR.Serial_Number 
) AS TR2
ON   TR1.Serial_Number = TR2.Serial_Number
AND TR1.date_time = TR2.ChamberDate

Open in new window

0
 
mani_saiAuthor Commented:
Thanks, query is not working.. but not getting the expected results..

For e.g: C037197 2089 13  .....appears 3 times , but I need the first row (by earliest Date_time) to evaluate CASE statement..ignore the other 2 rows...

so my case statement should not pass the coil...because first row is failed...

where as C037197 2139 13...should appeared only once and passed, so it should pass..
0
 
mani_saiAuthor Commented:
Got it working...

Thanks for your help...  Instead of MAX..i have to use MIN....


SELECT TR2.Serial_Number, TR2.ChamberResults , TR2.ChamberDate
FROM dbo.Test_Results TR1 WITH (NOLOCK)  
JOIN (
Select      
      TR.Serial_Number,      
      MIN(CASE
        WHEN (TR.Test_Result LIKE '%PASS%' ) THEN  'Pass'
        ELSE ''      
      END)  as 'ChamberResults',
      MIN(TR.Date_Time) as 'ChamberDate'
FROM
      dbo.Test_Results TR WITH (NOLOCK)  
WHERE      
      (TR.Date_Time BETWEEN '2013-12-19 00:32:35.757' AND '2013-12-23 23:32:35.757')      
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14
      AND (System_ID LIKE '%Chamber%')   AND     Serial_Number IN( 'C037197 2139 13', 'C037197 2089 13')
Group By
      TR.Serial_Number
) AS TR2
ON   TR1.Serial_Number = TR2.Serial_Number
AND TR1.date_time = TR2.ChamberDate
0
 
Anthony PerkinsCommented:
Got it working...
Excellent! Now please close the question.
0
 
mani_saiAuthor Commented:
Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now