Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

ridhirao22 (Programmer)
27 Feb 12 15:18
Using Oracle 11g

We have Orders and Customers table. I need to identify the order where the customers are first time buyers or repeated buyers. Could anyone please suggest how I can do this in SQL?

TIA,
RR

 
ggriffit (Programmer)
27 Feb 12 16:00
how are your tables structured ? you could look for duplicated addresses with the same name or similar depending on the structure and how detailed you want to go. Personally to remove issues like typos you could use the contacts email address as the unique username type thing.

Greg Griffiths
Livelink Certified Developer & ECM Global Star  Champion 2005 & 2006
http://www.greggriffiths.org/livelink/

ridhirao22 (Programmer)
27 Feb 12 16:18
Thank you for quickly responding, Greg.

Table structure for
Orders

Order_id
Order_date
Order amt
Cust_id


Customers

Cust_id  
Fname
Lname
Address1
Address2
Address3
City
State
Country
Phone
Email
 
LKBrwnDBA (MIS)
27 Feb 12 16:48

Perhaps all you need to do is count the orders for each customer, and if more than one then customer is repeat buyer else it's first time buyer.

Show us what you have coded.
3eyes
 

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 

SantaMufasa (TechnicalUser)
27 Feb 12 17:13
Or, as an alternative...

CODE

break on cust_id
select c.cust_id
      ,c.name
      ,o.order_id
      ,o.order_date
      ,decode(o.order_date
             ,f.min_date,'First-time Customer'
             ,'Returning  Customer') Customer_Status
  from orders o
      ,customers c
      ,(select c2.cust_id,min(order_date) min_date
          from orders o2
              ,customers c2
         where o2.cust_id = c2.cust_id
         group by c2.cust_id) F
 where o.cust_id = c.cust_id
   and o.cust_id = f.cust_id
 order by cust_id,order_date
/

 CUST_ID NAME                         ORDER_ID ORDER_DATE      CUSTOMER_STATUS
-------- -------------------------- ---------- --------------- -------------------
     201 Unisports                          97 28-AUG-92       First-time Customer
     202 OJ Atheletics                      98 31-AUG-92       First-time Customer
     203 Delhi Sports                       99 31-AUG-92       First-time Customer
     204 Womansport                        100 31-AUG-92       First-time Customer
         Womansport                        111 09-SEP-92       Returning  Customer
     205 Kam's Sporting Goods              101 31-AUG-92       First-time Customer
     206 Sportique                         102 01-SEP-92       First-time Customer
     208 Muench Sports                     103 02-SEP-92       First-time Customer
         Muench Sports                     104 03-SEP-92       Returning  Customer
     209 Beisbol Si!                       105 04-SEP-92       First-time Customer
     210 Futbol Sonora                     112 31-AUG-92       First-time Customer
         Futbol Sonora                     106 07-SEP-92       Returning  Customer
     211 Kuhn's Sports                     107 07-SEP-92       First-time Customer
     212 Hamada Sport                      108 07-SEP-92       First-time Customer
     213 Big John's Sports Emporium        109 08-SEP-92       First-time Customer
     214 Ojibway Retail                    110 09-SEP-92       First-time Customer

16 rows selected.

santaMufasa
(aka Dave of Sandy, Utah, USA)
"People may forget what you say, but they will never forget how you made them feel."

Helpful Member!  SantaMufasa (TechnicalUser)
27 Feb 12 17:24
Or, as another, simplified alternative (using Oracle Analytics funtion, RANK):

CODE

break on cust_id
select c.cust_id
      ,c.name
      ,o.order_id
      ,o.order_date
      ,decode(rank() over (partition by c.cust_id
                               order by o.order_date)
             ,1,'First-time Customer'
             ,'Returning  Customer') Customer_Status
  from orders o
      ,customers c
 where o.cust_id = c.cust_id
 order by cust_id,order_date
/

CUST_ID NAME                         ORDER_ID ORDER_DATE      CUSTOMER_STATUS
------- -------------------------- ---------- --------------- -------------------
    201 Unisports                          97 28-AUG-92       First-time Customer
    202 OJ Atheletics                      98 31-AUG-92       First-time Customer
    203 Delhi Sports                       99 31-AUG-92       First-time Customer
    204 Womansport                        100 31-AUG-92       First-time Customer
        Womansport                        111 09-SEP-92       Returning  Customer
    205 Kam's Sporting Goods              101 31-AUG-92       First-time Customer
    206 Sportique                         102 01-SEP-92       First-time Customer
    208 Muench Sports                     103 02-SEP-92       First-time Customer
        Muench Sports                     104 03-SEP-92       Returning  Customer
    209 Beisbol Si!                       105 04-SEP-92       First-time Customer
    210 Futbol Sonora                     112 31-AUG-92       First-time Customer
        Futbol Sonora                     106 07-SEP-92       Returning  Customer
    211 Kuhn's Sports                     107 07-SEP-92       First-time Customer
    212 Hamada Sport                      108 07-SEP-92       First-time Customer
    213 Big John's Sports Emporium        109 08-SEP-92       First-time Customer
    214 Ojibway Retail                    110 09-SEP-92       First-time Customer

16 rows selected.

santaMufasa
(aka Dave of Sandy, Utah, USA)
"People may forget what you say, but they will never forget how you made them feel."

ridhirao22 (Programmer)
28 Feb 12 10:36
Thank you and love you for your idea's Dave!

Stars for you

Thanks again,
RR

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close