SQL Editor help - need to find min & max order date for a customer

[rmwillis1973]

Hi all,  I'm happy using SQL editor to select fields & rename etc, but I've never done any SQL coding, so don't know the syntax.

I have a database of 15m records of sales data.  But I only need to pull out the 1st Order & Last Order date for each customer.

The current syntax is here:

Select dbo.dim_data_source.data_source, dbo.dim_Customer.CustomerNumber As [Customer Number], dbo.dim_Moss_Calendar.YYYYMMDD, MOSSDW.dbo.fact_Invoice.ILORD As [Order No.] From MOSSDW.dbo.fact_Invoice Left Join dbo.dim_data_source On dbo.dim_data_source.dim_data_source_key = MOSSDW.dbo.fact_Invoice.dim_data_source_key Left Join dbo.dim_Customer On dbo.dim_Customer.dim_Customer_key = MOSSDW.dbo.fact_Invoice.dim_Customer_key Inner Join dbo.dim_Moss_Calendar On dbo.dim_Moss_Calendar.dim_Moss_Calendar_key = MOSSDW.dbo.fact_Invoice.dim_Moss_Calendar_key Where dbo.dim_data_source.data_source = 'BPCS'

[JordanB · Accepted solution]

HI @rmwillis1973

What database are you querying? Alteryx has a tool category called In-database which allows you to use easy drag and drop tools to build out SQL queries and keep the data in the database. You can then stream this data into memory to use the remainder of the alteryx tools outside of this category.

Check out this video for more info on this.

In your case it looks like it would be summarise using mix and max for the date field. 

Best,

Jordan Barker

Solutions Consultant

[rmwillis1973]

Unfortunately this is a non-starter for me - my IT department needs to give us disk space to run this tool, which isn't possible at the moment.

When i try and use the aggregate option, Alteryx freezes / crashes.

I can bring out all order dates and then use a summarise to give me the min / max dates.  However, i want to be able to do this part still in the SQL editor tool.

Can i use a Pre-SQL statement or amend the statement below?

[Claje]

Hi,

There are definitely some options for this that involve changing your SQL query, however, I'm hoping that I can give you a solution which leverages the Visual Query Builder in Alteryx.

First off, I would recommend saving your workflow so that if something breaks in the query design, you can easily roll back to the current version.

Inside the Visual Query builder where you have your query currently, if you right click in the white space, there should be an option named "add derived table".  If you click this option, you should see a new tab pop up next to the "Main" Tab named "Query1", and you should also see a new box for that table.

If you go into the "Query1" tab, you should be able to build out a Query here which pulls in the MINIMUM of your YYYYMMDD column, as well as the customer Number.  This query is probably very similar to your initial query.

Once that is complete, you can go back to the "Main" tab, and you will see that the Query1 table now has a "Min_YYYYMMDD" column, and a Customer Number column.  From there, you can connect your YYYYMMDD column to the Query1.Min_YYYYMMDD column, and your [Customer Number] column to the Query1.CustomerNumber column.

That should get you the oldest order per customer.  I'd recommend clicking "OK" and running this in Alteryx to see if it meets your expectations.

You can then do one of two things to get the max order:

1) copy your Input Data Tool, and in the second copy, update Min_YYYYMMDD to be Max_YYYYMMDD, then use an Alteryx Union tool to combine both recordsets after the query

2)By right-clicking on the canvas in the Visual Query Builder inside your Input Data tool, on the Main tab, you can choose the option Union->Copy Union Subquery.  This will recreate your entire SQL query and add a Union statement.  You will see a pair of "Q" buttons on the top right of the canvas.  You can then click into the right-most "Q", and change Min_YYYYMMDD to Max_YYYYMMDD here.  That should provide you with the results you are looking for, although it may not be the most readable.

Both of those options should be fairly close in performance, so its really up to you which way you would rather go.

I know thats a lot of information, and unfortunately I couldn't provide any good pictures at the moment.

Please let me know if you get stuck trying this!  I can try and add more clarity to the steps listed.

[rmwillis1973]

I managed to get additional permissions from IT to do this (mainly my misunderstanding of our IT rules).

I was then able to use the InDB tools very easily.  They've saved me quite a lot of time by only pulling the data i really need to use.

Thanks for your help on this topic Jordan