February 19, 2007 at 8:11 am
I have a query:
SELECT orig.*, X.DisplayName AS Expr1, X.BirthDayNum AS Expr2,
X.BirthMonthNum AS Expr3,
X.BirthYearNum AS Expr4
FROM X INNER JOIN
Y ON X.PRIMARYKEY =
Y.Client.PRIMARYKEY INNER JOIN
(SELECT ReasonText, CreatedWhen, OrderStatusCode
FROM Y
WHERE (OrderStatusCode = 'perf')) orig INNER JOIN
(SELECT reasontext
FROM Y
WHERE orderstatuscode = 'perf'
GROUP BY reasontext
HAVING COUNT(*) > 1) dup ON orig.ReasonText =
dup.reasontext AND orig.ReasonText = dup.reasontext ON
Y.ReasonText = orig.ReasonText COLLATE
SQL_Latin1_General_CP1_CI_AS
WHERE (Y.CreatedWhen >= '20061101') AND
(Y.CreatedWhen < '20070215') AND
(Y.ReasonText LIKE 'Filler Order ID:%') OR
(Y.ReasonText LIKE 'Placer Order ID:%')
Filler Order ID: <A023403771020> Filler Facility ID: <RAD> | PERF | Good, KELLY |
Filler Order ID: <A023403771020> Filler Facility ID: <RAD> | PERF | Good, KELLY |
Filler Order ID: <1018863276856> Filler Facility ID: <RAD> | PERF | XTEST, TEST |
Filler Order ID: <1018863276856> Filler Facility ID: <RAD> | PERF | XTEST, TEST |
Filler Order ID: <A023403771020> Filler Facility ID: <RAD> | PERF | BAD, KELLY |
Filler Order ID: <A023403771020> Filler Facility ID: <RAD> | PERF | BAD, KELLY |
Filler Order ID: <1018863276856> Filler Facility ID: <RAD> | PERF | XTEST, TWO |
Filler Order ID: <1018863276856> Filler Facility ID: <RAD> | PERF | XTEST, TWO |
What I want is to identify when the filler order id is repeated for more than one person., ie Good, Kelly and Bad, Kelly
I don’t care if its repeated on the same person.
How do I do this? And thanks to SQLBILL for all previous help.
February 19, 2007 at 10:26 am
Your question and query are extremely confused.
It looks as though you need to extract FillerOrderID from ReasonText in order to count the number of people associated with each one. This is done in the derived table, D, below.
Hopefully this query will give a starting point for you to solve your problem.
SELECT *
FROM X
JOIN Y
ON X.X_PrimaryKey = Y.X_PrimaryKey
AND Y.CreatedWhen >= '20061101'
AND Y.CreatedWhen < '20070215'
JOIN (
SELECT D1.FillerOrderID
FROM (
SELECT DISTINCT
SUBSTRING(Y1.ReasonText, 19, PATINDEX('%>%', Y1.ReasonText) - 19) AS FillerOrderID
,Y1.X_PrimaryKey
FROM @Y Y1
WHERE Y1.ReasonText LIKE 'Filler Order ID: <%'
) D1
GROUP BY D1.FillerOrderID
HAVING COUNT(*) > 1
) D
ON Y.ReasonText LIKE 'Filler Order ID: <' + D.FillerOrderID + '>%'
February 19, 2007 at 2:23 pm
Thank you for helping. The filler order id field is one field and I do not think I need to extract the number. In essence what I want is to know when the filler order id is repeated on a different customer. The filler order id should be unique to one person but we are finding that it is not. I am trying to query to find when this "anomaly" happens. My query gives me extraneous results, as posted above. I only want the bolded results but am unable to fine tune.
Again, thanks for any help.
February 20, 2007 at 2:02 am
You will need to post sample data and expected results to get any useful help.
http://www.aspfaq.com/etiquette.asp?id=5006
February 20, 2007 at 7:01 am
This might work:
SELECT
* FROM X
INNER JOIN (
SELECT PK FROM Y
WHERE ReasonText IN (
SELECT Y.ReasonText
FROM X
INNER JOIN Y ON X.PK = Y.PK
WHERE Y.OrderStatusCode = 'perf'
GROUP BY Y.ReasonText
HAVING COUNT(DISTINCT X.DisplayName) > 1)
) Z ON X.PK = Z.PK
February 20, 2007 at 8:54 am
Quote: "In essence what I want is to know when the filler order id is repeated on a different customer."
SELECT a.FillerOrderID, a.Customer, b.Customer
FROM Orders a
INNER JOIN Orders b
ON a.FillerOrderID = b.FillerOrderID
WHERE a.Customer < b.Customer
Change column and table names as needed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply