Earliest Record for Same Criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Earliest Record for Same Criteria

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-07-03 : 11:41:41

I'm not sure how to explain this so that it makes sense, but I'll give it a go.

I have a table in which there is an order id, a credit card number, a phone number, and the date the record was inserted(there's other information, but it's not relevant here). This table can have the same credit card or phone number entered multiple times.

What I need to do is get the earliest entry for each credit card number, or phone number, or both.


Create Table tblCustomerOrders (
	OrderID int IDENTITY(1, 1),
	CreditCardNumber varchar(16),
	PhoneNumber varchar(10),
	OrderDate datetime
)

Insert Into tblCustomerOrders Values ('4128111111111111', '8885551212', '6/6/2003') -- OrderID 1
Insert Into tblCustomerOrders Values ('4137111111111111', '8885551212', '6/7/2003') -- OrderID 2
Insert Into tblCustomerOrders Values ('4159111111111111', '8884443535', '6/8/2003') -- OrderID 3
Insert Into tblCustomerOrders Values ('4159111111111111', '8008675309', '6/9/2003') -- OrderID 4
Insert Into tblCustomerOrders Values ('5037111111111111', '7778153295', '6/10/2003') -- OrderID 5
Insert Into tblCustomerOrders Values ('5037111111111111', '8008675309', '6/11/2003') -- OrderID 6
Insert Into tblCustomerOrders Values ('4268111111111111', '8008675309', '6/12/2003') -- OrderID 7

For the above data, I should get back OrderIDs 1, 3, 5, 7

I honestly don't even know where to begin on this one, so any help you guys can give me would be really be appreciated.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-03 : 12:04:38

try:
select creditcardnumber, min(orderID)
from tblCustomerOrders
group by creditcardnumber

Jonathan
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-03 : 12:05:44

Do you have multiple entries for same entity on the same day?
if not

select OrderID
from tblCustomerOrders t1
where not exists (select * from tblCustomerOrders t2 where t1.CreditCardNumber = t2.CreditCardNumber and t2.OrderDate < t2.OrderDate)
union
select OrderID
from tblCustomerOrders t1
where not exists (select * from tblCustomerOrders t2 where t1.PhoneNumber = t2.PhoneNumber and t2.OrderDate < t2.OrderDate)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-03 : 12:16:46

Well, heres a start:


SELECT * 
  FROM tblCustomerOrders o
 WHERE EXISTS (
	  SELECT PhoneNumber, Max(OrderDate) As Max_OrderDate 
	    FROM tblCustomerOrders i
	   WHERE PhoneNumber In ( 
				  SELECT PhoneNumber 
				    FROM tblCustomerOrders 
				GROUP BY phonenumber 
				  HAVING COUNT(*) > 1
				)
	     AND o.PhoneNumber = i.PhoneNumber
	GROUP BY PhoneNumber
	  HAVING o.OrderDate = Max(i.OrderDate)
		)

you'll have to union this and do it again for the card #, and what about the rows that don't have dups, and what about that have both dup'ed?

Brett

8-)

Edited by - x002548 on 07/03/2003 12:19:11

Edited by - x002548 on 07/03/2003 12:21:15

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-07-03 : 12:20:07

Jonathan -
The problem with that, is that only takes credit card numbers into account, and not phone numbers as well, it returns 1,2,3,5,7. It shouldn't return order 2, since the phone number for that order previously exists in order 1.

Nigel -
Yes, there can be multiple entries for a CC# or phone number on the same day, but this shouldn't be an issue, because when inserted, the full date and time is stored. The data I have above is just test data that I made up to illustrate what I needed.

I tried running the query you specified, but it returns all of the orders.

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-07-03 : 12:30:45

Brett -
I changed the Max statements to Min, since I need the earliest date, but even with the Union, I still get 1,3,4,5. 4 shouldn't be there, and I should be able to get 7 as well. I need to get the earliest row that wasn't cancelled out due to a duplicate credit card number, that doesn't have previously matching phone number. It's kind of difficult to explain, I'm really trying my best here though.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-03 : 12:40:21

