RaqForum 28 No.
482 View  • 
     ...

Get Recent Records

 
SQL(179)

【Question】

I have defined a domain that joins a few tables, and I’d like to limit the result set by applying a pre-filter that will only return recent (last 30 days) records. I am using SQL server, so the SQL syntax would be:

where MyTable.DateTimeField > getdate() - 30

If I add a pre-filter using an absolute date, I see the following in the exported domain XML:

MyTable.DateTimeField> ts’2014-11-11’

And this works fine. How can I change the absolute day to TODAY - 30? I have tried many combinations. I have looked at the semanticlayer.xml, but nothing seems to work. Any help would be greatly appreciated!

 

【Answer】

It’s convenient to achieve what the above SQL query does in SPL. You can pass a day within TODAY – 30 through a parameter in SPL:

A

1

return myDB1.query(“select *   from MyTable where DateTimeField>?”,now()-30)

A1: Find and return records from today to 30 days ago. You can write your own MyTable and DateTimeField.


SQL(179)
 
 •  482 View  •