Need help fixing data sent to me

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)

Need help fixing data sent to me

Author

Topic

OSUJeff
Starting Member

7 Posts

Posted - 2002-03-07 : 12:47:05

Help!

I have a really tough problem that I need help with. Here is a quick rundown, followed by the specifics.

I work for an e-commerce company. We contracted another company to take some orders for us. After a few days, they provided me with a SQL database containing the orders. Unfortunatley, they designed their database incorrectly, and now I have no way of grouping all the items that a customer ordered into one single order.

Here is a simplified layout of the database they sent:

 
DATABASE
---------------------------------------
TABLE Contacts
 Fields:
  Contact_ID (not a key, but is unique)
  Name
  BillToAddress1
  BillToAddress2
  BillToCity
  BillToState
  BillToZip
  ShipToAddress1
  ShipToAddress2
  ShipToCity
  ShipToState
  ShipToZip
  Credit_Card_Number

TABLE Orders
 Fields:
  Order_ID (not a key, but is unique)
  Contact_ID (not a key, but is unique)
  UPC
  Quantity
  Total
----------------------------------------

This is an odd database design. The Orders table should really be split into an OrderHeader and OrderDetail table, but I digress. I'll give you an example of a few rows and you'll see what my problem is:


ORDERS
Order_ID | Contact_ID |  UPC  | Quantity | Total
   1           1         123       2       $19.99
   2           2         234       5       $12.00
   3           3         123       2       $19.99
   4           4         567       1       $69.99
   5           5         123       1       $49.99

CONTACTS
Contact_ID |     Name     | BTAddress1       |  BTState
   1         Joe Tester     12 Nowhere St.      NY
   2         Joe Tester     12 Nowhere St.      NY
   3         George Bush    1 Penn Dr.          TX
   4         George Bush    1 Penn Dr.          TX
   5         Bill Clinton   5 Main St.          AR

Here's the problem. The contacts, rather than being unique, are duplicated - 1 for each order detail instead of 1 for each order header. So Order_ID 1, Contact_ID 1 and Order_ID 2, Contact_ID 2 should (probably) be on the same order. The way they are in the DB, there's no way to link these two details together.

Some of you will point out that the way the data exists, there is NO way to group the details into one order. But we have decided that if the Name, ShipToAddress, and Credit_Card_Number fields are the same, it's safe to assume that those items belong in the same order.

Can anyone come up with some help (general at first, then some SQL specifics if possible) to group the details into one order? I'm up for any suggestions.

Thanks,
- Jeff Coon
Dallas, TX

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-07 : 14:26:00

Jeff,

What type of information do you want from this? From what I gather, you want to provide a Name, ShipToAddress, and Credit_Card_Number and receive back the UPC, Quantity and Total for that particular order.

If this is correct, I would create the following procedure:

CREATE PROCEDURE get_customer_order
@name varchar(50),
@ShipToAddress varchar(200),
@Credit_Card_Number int???

Select a.UPC, a.Quantity, a.Total
FROM Orders as a INNER JOIN Contacts b on a.Contact_ID = b.Contact_ID
Where b.Name = @Name
AND ShipToAddress = @ShipToAddress
AND Credit_Card_Number = @Credit_Card_Number

If what you want is a grouping of all orders, then the following:

CREATE PROCEDURE get_customer_order

Select b.Name, b.ShipToAddress, b.Credit_Card_Number,
a.UPC, a.Quantity, a.Total
FROM Orders as a INNER JOIN Contacts b on a.Contact_ID = b.Contact_ID

If I am totally off-base and not understanding the requirements, please let me know. If you are trying to take the data in the format give and generate better tables I can look into this as well.

Hope you don't mind suggestions from an OKIE.

Jeremy

OSUJeff
Starting Member

7 Posts

Posted - 2002-03-07 : 15:31:07

quote:
Hope you don't mind suggestions from an OKIE.

Not at all! I grew up there. (Edmond, then school at Oklahoma State - Go Pokes!) I moved to Dallas about 4 years ago.

quote:
What type of information do you want from this? From what I gather, you want to provide a Name, ShipToAddress, and Credit_Card_Number and receive back the UPC, Quantity and Total for that particular order.

