0

The Select below summarizes the customer totals, but I need the customers first buy code to show where they first came from.

Select is:

SELECT h.buycode, Min(h.sdate) AS firstBuy, h.i_id,a.eid,count(*) AS orders,Sum(h.i_total) AS revenue,Max(h.sdate) AS LastBuy, a.eid, a.c_id 
FROM mk_adr a
     INNER JOIN oe_hdr h
         ON  h.c_id = a.c_id
WHERE h.sdate is not null
GROUP BY a.eid

eid is the enterprise ID that ties all of the c_id's(customer_id) together. A customer can have multiple c_id's, but only 1 eid. Eid is not part of the table oe_hdr.

I've tried using a sub-select with a left join and min(sdate) on oe_hdr, buycode, but it mostly returns null values for buycode.

user2705403
  • 1
  • 1
  • what is the buycode? how is it stored and what determines the first from middle from last? can you please read [MY POST](http://meta.stackoverflow.com/a/271056/2733506) on how to ask a question.. it has some good recommendations that you could follow to get a better and faster answer – John Ruddell Sep 15 '14 at 16:14
  • You should be able to use a subquery that uses one of the techniques here: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group – Barmar Sep 15 '14 at 16:17
  • @Barmar i wrote [**this post**](http://meta.stackoverflow.com/a/271056/2733506) a little while ago, trying to make it useful and helpful for us to use for all server side SQL queries. if you have a minute could you look at it and let me know if I'm missing anything or should edit the answer? I would appreciate it! – John Ruddell Sep 15 '14 at 16:29
  • @JohnRuddell It's too long, no one who needs to read it will bother. – Barmar Sep 15 '14 at 16:32
  • @Barmar ok thanks I'll try to shorten it up some :) – John Ruddell Sep 15 '14 at 16:35
  • Thanks Barmar, added 'order by h.sdate' to end of it and the buycode which is a vchar(12) is being returned from the first order. Was able to verify by adding the entered date field along with min(entered date field). – user2705403 Sep 15 '14 at 17:13

1 Answers1

0

This may not be the best way, but you can create a table that only holds the first purchase for the user. it places a foreign key to the user, and to the first item that they bought. Then you can just select from that table.

LOGIC: IF -the user has no buys in the table- THEN

 -Place the information in the first buy table-
Gavin Perkins
  • 685
  • 1
  • 9
  • 28