· Feb 28, 2020

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


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!

Discussion (3)0
Log in or sign up to continue

I believe I found the answer:

SELECT DATEADD("D", "Number", '2017-01-01') FROM Numbers
  WHERE "Number" BETWEEN 0 AND DATEDIFF("D", '2017-01-01', '2017-04-05')

Now I just need to expand my Numbers table.  I'm wondering if anyone has a view for this.  I'd like a numbers view between 0 and 10000.  I think that should be sufficient.  Feel free to answer here, but I'll start a new topic.

Defining and Using Stored Procedures

Source code
select * from dc.daterange('1-5-2019','5-25-2019')
dBegin dEnd
01-05-2019 01-31-2019
02-01-2019 02-28-2019
03-01-2019 03-31-2019
04-01-2019 04-30-2019
05-01-2019 05-25-2019