I thought all the info I was giving might be a bit confusing. The problem Jeremy, is this:

For a single order with mutiple items on it, the data should have multiple Order records for a single contact_id. If that were the case, then your 2nd stored procedure would be what I needed. However, each Order record actually points to a different contact_id.

For example, if I ordered 2 products, they would have written this to the DB:


ORDERS
Order_ID | Contact_ID |  UPC  | Quantity | Total
   1           1         123       2       $19.99
   2           2         234       5       $12.00

CONTACTS
Contact_ID |     Name     | BTAddress1       |  BTState
   1         Jeff Coon      12 Nowhere St.      TX
   2         Jeff Coon      12 Nowhere St.      TX

See what they've done? The two order records they've written should reference the same contact_id to show that it is a single order. They wrote the contact information twice.

So what I need is some way to take all the contact records that have equal Names, Addresses, and Credit Card Numbers, and find out what order_ids belong to them.

In the example above, I need to get rid of contact_id #2 (the duplicate Jeff Coon), and update order_id #2 to point to contact_id #1 like this:


ORDERS
Order_ID | Contact_ID |  UPC  | Quantity | Total
   1           1         123       2       $19.99
   2           1         234       5       $12.00

CONTACTS
Contact_ID |     Name     | BTAddress1       |  BTState
   1         Jeff Coon      12 Nowhere St.      TX

Any suggestions? I can't think of an easy way.

Thanks,
- Jeff Coon
Dallas, TX

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-07 : 15:57:22

Jeff,

I won't hold the OSU thing against you either.

I am going to give this a shot, but please bop the contractor who did this over the head.

SELECT aa.Order_ID, bb.Contact_ID, aa.UPC, aa.Quantity, aa.Total
FROM (Select a.Order_ID, a.Name, a.UPC, a.Quantity, a.Total
FROM Orders a, Contacts b
Where a.Contact_ID = b.Contact_ID) aa,
(Select Name, ShiptoAddress1, Credit_Card_Number, Min(Contact_ID)
FROM Contacts
GROUP BY Name, ShiptoAddress1, Credit_Card_Number) bb
WHERE aa.Name = bb.Name
AND aa.ShiptoAddress1 = bb.ShiptoAddress1
AND aa.Credit_Card_Number = bb.Credit_Card_Number

I haven't tested this and my guess is that somebody else in this forum will have a better way to do this. If this is going to be an ongoing feed from the contractors, and they refuse to fix their tables, I would add additional steps to create entirely new tables in the right format in a new stored procedure.

Let me know if this works, or if a dense Sooner still misses the boat.

Jeremy

OSUJeff
Starting Member

7 Posts

Posted - 2002-03-07 : 17:04:36

Props to the Sooners today, Jeremy. Your query seems to work beautifully with one minor change. (adding ShipToAddress1 and Credit_Card_Number to the first sub-select.


SELECT aa.Order_ID, bb.Contact_ID, aa.UPC, aa.Quantity, aa.Total
  FROM (SELECT a.Order_ID, a.Name, a.UPC, a.Quantity, a.Total, a.ShipToAddress1, a.Credit_Card_Number
        FROM Orders a, Contacts b
        WHERE a.Contact_ID = b.Contact_ID) aa,

       (WHERE Name, ShiptoAddress1, Credit_Card_Number, Min(Contact_ID)
        FROM Contacts
        GROUP BY Name, ShiptoAddress1, Credit_Card_Number) bb

WHERE aa.Name = bb.Name
AND aa.ShiptoAddress1 = bb.ShiptoAddress1
AND aa.Credit_Card_Number = bb.Credit_Card_Number

I've only spot-checked the orders so far, but everything is looking good. I'll have to further scrutinize to make absolutely certain, since customers' credit cards are in the mix. But from your query, I don't find any holes.

Nice work - you've saved me a lot of time. I couldn't find a way to do it with pure SQL - I was going to have to use cursors. I guess it's true what they say: Cursors are for people who don't know SQL.

Thanks for your help. Good luck in the B12 tourney this year. Maybe we'll see a Bedlam rematch in the finals. :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources