Is there a cool and simple way to get aggregates over a date range in IDOs?
For example, consider this simple SQL:
Declare @StartDate DateTime = '1/1/2024'
Declare @EndDate DateTime = '3/31/2024'
SELECT cust_num, sum(price) AS Total
FROM co_mst
WHERE order_date between @StartDate and @EndDate
GROUP BY cust_num
If I need this as a dataview (with StartDate and EndDate as input parameters), I can make an IDO Method in a Custom Assembly. First I will use LoadCollection to fetch all data of the orders in the date range (can be thousands of records) and then using LINQ's group by on cust_num, prepare a new DataTable with summary columns (cust_num and Total) and then return it.
Is there a simpler way? For example, we can create a derived IDO fields and can write SQL over there. But I guess we can't have variables (Inputs from Dataview) inside the devied fields.
So, can I achieve this simple thing without going to Custom Assembly?
