Solved

SQL Query Help

Posted on 2013-12-23
6
505 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

919 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

12 Experts available now in Live!

Get 1:1 Help Now