Survey123 Power Automate to Create Report "Where" clause

[KhemAryal]

I am struggling with Power Automate Survey123 create report to get the results only for the previous month. As it can be done easy for the last x days but I want for the last month. 

I tried different SQL syntax in 'Where' clause. I can do for last 30 days or any number of days but not all the months are 30 days. There should be an easy way to do it but I kept failing. any suggestion?

BETWEEN CURRENT_TIMESTAMP - 30 AND CURRENT_TIMESTAMP successfully creates a report with results for the last 30 days. But I need to create report for previous month automatically to create a report every month.

Thank you in advance

[KhemAryal]

@IsmaelChivite I am sure you have the answer for my question above if this can be DONE or NOT? I tried multiple SQL syntax at Where Clause, nothing worked. I appreciate your time. Thank You

KA

[IsmaelChivite]

@KhemAryal  I have been thinking about your question, but I do not have a solution that works so far. I was hoping someone else can chime in. I am guessing that this will involve some string manipulation in Power Automate. I am certain this can be done. I just have not had enough free time to experiment with this. Did you try posting your question in the Power Automate forums at Microsoft? I bet this is something someone else has done before.  This is less of a Survey123 question. More of a Power Automate question.

[VivekAcharya · Accepted solution]

@KhemAryal, I've gotten this to work by calculating the dates in Power Automate and using dynamic expressions to merge them into Filter WHERE clause. Please let me know how this solution works for you.

• In your Flow, add a "Subtract from time" action with Interval: 1 and Time Unit Month and the expression:
  

formatDateTime(utcNow())

  
This step will grab the exact date 1 month ago - used as reference.
Outputs: "body": "2023-06-12T04:30:10.5467055Z"

• Add another step to initialize variable. Name: StartOfPreviousMonth , Type: String , Value expression: 
  

formatDateTime(startOfMonth(body('Subtract_from_time')), 'yyyy-MM-dd')

This step uses the earlier reference and determines first day of that month.
Value is 2023-06-01

• Initialize another variable. Name: StartOfCurrentMonth , Type: String , Value expression: 
  

formatDateTime(startOfMonth(formatDateTime(utcNow())),'yyyy-MM-dd')

This step determines start of the current month.
Value is 2023-07-01

• Next, the Feature WHERE clause is set up to reference previously initialized variables:

Date_and_time_collected_Data BETWEEN '@{variables('StartOfPreviousMonth')}' AND '@{variables('StartOfCurrentMonth')}'​

:warning:Ensure single quotation characters are placed around dates and spaces exist between SQL keywords.
 

• Running the flow succeeds and you can observe how Feature WHERE clause gets formatted using the computed values.
  

I don't think complex date functions from are supported in this Feature WHERE clause syntax - seems quite limited SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation.

[KhemAryal]

@VivekAcharya You Rock. I was able to get the flow success with the solution you provided. And yes, any SQL questions in the future will be tagged on you 🙂

@IsmaelChivite You are right as the solution was more from Power Automate. Thank you