problem creating query.

Question

Post reply

problem creating query.

winston Smith

SSCoach

Points: 19500
More actions
July 30, 2008 at 8:35 am

#190313

There is a table that contains a row for each time a user is allowed to renew an order for another year:
OrderRenewalID | OrderRenewalRef | OrderID | RenewalStatusCode | RenewalDate
110444ACT01-06-2006
210444INA01-06-2006
320444INA01-06-2007
Ideally each order would only have two of these lines, both in ACT status. Here as you can see for OrderRenewalRef 10, there are two entries, one active, one inactive.
I want to write a script to find only orderRenewalID’s, for OrderRenewalRef’s that have only one entry for them thats inactive.
Note this problem exists on many thousands of orders.
Can you help me out on this please?

Viewing 3 posts - 1 through 2 (of 2 total)

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

winston Smith SSCoach Points: 19500 More actions · Answer 1

please ignore. i was doing all sorts of joins to try to resolve this. just needed a

" select max(orderRenewalID).......having count(orderrenewalRef)<=1"

Apologies for the unnecessary post.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

DROP TABLE #OrderRenewals

CREATE TABLE #OrderRenewals (OrderRenewalID INT, OrderRenewalRef INT, OrderID INT, RenewalStatusCode CHAR(3), RenewalDate DATETIME)

INSERT INTO #OrderRenewals (OrderRenewalID, OrderRenewalRef, OrderID, RenewalStatusCode, RenewalDate)

SELECT 1, 10, 444, 'ACT', '01-06-2006' UNION ALL

SELECT 2, 10, 444, 'INA', '01-06-2006' UNION ALL

SELECT 3, 20, 444, 'INA', '01-06-2007'

SELECT OrderRenewalID, OrderRenewalRef, OrderID, RenewalStatusCode, RenewalDate

FROM #OrderRenewals r

INNER JOIN (

SELECT OrderRenewalRef

FROM #OrderRenewals

GROUP BY OrderRenewalRef

HAVING COUNT(*) = 1

) d ON d.OrderRenewalRef = r.OrderRenewalRef

WHERE r.RenewalStatusCode = 'INA'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden