When i looked up if it's possible to variables in functions the answer was ok but what about field filters?
when the code runs the filed filter part in TIMESTAMPDIFF gets translated into something like " date(tbl_therapist_timeslot
.fldDate
< date '2024-09-01')"
that's why I used date() to get only the date from it but now I'm facing this error "Unknown column 'tbl_therapist_timeslot.fldDate' in 'field list'"
I've been trying to make this thing work for a while now but every time I stop an error something else comes up. It feels like I met a brick wall so I'd appreciate any help
My query:
SELECT
source.fkClientID,
source.FirstSession,
source.LastSession,
CASE
WHEN (
TIMESTAMPDIFF(
month,
CAST(source.LastSession AS date),
CAST(source.FirstSession AS date)
) = 0
)
AND (
TIMESTAMPDIFF(
month,
CAST(source.LastSession AS date),
date({{month}})
) = 0
) THEN 'New'
ELSE 'Returning'
END AS `category `
FROM
(
SELECT
tbl_client_service.fkClientID,
MIN(
tbl_therapist_timeslot.fldDate
) AS FirstSession,
MAX(
tbl_therapist_timeslot.fldDate
) AS LastSession
FROM
tbl_invoice
LEFT JOIN tbl_client_service ON tbl_invoice.fkClientServiceID = tbl_client_service.pkClientServiceID
LEFT JOIN tbl_client_service_timeslot AS tbl_client_service_timeslot ON tbl_client_service.pkClientServiceID = tbl_client_service_timeslot.pfClientServiceID
LEFT JOIN tbl_therapist_timeslot ON tbl_client_service_timeslot.fkTimeslotID = tbl_therapist_timeslot.pkTimeslotID
WHERE
tbl_invoice.fldStatus = 'saved'
AND {{month}}
GROUP BY
tbl_client_service.fkClientID
ORDER BY
tbl_client_service.fkClientID ASC
) AS source