1

I have a DE with the number of customers, like this:

Entry_Date Customer_Total New_Customers
05/26/2021 5
05/27/2021 10 5
05/28/2021 21 11

I need to calculate the value for new customers daily, which would be the difference of Customer_Total for current day vs previous day. So, for 5/27/2021 it would be 10-5, for 5/28 21-10.

Getting an error

Errors: Old style JOIN (ANSI JOINs) syntax is not allowed.

when using below query:

SELECT
(
(SELECT COUNT(*) FROM x WHERE datediff(day, Entry_Date, getdate()) = 0)) - (SELECT COUNT(*) FROM x WHERE datediff(day, Entry_Date, getdate()) = 1))
) as counter

What is the best way query this in marketing cloud?

5

Assuming you already have a data extension totalling the entries by date, you can use an Outer Apply to get the previous day's count for the calculation:

select
  convert(date, es.entry_date) entry_date
, es.customer_total 
, (es.customer_total - p.customer_total) new_customers
from Entry_Summary es
outer apply (
  select
  es0.customer_total
  from Entry_Summary es0
  where es0.entryDate >= convert(date, es.entry_date-1)
  and es0.entryDate < convert(date, es.entry_date)
) p

The convert() to a date datatype is for stripping the time from the date.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.