ExecuteSQL doesn't return records. You might need to use it to get a list of ID's that you then use as a relationship match field. Or you might be able to size the field large and use tabs as field separators (inspector can specify tab stops) or you might feed the results into a virtual list.
What kind of date and time data do you have to query? Do you have a date field and a time field or do you have a timestamp field? (which combines date and time in one field.)
Below was part of my query the other day that you helped me with. Instead of 48hrs I was looking to find data for 24hrs. Just so happen that the start time is 0900.
"You you need to use
status = ? and priority = ? and time_resolved >= ?
get (currentTimeStamp)- ( 2 * 24 * 3600)
as your third parameter. "
I also found a post that you wrote some time ago where you were speaking TIMESTAMPVAL. I wouldn't know where to start with that unless I use BETWEEN.
But what fields for this info do you have in your table? Since you need a date and time, you might have a date field for the date and a time field for the time or you might use a field of type timestamp that then records the date and the time. This detail then determines how to structure the WHERE clause as you might need to use separate values for date and time or a single value for date and time combined.
I'm using Timestamps
Just throwing out an idea with a completely different approach than what i expect the answer to be.
For the report
Once the sql has been made is it possible to write that information (virtual list) to a 'report table' either directly
o if not r exporting contents to a temp csv and scripting a an import from that csv into the report table.
1 of 1 people found this helpful
The query I am attempting to write covers a period of 0900 the previous day to 0859 the current day, Tuesday – Friday and 0900 Friday – 0859 Monday.
Let ( [ T = Get ( CurrentDate ) ;
dw = DayOfWeek ( T ) ;
StartTS = If ( dw < 3 or dw > 6 ; TimeStamp ( T - dw ; Time ( 09 ; 00 ; 00 ) ) ; TimeStamp ( T - 1 ; Time ( 09 ; 00 ; 00 ) ) );
EndTS = if ( dw < 3 or dw > 6 ; TimeStamp ( T - dw + 2 ; Time ( 08 ; 59 ; 00 )) ; TimeStamp ( T ; Time ( 08 ; 59 ; 00 ) ) )
ExecuteSQL ( "SELECT
rest of query goes here
TimeStampFieldHere >= ? AND
TimeStampFieldHere <= ?"; "" ; "" ; StartTS ; EndTS )
) // Let
I think that will work, but I haven't tested it. The let and if functions calculate times stamps that then are inserted into the query's WHERE clause. I recommend testing the calculations for StartTS and EndTS in the data viewer first before trying to build the full query as they may need "tweaking" to get the right values to insert into the query.
Thank you it works like a charm