cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

How to query for date ranges such as "last week" or "last month" ?

BenAvdicevic
Honored Contributor

Hi,

I am trying to write some queries that return objects modified or created  in specific time frames. For example:

  • yesterday
  • past 7 days
  • past 30 days
  • etc..

Right now, I only know how to query for objects created/modified today  using the "&CurrentDate" parameter.

 

For example, here is a query that returns all diagrams modified today

 

Select Diagram WHERE [Modification Date] >= "&CurrentDate"

 

Now, what I would like is to have a query that returns diagrams modified in "past 7 days" or "past 14" days etc..

 

Is there any special keywords for this in HOPEX, or are any calculations allowed and if so - how?

 

9 Replies

Hello,

You need to have access to report Studio.
So a profile of Hopex Customizer, with a token MTS2 (I am not sure if other tokens give you access to this too)
In the menu Report studio, you can easily create queries implemented through macros, and write the macro itself.

A follow up on Macros. 

How can SaaS customers develop macros?  Which profile/license is needed for that ?  

@PBessodes Thank you!! this works now. I can now enter a date as query parameter.

BenAvdicevic_0-1672930638597.png

 

Hello,

This query works...as soon as you have activated the "Display repository administration properties..." option :

PBessodes_0-1672928969422.png

 

Patrick Bessodes

BenAvdicevic
Honored Contributor

A quick followup @PBessodes @CLAVALLEE 

Do you know if we can use Date as a Request Parameter.

I envision something like this

SELECT Application WHERE [Modification Date] >= &"Date"

BenAvdicevic
Honored Contributor

@PBessodes @CLAVALLEE  Thank you both!
Good to know that macros is the way to go about this.

Hi,

ERQL doesn't offer this kind of capability today. You can only use system variable : &CurrentDate

But you can implement a query by a macro.

Regards

CLAVALLEE
MEGA
MEGA

Hello,

As far as I know, you would have to create query with a macro implementation.
For example, for the implementation : 


Sub FillSelectionCollection(mgobjObject as MegaObject, vntSelectorID as Variant, mgcolCollection as MegaCollection)
  Dim mgRoot, col
  mgRoot = mgobjObject.getroot
  col = mgRoot.getSelection("Select [Diagram] where [Modification Date] > '" & CDate(Now-7) & "'")
  mgcolCollection.insert col
End Sub

Hope this can help.

BenAvdicevic
Honored Contributor

@PBessodes Are you able to offer any help on this question?

I am writing some queries - and would really like to be able to have a way to return results "from last week" or "last month" or something like that.