Counting records submitted today

[SaraK]

Hello -- I want to be able to show our patrons how many curbside requests were submitted today as of that moment, and I have been unable to do so. I had no problem getting a running grand total; you will see two different numbers (today and a grand total) in my example images. I was copying my grand total DataPage to try to tweak it for a 'today' total, so that is why they are both there. I intend them to be two separate DataPages, so you can just ignore the fact that you see me grabbing a grand total despite trying use filtering criteria that should (but does not) exclude the grand total from being accurate.

I have looked extensively for an answer online in general, as well as in these forums, so I hope I didn't miss an answer already sitting out there. 

I began by setting my initial filter criteria to require the timestamp to be equal to today only so far as the date (I thought this would ignore the time portion of the timestamp). I assumed that when I chose to display the results it would only count records as defined by that filter; however, it doesn't seem to be using the criteria I set in the way I expected. In one instance I just asked it to count the number of RequestIDs, b/c every request is given one, and I thought the criteria filter would limit it to just today. It did not, and my total for the day equaled my grand total (z_criteriaNotEnoughCountToday.png).

 

 

I then tried two different things asking it to once again look at the timestamp and try to compare it to today. In each case it returned ZERO b/c, I am assuming, even though I asked it to only consider the date in both the criteria and in what I wrote, it was comparing down to the second with the time, so nothing, of course, matched for 'today' and I got zero results (z_codeNotWorkingCountToday.png).

SELECT COUNT([@field:Request_ID]) FROM curbsideEntries WHERE [@field:Timestamp] = CURRENT_TIMESTAMP

OR

SELECT COUNT([@field:Request_ID]) FROM curbsideEntries WHERE [@field:Timestamp] = CAST( GETDATE() AS Date )

The code I was writing in my second attachment was all from Googling around -- this is new to me -- so I expect I'm not doing/understanding it right and was hoping this might be a super-easy question for someone out there. Thanks so much. I hope I provided all the necessary info. 

[sandy159]

Hello @SaraK,

Thank you for the detailed explanation. You have made a big research on this as well and were so close. 

Please try to use the following formula in the Calculated field to get the number of IDs that were Submitted Today:

SELECT COUNT([@field:Request_ID]) FROM curbsideEntries WHERE DateDiff(day, [@field:Timestamp], SysDateTime())=0

Hope this helps! 

Please let me know if it works for your case.

Regards

[SaraK]

Yes, thank you so much @sandy159 -- that worked. I really, really appreciate you taking the time to help me with this. Have a nice day