Solved

need select statement

Posted on 2007-03-28
6
169 Views
Last Modified: 2010-03-19
i have a table in sql 2005 with the following fields:

tblOrders: Customer, OrderStatus, Date

I need to find all orders that have been resubmitted once rejected, by Customer. For example:

Customer     OrderStatus     Date
abc group    rejected            12/12/2006
fgh inc         pending             12/13/2006
abc group   submitted           1/7/2007
fgh inc        submitted           1/7/2006
fgh inc        rejected             1/9/2006

the result here should be only:
abc group because they are the only ones that were rejected at some date in the past and then resubmitted.
fgh in would not be in this list because following their rejection they did not resubmit anything...

0
Comment
Question by:acdagirl
  • 3
  • 3
6 Comments
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
Try this:

SELECT * FROM tblOrders t1 WHERE OrderStatus='submitted' and exists (SELECT * FROM tblOrders t1 WHERE OrderStatus='rejected' AND t1.Customer=t2.Customer)
0
 

Author Comment

by:acdagirl
Comment Utility
WHERE OrderStatus='submitted' clause is too constricting - the status could be anything, not just submitted...
0
 

Author Comment

by:acdagirl
Comment Utility
shouldn't the second t1 be a t2?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:acdagirl
Comment Utility
but I also need to account for the timeline... your query doesn't exclude orders that were resubmitted *after* they were rejected only. It yields all customers that have at any point been rejected and/or accepted...
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
yes. Sorry - long day :)

And if the OrderStatus='submitted' is too restrictive, simply remove it.

SELECT * FROM tblOrders t1 WHERE EXISTS (SELECT * FROM tblOrders t2 WHERE OrderStatus='rejected' AND t1.Customer=t2.Customer)
0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
Comment Utility
How about this?

SELECT * FROM tblOrders t1 WHERE OrderStatus<>'rejected' AND EXISTS (SELECT * FROM tblOrders t2 WHERE OrderStatus='rejected' AND t1.Customer=t2.Customer AND t2.Date<t1.Date)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

16 Experts available now in Live!

Get 1:1 Help Now