Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query Help

Posted on 2013-12-23
6
Medium Priority
?
563 Views
Last Modified: 2013-12-23
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?
0
Comment
Question by:mani_sai
  • 3
  • 2
6 Comments
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 39736042
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39736119
>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
 
LVL 8

Author Closing Comment

by:mani_sai
ID: 39736124
Thanks. It worked.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 8

Author Comment

by:mani_sai
ID: 39736129
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
 
LVL 43

Expert Comment

by:pcelba
ID: 39736193
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
 
LVL 8

Author Comment

by:mani_sai
ID: 39736198
Thanks. Let me check it out.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

What we learned in Webroot's webinar on multi-vector protection.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

886 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