What it does
Walks backward through the shop schedule to find the previous open business day, then looks for earned awards in
reward_card_transaction on that date.service_package_id is null excludes purchased packages. ro_number != '' ensures the award is tied to a real repair order. marketing_ok must be true on the customer's location record.
CTEs
shopSchedule
Open/closed by day of week
From config_client_location_hours
dateSteps
Recursive backward date walk
Counts only open days stepping back from today
collectDate
Resolved target date
The open business day nDays back
rewardsBalance
Current balance per customer
credits − debits, familyID scoped, deleted_at excluded
mainQuery
Candidate selection with dedup
Anchors on reward_card_transaction. having matches max(created_at) to collectDate.
Output fields
candidateID
companyID
company
"Name"
"FirstName"
last_name
"FullName"
"Email"
"CustPhone"
"RewardsCard"
"RewardsBalance"
"OilChangeEarnedDate"
"CusType"
"CusGrade"