Skip to Content
Feb 11, 2014 at 12:21 PM

Struggling with SQL for LTVC


Hello all,

I'm trying to output the following data from Business One in a query or queries in order to calculate the Lifetime Value of customers from various sources.

  1. BP Code
  2. BP Name
  3. BP Email
  4. Date of first Sales Order (need to be able to specify between two dates)
  5. Total Value of first Sales Order
  6. UDF from the first Sales Order (a custom field called U_WebReferrer which is populated with the source channel)
  7. Total number of Sale Orders
  8. Total Sales Orders Value (excluding tax) (need to able to specify between two dates)
  9. Total Gross Profit
  10. Total Gross Profit %

Using the data above I want to specify all the customers who, for instance, first placed an order between 20130101 and 20130131 and then give the Totals for points 7, 8, 9, 10 between two other dates, for instance, 20130101 and 20131231.

I hope that makes sense? I am really struggling to output the above data, can anyone assist please?

Many thanks