Stored procedures are batch files (so to speak) of SQL commands that you can run. Crystal can use them as a datasource just as easily as the tables that you are using.
The solution outlined actually takes into account the multiple swiping. The suppression of sections by mod 2 = 0 is if there are multiples of 2 for swipes ignore them...1 in, 1 out. If the swipes are grouped by date, this should work, unless you have people who regularly are there past midnight.
simple way to see this in action. Start you report as you regularly do (with all the swipes). Create a group by user, and one by date. Create a formula like:
count(({table.userid}, dateGroup) mod 2 = 0
put the formula on the detail line and display the results. What you should see is all swipes ending with an out are true, all the rest are true.
in the date group footer put the info that you want to see ( which should be the swipe in). preview the report again, and you should see the last record of each group in the group footer. If everything is looking good, suppress the group headers and detail lines and the group footer for the userid.
Now your report is just the last line for every person for every day. Copy your formula into the Section Expert/Suppress formula (x-1) button) for the date footer. Click ok to get back to the preview, and you should see just the footer line for the people who have not swiped out on the same day that they swiped in.
You can add refinements for people past midnight, but this should get you started.
IP Logged
