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.



  • 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

    Comment actions Permalink
  • 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 -------

    postdate AS EXPIRED_ON,
    concat(lastname, ', ', firstname) AS CUSTOMER,
    delta AS PUNCHES
    JOIN customers ON punches.customer_id = customers.customer_id
    notes LIKE 'Exp. punches on%' and
    POSTDATE between '{STARTDATE}' and '{ENDDATE}'

    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post