Solved

SQL Query Help

Posted on 2013-12-23
6
494 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 41

Accepted Solution

by:
pcelba earned 500 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 65

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 41

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now