SQL Query Help

Using SQL Server 2008:

I have this query :

Select       
      Distinct TR.Serial_Number,      
    CASE
      WHEN (TR.Test_Result LIKE '%PASS%') THEN  'Pass'
      ELSE ''      
      END   as 'DecayResults',
      MAX(TR.Date_Time)  as 'DecayDate'      
FROM
      dbo.Test_Results TR  
WHERE       
      (TR.Date_Time BETWEEN '2013-12-18 00:03:44.890' AND '2013-12-20 00:03:44.890')  
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14
      AND (TR.System_ID LIKE '%Decay%')
Group By
      TR.Serial_Number,TR.Test_Result
      

I am getting the results below:
SerialNumber                    DecayResults      DecayDate

C037197 2094 13           Pass                   2013-12-18 10:48:04.810
C037197 2106 13           Pass                   2013-12-18 17:06:32.657
C037197 2107 13           Pass                   2013-12-19 14:58:39.407
C037197 2110 13                             2013-12-18 18:49:02.067
C037197 2110 13           Pass                  2013-12-18 18:53:30.130
C037197 2120 13           Pass                  2013-12-18 17:11:01.850
C037197 2127 13           Pass                  2013-12-19 13:34:57.447
C037197 2128 13           Pass                 2013-12-19 13:38:09.303
C037197 2150 13           Pass                  2013-12-19 08:08:34.647
C037197 2153 13           Pass           2013-12-19 22:50:46.050
C037197 2154 13           Pass                2013-12-19 23:04:47.570
C037197 2158 13                           2013-12-18 07:28:33.420
C037197 2158 13           Pass                2013-12-18 07:31:43.513
C037197 2159 13           Pass        2013-12-18 07:38:36.027
C037197 2161 13           Pass               2013-12-18 13:05:24.810
C037197 2162 13           Pass                2013-12-18 07:35:20.267
C037197 2163 13           Pass               2013-12-18 13:07:33.103
C037248 1170 13                          2013-12-18 13:46:12.587
C037248 1170 13           Pass               2013-12-18 13:44:43.637
C037248 1170 13           Pass               2013-12-18 13:49:47.523
C037743 1009 13                          2013-12-19 00:23:26.703
C037743 1009 13           Pass               2013-12-19 00:27:38.137
C037743 1010 13                          2013-12-19 00:17:02.610
C037743 1010 13                          2013-12-19 00:20:36.140
C037743 1010 13                          2013-12-19 00:23:26.483
C037743 1010 13           Pass               2013-12-19 00:27:38.007
C037743 1011 13           Pass               2013-12-19 00:17:14.020
C037743 1012 13           Pass         2013-12-19 00:20:12.317


I want to eliminate duplicate serial numbers from my above query....

Also I want to keep 'Pass' records and eliminate records without 'Pass' for the same SerialNumber....


Any Idea?
LVL 8
mani_saiAsked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
You may try this:
Select      
      TR.Serial_Number,      
    MAX(CASE
      WHEN (TR.Test_Result LIKE '%PASS%') THEN  'Pass'
      ELSE ''      
      END)   as 'DecayResults',
      MAX(TR.Date_Time)  as 'DecayDate'      
FROM
      dbo.Test_Results TR  
WHERE      
      (TR.Date_Time BETWEEN '2013-12-18 00:03:44.890' AND '2013-12-20 00:03:44.890')  
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14
      AND (TR.System_ID LIKE '%Decay%')
Group By
      TR.Serial_Number
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I want to eliminate duplicate serial numbers from my above query....
Okay, but when there are duplicates, you'll need to spell out for us what logic you want as far as which row to return.

For example, the below has now Passes, but three dates.  Which date to pick?

C037743 1010 13                          2013-12-19 00:17:02.610
C037743 1010 13                          2013-12-19 00:20:36.140
C037743 1010 13                          2013-12-19 00:23:26.483
0
 
mani_saiAuthor Commented:
Thanks. It worked.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
mani_saiAuthor Commented:
For example, the below has now Passes, but three dates.  Which date to pick?

C037743 1010 13                          2013-12-19 00:17:02.610
C037743 1010 13                          2013-12-19 00:20:36.140
C037743 1010 13                          2013-12-19 00:23:26.483
C037743 1010 13           Pass               2013-12-19 00:27:38.007  <--- I want this record in my output...
0
 
pcelbaCommented:
My query isn't perfect...

If the possibility exists to have not passed records with newer DecayDate then the datetime value selected does not necessarily belong to the passed records... You will need UNION query which will join passes records with ones having just not passed status:
SELECT * FROM (
Select 
      TR.Serial_Number,      
      'Pass' as 'DecayResults',
      MAX(TR.Date_Time)  as 'DecayDate'      
FROM 
      dbo.Test_Results TR  
WHERE       
      (TR.Date_Time BETWEEN '2013-12-18 00:03:44.890' AND '2013-12-20 00:03:44.890')  
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14 
      AND (TR.System_ID LIKE '%Decay%') 
      AND TR.Test_Result LIKE '%PASS%'
GROUP BY 1,2 ) passed
UNION ALL
SELECT * FROM (
Select 
      TR.Serial_Number,      
      '    ' as 'DecayResults',
      MAX(TR.Date_Time)  as 'DecayDate'      
FROM 
      dbo.Test_Results TR  
WHERE       
      (TR.Date_Time BETWEEN '2013-12-18 00:03:44.890' AND '2013-12-20 00:03:44.890')  
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14 
      AND (TR.System_ID LIKE '%Decay%') 
      AND TR.Test_Result NOT LIKE '%PASS%'
      AND TR.Serial_Number NOT IN (
          Select TR.Serial_Number
            FROM dbo.Test_Results TR  
           WHERE (TR.Date_Time BETWEEN '2013-12-18 00:03:44.890' AND '2013-12-20 00:03:44.890')  
             AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14 
             AND (TR.System_ID LIKE '%Decay%') 
             AND TR.Test_Result LIKE '%PASS%'
           )
Group By 1,2 ) notpassed

Open in new window

... which makes the query much more complex and some additional refactoring would be a plus probably.
0
 
mani_saiAuthor Commented:
Thanks. Let me check it out.
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.

All Courses

From novice to tech pro — start learning today.