sandy159's post in Median Calculation was marked as the answer
I had the same question on how to calculate median and found out that there is no built-in function in SQL for that
I have found a workaround though, here is my example:
SELECT ( (SELECT MAX(Price) FROM (SELECT TOP 50 PERCENT Price FROM Products_sold ORDER BY Price ) AS BottomHalf) + (SELECT MIN(Price) FROM (SELECT TOP 50 PERCENT Price FROM Products_sold ORDER BY Price DESC) AS TopHalf) ) / 2 AS Median Where 'Price' is the field name, 'Products_sold' - table name. You want to make sure to change these to your field/table names.
Here is a helpful external resource as well: https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server
I hope that it will be helpful for someone!
sandy159's post in Filtration Criteria was marked as the answer
If I understood you correctly, you want to filter the records where Work_Date is blank or Work_Date is not today's date.
Here is a workaround that I found.
You may add a Difference field in the Table and populate it with the Application Task. Then use this field to Filter the needed records in View.
Table (Difference - Number data type):
The Task can be set up to run every day and update the Difference field. Basically, it checks the difference in days. If it is today- then difference will be equal to 0.
In the View you may filter all the records where Difference field is not equal to 0.
Hope this helps!
sandy159's post in Counting records submitted today was marked as the answer
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.