Retrieve Most Recent Customers And Insert Into Tempdb

0
votes

Platform: SQL Server (SSMS)

I have tables tblorder and tblorderproduct. These are the relevant columns from these tables:

  • Tblorder: CUID (Customer ID), OrOrderID (Order ID), CuName (Customer Name), completedate (Order Date), OrCancellation (1 = Cancelled, 0 = Normal)

    The table is automatically sorted in ascending order by date

  • Tblorderproduct: OpOrderID (Order ID), OpOrderValue (Order Value)

tblorder.ororderid = tblorderprodcut.oporderid

Objectives:

  1. Display most recent 1 million customers (CUID and Name) with all the OrderID they are reponsible for (excluding cancelled orders).

  2. Then insert them into a temp table #Recent1MCus

Example

CUID  OrderID   CUName      completedate
----------------------------------------
3     201       Joe P       2017 06 02
3     202       Joe P       2017 06 25
1     203       Jason A     2017 07 21
4     204       Jason C     2017 08 31
7     205       Nick C      2017 09 03
2     206       Ari C       2017 10 01
3     207       Joe P       2017 10 21
1     208       Jason A     2017 11 05

Let's say I only want most recent 2 customers (for the sake of the example) with all their orders

So the query should return:

CUID  OrderID   CUName      completedate
----------------------------------------
1     208       Jason A     2017 11 05
1     203       Jason A     2017 07 21
3     207       Joe P       2017 10 21
3     202       Joe P       2017 06 25
3     201       Joe P       2017 06 02

So sum it up the query should:

  1. Distinct Top 1000000 for CUID (either Group by or Distinct)

  2. Display all Order ID that is connected with that CUID (if group by CUID, OrOrderID, you can't put the top 1000000 limit on CUID?)

  3. When selecting the customers, I want the most recent 1000000 distinct CUIDs, therefore the completedate in desc order.

I am just completely lost after trying different methods. I appreciate any help!

Thanks!

2
sqlsql-server
I cannot tell what the question it. At one point you say you want 2 customers, another 1000000 customers. You say you want orders, but then distinct CUIDs. Very confusing. - Gordon Linoff
@GordonLinoff My objective is to retrieve the 1M Customers. I used 2 customers as an example to demonstrate my objective in the fake tables I created. - stillthrough
@Gordon - it's 'jwolf' again - this was one of the questions we were both on over the weekend. I just posted an updated solution. I agree it's not entirely clear what he's going for but I did notice something new in his retelling. Hoping this will take care of it. - jwolf
@stillthrough - There is an updated solution for you below. Hope it helps. - jwolf

2 Answers

1
votes

Updated Solution

It looks like there is something new here - having ALL of the orders from the most recent customers not just the more recent orders. For this we are going to have to join to the order table a second time. First we'll find the most recent customers by their orders as we have already done (now the inner query), secondly we'll find all of the orders associated with those customers (in the outer query):

select t1.cuid, t1.cuname, t2.order_id, t2.ordervalue -- Etc.
from
    (select top 1000000 cuid, cuname
        into #Recent1MCus
        from tblorder t1
        group by cuid, cuname
        order by max(ordate) desc
    ) t1
inner join tblorder t2 on t2.cuid = y1.cuid

You could also join to the product table in the outer query if you need associated product data in the outer select list and so on - or group in the outer query to find total ordervalue per cuid etc - but this is the basic structure that I think you want.

I hope this helps.

0
votes

Try This (or see updated solution)

I thought this sounded familiar; here's my answer from 2 days ago:

TOP 1000000 is the way to go, but you're going to need an ORDER BY clause or you will get arbitrary results. In your case, you mentioned that you wanted the most recent ones, so:

ORDER BY OrderDate DESC

Also, you might consider using GROUP BY rather than DISTINCT. I think it looks cleaner and keeps the select list a select list so you have the option to include whatever else you might want (as I took the liberty of doing). Notice that, because of the grouping, the ORDER BY now uses MAX(ordate) since customers can presumably have multiple ordate's and we are interested in the most recent. So:

select top 1000000 cuid, cuname, sum(order_value) as ca_ching, count(distinct(order_id)) as order_count
into #Recent1MCus
from tblorder
group by cuid, cuname
order by max(ordate) desc

I hope this helps.