Salesforce Marketing Cloud Query - Count by Date and Appointment Status, Group By CustomerID

Question

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

DE2 = new data extension with count

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

Answer 1

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!