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?

Microsoft SQL Server 2008Microsoft SQL ServerDatabases

Last Comment

Mani Pazhana

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Pavel Celba

12/23/2013

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Jim Horn

12/23/2013

>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 Pazhana

12/23/2013

ASKER

Thanks. It worked.

Mani Pazhana

12/23/2013

ASKER

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...

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Pavel Celba

12/23/2013

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 Pazhana

12/23/2013

ASKER

Thanks. Let me check it out.