Written by

Question Drew Holloway · Feb 28, 2020

How to transform date range into multiple rows in Intersystems Cache?

#Caché #SQL

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!

Discussion (3)0

Add reply

Comments

Drew Holloway · Feb 28, 2020

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.

0 0

Vitaliy Serdtsev · Mar 3, 2020

Defining and Using Stored Procedures

Source code

Class dc.test [ Abstract ]
{

Query daterange(
  d1 As %String,
  d2 As %String) As %Query(ROWSPEC = "dBegin:%String,dEnd:%String") [ SqlName = daterange, SqlProc ]
{
}

ClassMethod daterangeExecute(
  ByRef qHandle As %Binary,
  d1 As %String,
  d2 As %String) As %Status
{
  s qHandle("d1")=$system.SQL.TODATE(d1,"MM-DD-YYYY"),
    qHandle("d2")=$system.SQL.TODATE(d2,"MM-DD-YYYY")
  q $$$OK
}

ClassMethod daterangeFetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = daterangeExecute ]
{
  s d2=qHandle("d2"),
    dBegin=qHandle("d1"),
    dEnd=$system.SQL.LASTDAY(dBegin)
  
  s:dEnd>d2 dEnd=d2
  
  i dBegin>d2 {
    s AtEnd=1
  } else {
    s Row=$lb($system.SQL.TOCHAR(dBegin,"MM-DD-YYYY"),$system.SQL.TOCHAR(dEnd,"MM-DD-YYYY")),
      qHandle("d1")=dEnd+1
  }

  q $$$OK
}

ClassMethod daterangeClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = daterangeExecute ]
{
  q $$$OK
}

}

Result:
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

0 0

Drew Holloway Mar 3, 2020 to Vitaliy Serdtsev

Thanks for your reply. I'm just able to use SQL code and tables for this. I'm not sure if that was clear, but I don't have a way to insert or work with code like you had suggested.

Thanks though!

0 0