BusinessObjects Board

@prompt date function with lookback - Freehand SQL

general discussion
#1

I’m using freehand SQL in my BO report and am needing to come up with a way to have the ‘Start time’ that the end user selects look back an additional 7 days. For example, if September 8, 2023 is selected have the date actually start at September 1, 2023.

Is this possible?

Much appreciated for looking.
Paul

SELECT DISTINCT
 tc2.tracking_id,
 tc2.tracking_checkin_id,
 tc2.checkin_dt_tm,
 DENSE_RANK() OVER (PARTITION BY tc2.tracking_id, tc2.tracking_group_cd ORDER BY tc2.tracking_checkin_id ASC) keep_date
FROM
 tracking_checkin tc2
WHERE
 tc2.tracking_group_cd = 2560254871 and
 cast(from_tz(cast(tc2.checkin_dt_tm AS TIMESTAMP),'GMT') at time zone 'US/Eastern' as date) BETWEEN @prompt('Start time:','D',,mono,Free,Persistent,,User:0) AND @prompt('End time:','D',,mono,Free,Persistent,,User:1)
#2

Sure. I am going to assume your database is SQL Server. If not, the syntax may need to be adjusted.

All you need to do is wrap your Start time prompt with a DATEADD function.

SELECT DISTINCT
 tc2.tracking_id,
 tc2.tracking_checkin_id,
 tc2.checkin_dt_tm,
 DENSE_RANK() OVER (PARTITION BY tc2.tracking_id, tc2.tracking_group_cd ORDER BY tc2.tracking_checkin_id ASC) keep_date
FROM
 tracking_checkin tc2
WHERE
 tc2.tracking_group_cd = 2560254871 and
 cast(from_tz(cast(tc2.checkin_dt_tm AS TIMESTAMP),'GMT') at time zone 'US/Eastern' as date) 
 BETWEEN DATEADD(DAY, -7, @prompt('Start time:','D',,mono,Free,Persistent,,User:0)) AND @prompt('End time:','D',,mono,Free,Persistent,,User:1)
#3

Thanks for the suggestion. I’m using Oracle SQL so DATEADD will not work.

#4

But you can get this approach to work if you experiment. Try wrapping your entire first prompt in a TO_DATE function and then subtract 7 like this…

TO_DATE(@prompt('Start time:','D',,mono,Free,Persistent,,User:0)) - 7

#5

nscheaffer, the TO_DATE function worked great. Thanks so much for the solution. I really appreciate it.

1 Like