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 PazhanaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornSQL Server Data DudeCommented:
>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
Mani PazhanaAuthor Commented:
Thanks. It worked.
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Mani PazhanaAuthor 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...
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.
Mani PazhanaAuthor Commented:
Thanks. Let me check it out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.