How to transform date range into multiple rows in Intersystems Cache?
Let's say I start with a date range of '1-5-2019' to '5-25-2019' that occurs on one row. I'd like to ultimately have this show as 5 rows in Crystal Reports as shown below
Result
1-5-2019 1-31-2019
2-1-2019 2-28-2019
3-1-2019 3-31-2019
4-1-2019 4-30-2019
5-1-2019 5-25-2019
I found a result that worked in T-SQL, but I'm not sure how to translate it to Cache SQL. The T-SQL code is
select dateadd(d,N.number,d.begindate) adate, data
from data d
join Numbers N ON number between 0 and datediff(d, begindate, enddate)
This code fetches a row for every day between the begin date and end date. Then I can group it and find the MIN and MAX for each month.
Do you know how to write this in InterSystems Cache? We have a MyAvatar implementation and so I was able to create a multi iteration table after some trouble with numbers 0 to 99.
Thanks for any help!