expired punches report
Our punches are set to expire after 2 years. I'd like to know how many are left unused after that time. I can work out how many punches are sold in a given time period and how many are used, but this isn't quite accurate because punches used within that time period might have been from before. Also, because of the long time period they are valid for, using that method I'd only ever know the results of punches that were purchased more than 2 years ago.
Thanks,
audrey
Comments
There is no standard report that will provide this information. A custom report could be created, though it would require some thought as it's a little tricky to get right. For example, someone could buy 10 punches, use 5, buy 10 more, and then have the first extra 5 expire... so the answer "depends" on the target date of the analysis.
It could be done, but it'd definitely require some SQL trickery and a few hours of development and testing to be sure the answer is correct.
If this is a report you definitely want, Tod @ Edgeworks might be able to help. His website: http://www.todbloxham.com/
Hope this helps
This is a pretty easy report since expiration adjustments can be uniquely identified in the table. I don't normally make custom reports for free, but this one was too easy :) This hasn't been exhaustively tested, but I think it fits the requirement.
Go to View -> Reports -> Custom Report and create a custom report with this SQL. The date range you select will be the dates that the punches were SOLD
--- SQL below -------
SELECT
postdate AS EXPIRED_ON,
concat(lastname, ', ', firstname) AS CUSTOMER,
delta AS PUNCHES
FROM
punches
JOIN customers ON punches.customer_id = customers.customer_id
WHERE
notes LIKE 'Exp. punches on%' and
POSTDATE between '{STARTDATE}' and '{ENDDATE}'
ORDER BY
postdate;
Please sign in to leave a comment.