Use Date Criteria to Calculate Sum

[TippyTV2018]

I have a datapage (details) referencing a table called "UserTips2". I am trying to calculate the current month's tips only. I have a field that is named "itemamount" that contains all of the dollar amounts of the tips received for that user. Then I have a field called "transactiondate" that records the time and date the tip was received and it is in the following format: "20:15:03 Feb 02, 2018". My goal is to show the amount of tips received for the current month in a details page. I tried using criteria, but it did not work. I need some help trying to figure this out. Also I would like to know if I should be using a formula to accomplish this task and if so what should the formula look like?

[Vitalikssssss]

Hi TippyTV2018,

I can suggest creating a View based on UserTips2 table in order to filter records based on current month.

You can reference the View in your SQL query in order to aggregate total of itemamount for the user.

The following syntax can be used to reference a view:  _v_viewname

Here is an example of SQL query: 

SELECT Sum(My_table_Sales) FROM _v_Current_month WHERE My_table_Sale_rep = target.[@field:Sales_rep]

 

[NailDyanC]

Hello, just to add to this post.  You might find this link useful for Function Reference for SQL Statements.

https://howto.caspio.com/function-reference/

[ParkLoey]

Hi there! Caspio's YouTube channel also had a livestream with Ned where he discussed more about using SQL. You can watch that here:

 

- LOEY