Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL query help

Posted on 2014-01-03
13
Medium Priority
?
467 Views
Last Modified: 2014-01-06
I have this SQL query:

Select       
      TR.Serial_Number,      
      MAX(CASE
        WHEN (TR.Test_Result LIKE '%PASS%' ) THEN  'Pass'
        ELSE ''      
      END)  as 'ChamberResults',
      MIN(TR.Date_Time) as 'ChamberDate'
FROM
      dbo.Test_Results TR WITH (NOLOCK)  
WHERE       
      (TR.Date_Time BETWEEN '2013-12-23 00:32:35.757' AND '2013-12-23 23:32:35.757')       
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14
      AND (System_ID LIKE '%Chamber%')       
Group By
      TR.Serial_Number



I want to modify the above query to pick only  the first unique serial_number based on the Date_Time ascending and do CASE statement check on the first serial_number...


For e.g: C037197 2089 13 appears 3 times , but I need the first row to evaluate CASE statement..ignore the other 2 rows...



Input Data is attached..

Any idea?

Thanks
Capture.PNG
0
Comment
Question by:mani_sai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39755152
THis can be achieved by using the row_number() function and a CTE

;WITH CTE AS 
(
SELECT ROW_NUMBER() OVER(partition by Serial_Number ORDER BY date_time) RN, * 
FROM dbo.test_results WITH (NOLOCK)
)
Select       
      TR.Serial_Number,      
       CASE WHEN rn = 1 THEN
       CASE  
        WHEN (TR.Test_Result LIKE '%PASS%' ) THEN  'Pass' 
        ELSE ''      
      END 
      ELSE '' END )  as 'ChamberResults',
      TR.Date_Time as 'ChamberDate'
FROM 
      CTE TR   
WHERE       
      (TR.Date_Time BETWEEN '2013-12-23 00:32:35.757' AND '2013-12-23 23:32:35.757')       
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14 
      AND (System_ID LIKE '%Chamber%')       

Open in new window

0
 
LVL 8

Author Comment

by:mani_sai
ID: 39755156
Thanks

how to run this from Query Analyzer?
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39755157
just copy and paste it in the query analyzer as you do it for all select queries....

No special thing required
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 8

Author Comment

by:mani_sai
ID: 39755158
I am  using SQL Server 2000   (8.0.2066)
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39755161
Then the above solution wont work, as you have sql 2005 and sql 2008 in your question, I assumed you are SQL 2005 or 2008, let me re-write this for SQL 2000 it will take a while.
0
 
LVL 8

Author Comment

by:mani_sai
ID: 39755163
Sorry for that.. Thanks.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39755164
Try the below one

SELECT TR1.*,TR2.ChamberResults 
FROM dbo.Test_Results TR1 WITH (NOLOCK)  
LEFT JOIN (
Select       
      TR.Serial_Number,      
      MAX(CASE 
        WHEN (TR.Test_Result LIKE '%PASS%' ) THEN  'Pass' 
        ELSE ''      
      END)  as 'ChamberResults',
      MIN(TR.Date_Time) as 'ChamberDate'
FROM 
      dbo.Test_Results TR WITH (NOLOCK)  
WHERE       
      (TR.Date_Time BETWEEN '2013-12-23 00:32:35.757' AND '2013-12-23 23:32:35.757')       
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14 
      AND (System_ID LIKE '%Chamber%')       
Group By 
      TR.Serial_Number 
) AS TR2
ON   TR1.Serial_Number = TR2.Serial_Number
AND TR1.date_time = TR2.ChamberDate

Open in new window


Check the above out.
0
 
LVL 8

Author Comment

by:mani_sai
ID: 39755174
It ran for 45 seconds and pulled all the data from dbo.Test_Results  table instead of just the filtered result...

normally the query runs in 2 seconds or less...
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39755180
My apologies, I missed the statement Ignore the other 2 rows in your question above

try the below
SELECT TR1.*,TR2.ChamberResults 
FROM dbo.Test_Results TR1 WITH (NOLOCK)  
JOIN (
Select       
      TR.Serial_Number,      
      MAX(CASE 
        WHEN (TR.Test_Result LIKE '%PASS%' ) THEN  'Pass' 
        ELSE ''      
      END)  as 'ChamberResults',
      MIN(TR.Date_Time) as 'ChamberDate'
FROM 
      dbo.Test_Results TR WITH (NOLOCK)  
WHERE       
      (TR.Date_Time BETWEEN '2013-12-23 00:32:35.757' AND '2013-12-23 23:32:35.757')       
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14 
      AND (System_ID LIKE '%Chamber%')       
Group By 
      TR.Serial_Number 
) AS TR2
ON   TR1.Serial_Number = TR2.Serial_Number
AND TR1.date_time = TR2.ChamberDate

Open in new window

0
 
LVL 8

Author Comment

by:mani_sai
ID: 39755190
Thanks, query is not working.. but not getting the expected results..

For e.g: C037197 2089 13  .....appears 3 times , but I need the first row (by earliest Date_time) to evaluate CASE statement..ignore the other 2 rows...

so my case statement should not pass the coil...because first row is failed...

where as C037197 2139 13...should appeared only once and passed, so it should pass..
0
 
LVL 8

Author Comment

by:mani_sai
ID: 39755208
Got it working...

Thanks for your help...  Instead of MAX..i have to use MIN....


SELECT TR2.Serial_Number, TR2.ChamberResults , TR2.ChamberDate
FROM dbo.Test_Results TR1 WITH (NOLOCK)  
JOIN (
Select      
      TR.Serial_Number,      
      MIN(CASE
        WHEN (TR.Test_Result LIKE '%PASS%' ) THEN  'Pass'
        ELSE ''      
      END)  as 'ChamberResults',
      MIN(TR.Date_Time) as 'ChamberDate'
FROM
      dbo.Test_Results TR WITH (NOLOCK)  
WHERE      
      (TR.Date_Time BETWEEN '2013-12-19 00:32:35.757' AND '2013-12-23 23:32:35.757')      
      AND (ISNULL(TR.Serial_Number,'')<>'') AND LEN(TR.Serial_Number) > = 14
      AND (System_ID LIKE '%Chamber%')   AND     Serial_Number IN( 'C037197 2139 13', 'C037197 2089 13')
Group By
      TR.Serial_Number
) AS TR2
ON   TR1.Serial_Number = TR2.Serial_Number
AND TR1.date_time = TR2.ChamberDate
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39756714
Got it working...
Excellent! Now please close the question.
0
 
LVL 8

Author Closing Comment

by:mani_sai
ID: 39759061
Thanks for your help.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

715 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