Why should you get 7? It has a dup in card number and phone. 7 is not less than either?

-- my bad 7 just has a dup phone --

Also you get 4 because it less than the dup phone on record 7

Brett

8-)

Edited by - x002548 on 07/03/2003 12:42:55

Edited by - x002548 on 07/03/2003 12:48:52

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-03 : 12:46:28

quote:

I need to get the earliest row that wasn't cancelled out due to a duplicate credit card number, that doesn't have previously matching phone number

OK I read that requirement as "Same phone, same credit card"

Right?

Why does the Phone matter at all?

An Account is an Account, no?

Brett

8-)

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-07-03 : 12:49:14

This is where the process gets kind of weird. The reason 7 needs to come back is because number 6 will not be returned, since the CC# for 6 matches that of 5. As such, no record returned yet matches either the CC# or phone number for 7, so 7 can be returned. Does that make more sense?

If worse comes to worse, I can leave out that part I described above, and just make multiple passes on the DB. Things will then operate in a more normal manner, and I would only need 1, 3, and 5. 4, 6 and 7, however, should not be returned.

The criteria is there, because the process that is actually dealing with this data, does not just use the CC# for what it is doing.

Edited by - blastrix on 07/03/2003 12:51:07

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-03 : 12:57:44

But then 7's not a dup of anything.

Does that now mean you want records that are not considered dups, or ones that where but are no longer...

Wow I'm starting to even confuse myself...

Even more confusion. 7 was a dup of phone of 6, but because 6 was elimiated because of a dup account from 5, the fact that 7 was a dup of 6, but no longer, makes him now eligible for the result set?

Wow.

Imagine if you will, a logic tree, where there is infinite space and time no longer exists, yes my friends you have traveled to the twilight Yak zone.

Even more and more...How many Turtles down do you want to go?

Brett

8-)

Edited by - x002548 on 07/03/2003 13:17:39

Edited by - x002548 on 07/03/2003 13:18:46

Edited by - x002548 on 07/03/2003 13:20:21

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-07-03 : 13:41:15

Okay, change of plans...sort of. Throw the whole order 7 rule out the window.

Here's the underlying premise to the system. A credit card number *or* phone number may only be approved once in a certain period of time. This query is being used to pull back orders to be charged, but it needs to only pull back the earliest orders that do not have the same credit card number, or same phone number, unless the row is the earliest row in which the data appears.

I will take care of the multiple passes in my application. So for now I just would need rows 1, 3, and 5. After those have processed, and updated the DB, I will subsequent passes on the DB, until I have exhausted the orders to process.

I think that makes things a little simpler, and hopefully clears up what I'm trying to do.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-03 : 13:52:55

And the reason you don't want 4 is because 3 won already in the credit card race...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-03 : 13:56:37

Ok, now that we have THAT all cleared up (

)

The following does what you want:



SELECT * -- Returns 1
  FROM tblCustomerOrders o
 WHERE EXISTS (
	  SELECT PhoneNumber, Min(OrderDate) As Min_OrderDate 
	    FROM tblCustomerOrders i
	   WHERE PhoneNumber In ( 
				  SELECT PhoneNumber 
				    FROM tblCustomerOrders 
				GROUP BY phonenumber 
				  HAVING COUNT(*) > 1
				)
	     AND o.PhoneNumber = i.PhoneNumber
	GROUP BY PhoneNumber
	  HAVING o.OrderDate = Min(i.OrderDate)
		)
   AND CreditCardNumber NOT IN ( 
				  SELECT CreditCardNumber 
				    FROM tblCustomerOrders 
				GROUP BY CreditCardNumber 
				  HAVING COUNT(*) > 1
				)

 UNION
