0

I'm not proficient in SQL Query. I'm hoping someone on here can help me write this query for Saleforce Marketing Cloud, which uses a "limited" SQL Server 2016.

Problem: I'm trying to extract month from Appointment Date and count the Appointment Statuses for those months.
- Count the appointment status with 'pending' in month of May
- Count the appointment status with 'canceled' in month of May
- Count the appointment status with 'pending in month of June
- Count the appointment status with 'canceled' in month of June
- Count the appointment status with 'pending' in month of July
- Count the appointment status with 'canceled' in month of July

Use Case: Target customers based on their recent canceled appointment (status = 'canceled') and no upcoming appointment scheduled (status = 'pending')

Data Extensions DE1 = data extension with customer's appointment data - DE1 has records per row of customer's appointments so I need to group the records by CustomerID or Email DE1

DE2 = new data extension with count enter image description here

Query Here is the query I've worked on so far, but it's not working.

SELECT 
SM.Status,
SM.CustomerID,
SM.Email,
SM.AppointmentDate,
    sum(CASE WHEN SM.Status = 'Pending' THEN 1 ELSE 0 END) AS LM.May_PendingCount,
    sum(CASE WHEN SM.Status = 'Canceled' THEN 1 ELSE 0 END) AS LM.May_CanceledCount
    FROM [DE1] SM
        inner join [DE2] LM
        on SM.Email = LM.Email
    WHERE DATEPART(Month, SM.AppointmentDate) = LM.Campaignable
    GROUP BY SM.CustomerID
  • You could really do with the SQL PIVOT query, but that syntax isn't supported by Query Activities. Do you need to do this manipulation inside SFMC? It'd potentially be easier to dump a file with counts by month and then use a pivot table in something like Excel. – Macca May 7 at 0:43
0

I'd suggest to stage this into several queries in one automation. This may seem not "elegant" or whatever, but it will limit the operations to very simple ones. So (especially) if you don't feel proficient, keep it simple.

I'll make it very sequential with four separate queries in an automation. once you get it to run you can then try and combine operations to have less "moving parts" if necessary.

1) and 2)

Start with splitting into two separate processing data extensions:

SM.Status,
SM.CustomerID,
SM.Email,
SM.AppointmentDate,
SM.Status    
WHERE Status = 'Pending'
/* into Pending_staging*/
SM.Status,
SM.CustomerID,
SM.Email,
SM.AppointmentDate
WHERE Status = 'Cancelled'
/* into Cancelled_staging */

temp result: You have a list of cancelled appointments, and a list of pending appointments

3) SELECT the relevant record in the cancelled table by finding the most recent cancelled appointment using dedupe with ROW_NUMBER function:

SELECT c.Status, c.CustomerID,c.Email,c.AppointmentDate
FROM (
  SELECT Status, CustomerID, Email, AppointmentDate, ROW_NUMBER() OVER(Partition by CustomerID ORDER BY AppointmentDate DESC) AS Row_Number   
  FROM Cancelled_staging) o
WHERE b.Row_Number = 1
/* into mostRecentCancelled */

temp result: you have the most recent cancelled appointments in a data extension "mostRecentCancelled".

4) Lastly, do a "LEFT JOIN WHERE B KEY is NULL" logic as illustrated here in second from top, left column image to find cancelled appointments where the customer is not in the list of "pending" appointments.

https://4.bp.blogspot.com/-_HsHikmChBI/VmQGJjLKgyI/AAAAAAAAEPw/JaLnV0bsbEo/s1600/sql%2Bjoins%2Bguide%2Band%2Bsyntax.jpg

SELECT CustomerId /* and more fields if needed */
FROM mostRecentCancelled mrc
LEFT JOIN pending_staging ps
on mrc.CustomerId = ps.CustomerId
WHERE ps.CustomerId IS NULL
/* into resultDE */

end result: resultDE contains Target customers based on their recent canceled appointment (status = 'canceled') and no upcoming appointment scheduled (status = 'pending')

Hope this helps!

| improve this answer | |
  • Thank you so much for taking the time to explain this to me! It makes more sense when you break it down into simple queries and just piece the queries together with automation. I learned a lot from just this. Thank you, thank you!!! :) – EmailNerd May 8 at 2:13
  • Sure thing, please mark the answer as accepted if it solved your problem ;) – Jonas Lamberty May 8 at 6:39

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.