Extracting Data from single table

Question

Extracting Data from single table

dthmtlgod

Old Hand

Points: 360
More actions
October 15, 2012 at 11:14 am

#393388

I am attempting to pull only open tickets from my table. Thing is we have duplicate records with different status code in our data
TICKET STATUS
9543 1
9543 5
9543 5
9544 1
9546 1
9547 1
9547 5
I only want the records with a 1 and not a 5 status, so in the above sample, I should only return 9544 and 9546.
Any suggestions are much appreciated.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

What exactly are you trying to do here?

Can you please post better data, DDL and DML statements. You'll get more help if you follow the suggestions in this Post http://www.sqlservercentral.com/articles/Best+Practices.

Also Post what you have tried, and we can go from there

Lowell SSC Guru Points: 323504 More actions · Answer 2

i think a simple test with an EXISTS and correlated subquery will get you want you want:

With YOURTABLE (TICKET,STATUS)

AS

(

SELECT 9543,1 UNION ALL

SELECT 9543,5 UNION ALL

SELECT 9544,1 UNION ALL

SELECT 9546,1 UNION ALL

SELECT 9547,1 UNION ALL

SELECT 9547,5

)

--the above is just setup data to represent your data , wherever it is.

--data like that makes it easy for anyone to offer helpful posts.

--proposed example is just what is below.

SELECT

*

FROM YOURTABLE OPENTIX

WHERE NOT EXISTS(SELECT

1

FROM YOURTABLE CLOSEDTIX

WHERE CLOSEDTIX.TICKET = OPENTIX.TICKET

AND CLOSEDTIX.STATUS = 5)

AND OPENTIX.STATUS = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dthmtlgod Old Hand Points: 360 More actions · Answer 3

Lowell, that was exactly what I was looking for. Thank you so much for the assistance.:-)

drew.allen SSC Guru Points: 76944 More actions · Answer 4

I think that a GROUP BY is much simpler and probably faster, too.

With YOURTABLE (TICKET,STATUS)

AS

(

SELECT 9543,1 UNION ALL

SELECT 9543,5 UNION ALL

SELECT 9544,1 UNION ALL

SELECT 9546,1 UNION ALL

SELECT 9547,1 UNION ALL

SELECT 9547,5

)

SELECT TICKET, MAX(STATUS) AS STATUS

FROM YOURTABLE

GROUP BY TICKET

HAVING MAX(STATUS) = 1

GO

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

dthmtlgod Old Hand Points: 360 More actions · Answer 5

dthmtlgod

Old Hand

Points: 360

October 15, 2012 at 3:27 pm

#1549328

Thank you for the reply. I will keep this handy.

vinu512 SSCoach Points: 15729 More actions · Answer 6

This is another way of doing it...and I guess the simplest way....:-)

With YOURTABLE (TICKET,STATUS)

AS

(

SELECT 9543,1 UNION ALL

SELECT 9543,5 UNION ALL

SELECT 9544,1 UNION ALL

SELECT 9546,1 UNION ALL

SELECT 9547,1 UNION ALL

SELECT 9547,5

)

Select * From YOURTABLE

Where TICKET NOT IN (Select TICKET From YOURTABLE Where STATUS = 5)

GO

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 7

drew.allen (10/15/2012)
I think that a GROUP BY is much simpler and probably faster, too.
With YOURTABLE (TICKET,STATUS)
AS
(
SELECT 9543,1 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9544,1 UNION ALL
SELECT 9546,1 UNION ALL
SELECT 9547,1 UNION ALL
SELECT 9547,5
)
SELECT TICKET, MAX(STATUS) AS STATUS
FROM YOURTABLE
GROUP BY TICKET
HAVING MAX(STATUS) = 1
GO
Drew

Oh, be careful. For this particular data, that will absolutely work. If they introduce status less than 5 that aren't cause for rejection, it won't.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

vinu512 (10/18/2012)
This is another way of doing it...and I guess the simplest way....:-)
With YOURTABLE (TICKET,STATUS)
AS
(
SELECT 9543,1 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9544,1 UNION ALL
SELECT 9546,1 UNION ALL
SELECT 9547,1 UNION ALL
SELECT 9547,5
)
Select * From YOURTABLE
Where TICKET NOT IN (Select TICKET From YOURTABLE Where STATUS = 5)
GO

It's a good idea for the data given. I have a hard time believing that they have no statuses between 1 and 5, though. If they do, the code you posted would produce duplicate ticket numbers. Of course, that may be just what the doctor ordered if there are status other than 1 and 5.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 9

dthmtlgod (10/15/2012)
I am attempting to pull only open tickets from my table. Thing is we have duplicate records with different status code in our data
TICKET STATUS
9543 1
9543 5
9543 5
9544 1
9546 1
9547 1
9547 5
I only want the records with a 1 and not a 5 status, so in the above sample, I should only return 9544 and 9546.
Any suggestions are much appreciated.

The question that begs to be answered here is.... in real life, will there be any tickets with a status other than 1 and 5 and what do you want to do if that's true?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

Lowell (10/15/2012)
i think a simple test with an EXISTS and correlated subquery will get you want you want:
With YOURTABLE (TICKET,STATUS)
AS
(
SELECT 9543,1 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9544,1 UNION ALL
SELECT 9546,1 UNION ALL
SELECT 9547,1 UNION ALL
SELECT 9547,5
)
--the above is just setup data to represent your data , wherever it is.
--data like that makes it easy for anyone to offer helpful posts.
--proposed example is just what is below.
SELECT
*
FROM YOURTABLE OPENTIX
WHERE NOT EXISTS(SELECT
1
FROM YOURTABLE CLOSEDTIX
WHERE CLOSEDTIX.TICKET = OPENTIX.TICKET
AND CLOSEDTIX.STATUS = 5)
AND OPENTIX.STATUS = 1

Same thing here, ol' friend. This works perfectly if there are interim statuses and they don't mind seeing those interim status or the dupe ticket listings. If they want just a single ticket number for each ticket that's still open, this will probably need to change. Of course, we need to hear from the OP to know for sure.

Scratch that with my apologies. I made a mistake. Your's works fine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 11

dthmtlgod (10/15/2012)
I only want the records with a 1 and not a 5 status

;WITH SampleData (TICKET, [STATUS]) AS (

SELECT 9543, 1 UNION ALL

SELECT 9543, 5 UNION ALL

SELECT 9544, 1 UNION ALL

SELECT 9546, 1 UNION ALL

SELECT 9547, 1 UNION ALL

SELECT 9547, 5)

SELECT TICKET

FROM SampleData

WHERE [STATUS] IN (1,5)

GROUP BY TICKET

HAVING COUNT(*) = 1

AND MIN([STATUS]) = 1

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]