SELECT * -- Returns 3,5
  FROM tblCustomerOrders o
 WHERE EXISTS (
	  SELECT CreditCardNumber, Min(OrderDate) As Min_OrderDate 
	    FROM tblCustomerOrders i
	   WHERE CreditCardNumber In ( 
				  SELECT CreditCardNumber 
				    FROM tblCustomerOrders 
				GROUP BY CreditCardNumber 
				  HAVING COUNT(*) > 1
				)
	     AND o.CreditCardNumber = i.CreditCardNumber
	GROUP BY CreditCardNumber
	  HAVING o.OrderDate = Min(i.OrderDate)
		)

Brett

8-)

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-07-03 : 15:18:35

Okay, I ran this on some real production data, and progress is being made but there still appears to be some problems. I had to change the counts to > 0 instead of > 1, as it was only getting items with multiple entries, no big deal there. The other problem I'm still working on.

As it stands right now, the first query has absolutely no bearing on the resultset, the 14 results I currently get are all returned by the second query.

There are also some duplicate phone numbers. The credit card portion is spot on in that I have all of the right numbers if we didn't care about the phone number.

In the one set of duplicate phone numbers, the credit card numbers are indeed different. For one of the credit card numbers(4927) from the duplicates there are two orders, each with a different phone number. For the second credit card number(5137), there is only that single order, which happens to have been placed between the two orders for 4927. The records for these three look something like this(CC#s changed):


23584	4927111111111111	6043007246	2003-07-01 18:20:37.610
23587	5137111111111111	6043007246	2003-07-01 18:25:04.593
23590	4927111111111111	7909695147	2003-07-01 18:31:46.500

So the only one that should be returned is 23584, since it is the first one with the 4927 number, and phone number.

Edited by - blastrix on 07/03/2003 15:19:47

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-03 : 15:34:31

EDIT: see next post

If I understand you correctly, you are trying to articulate the following rule:

"Return a set of rows where neither the cc # nor the phone # was seen previously".

If so, then you have:


select *
from tblCustomerOrders o
where not exists (
	select 1
	from tblCustomerOrders
	where orderID < o.orderID 
		and ( creditcardnumber = o.creditcardnumber or phonenumber = o.phonenumber ))

Edited by - setbasedisthetruepath on 07/03/2003 15:46:47

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-03 : 15:45:54

Never mind ... I follow you now.


select *
from tblCustomerOrders
where orderID in (
	select orderID
	from tblCustomerOrders o
	where not exists (
		select 1
		from tblCustomerOrders o1
		where o1.orderID < o.orderID and o1.phonenumber = o.phonenumber and not exists (
			select 1
			from tblCustomerOrders o2
			where o2.orderID < o1.orderID and o2.creditcardnumber = o1.creditcardnumber)))
and orderID in (
	select orderID
	from tblCustomerOrders o
	where not exists (
		select 1
		from tblCustomerOrders o1
		where o1.orderID < o.orderID and o1.creditcardnumber = o.creditcardnumber and not exists (
			select 1
			from tblCustomerOrders o2
			where o2.orderID < o1.orderID and o2.phonenumber = o1.phonenumber)))

Jonathan
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-03 : 15:48:22

Haven't read through this but my query s.b. (I copied the table alias)

select OrderID
from tblCustomerOrders t1
where not exists (select * from tblCustomerOrders t2 where t1.CreditCardNumber = t2.CreditCardNumber and t2.OrderDate < t1.OrderDate)
union
select OrderID
from tblCustomerOrders t1
where not exists (select * from tblCustomerOrders t2 where t1.PhoneNumber = t2.PhoneNumber and t2.OrderDate < t1.OrderDate)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-07-03 : 15:53:40

Jonathan -
Your second query is returning the exact same results as the first one, either way the results are correct for both. What is supposed to be the difference between the two?

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-03 : 15:58:00

Not quite-

I took both sets of data you provided (the 7 rows in your first post and the 3 in your later) and unioned them to test.

The correct output is 1,3,5,7,8. The first select returns only 1,3,5,8. It does not understand how to include row 7 because row 6 cannot be used to exclude it. The second select does.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 11:22:41

Did you ever get this to work the way you want it?

I'm still wondering why the phone number has any bearing on a particular account?

I mean what difference does it make if I place an order with visa from my work, and the order something else on discover from work phone?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources