How to fetch data for last month (from 1 to 30)
Application Type
Mobile

Hello ,

I have created a table "orders" which have "OrderTime" attribute. I want to have count of all orders for last month (from date 1 to 30).

How can I do that. 

Any help would be appreciated.

Hello Darshan,

As per Mayank said, its the right one
other else

As you said for 1to 30 date apply this filter

Order.Orderdate >= DateToDateTime(NewDate(Year(CurrDate()),Month(CurrDate())-1,1)) and Order.Orderdate <= DateToDateTime(NewDate(Year(CurrDate()),Month(CurrDate())-1,30))

Hope this will helps you 

Thanks and Regards,
Akshay Deshpande

Hi @Darshan Saini 

Can you share the oml you are working and I can help you with preparing a solution.

Thanks

Shubham

Hi Darshan,

apply the below condition in the filter clause of your aggregate to retrieve the last month all orders

 Month(orders.OrderTime) = Month(AddMonths(CurrDate(),-1))

then apply the aggregation function Count on OrderId.

Hi @Darshan Saini 

Please check oml file:

and logic is:

Thanks

Mansur

MonthOrder.oap

Hello Darshan,

As per Mayank said, its the right one
other else

As you said for 1to 30 date apply this filter

Order.Orderdate >= DateToDateTime(NewDate(Year(CurrDate()),Month(CurrDate())-1,1)) and Order.Orderdate <= DateToDateTime(NewDate(Year(CurrDate()),Month(CurrDate())-1,30))

Hope this will helps you 

Thanks and Regards,
Akshay Deshpande

Thank you @Shubham Tiwari @Mayank Dharmpurikar  @Md Mansur @Akshay Deshpande šŸš€ for your efforts. It helped.

thank you again


Hi @Darshan Saini ,

What is your exact requirement, count total for previous month ?  If so, why say 1 - 30 in your question, not every month has 30 days.  Anyway, none of the above solutions will work.

The solution of @Mayank Dharmpurikar and @Md Mansur will be wrong all the time once you have data in your database covering more than a year, for example if in may, it will count all data in april, of all years.  It will also not work in janurary, as it will then be trying to find records for month 0.

The solution of @Akshay Deshpande šŸš€ will work more often, but will still be wrong some of the time.  As the other, it will fail in january as it will try to compose a newdate with a month value of 0.  It will fail in march as it is trying to compose the date of february 30th.  These failures will probably be throwing exceptions.  It will also return incorrect counts if there are records to be counted on the last day of the month, as it is using DateToDateTime on the last day, this has a time portion of 00:00, not 23:59.

See attached oml for a solution.  When dealing with datetimes in a reactive app, you get the added bonus of having to deal with difference of timezone between end user and server.  See difference between the almost correct and the correct solution in my oml.  If you define the start and end of month in end user perspective, you would have to calculate boundery datetimes of your query on the client, or make allowences for the difference in timezone in your server logic.

Dorine

MonthOrderRevised.oml

Thank you, Dorine, for clearly presenting the entire topic and pointing out the flaws in the previous methods. I believe it is critical to receive such comments in order to arrive at the most accurate and best feasible solution to the problem, which will benefit everyone in the community.

Thanks to you again.

Good contribution Dorine for a functional solution. 

Regards

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.