BAQ Help: using DateDiff in where clause


I am trying to recreate the following SQL statement in BAQ. I am calculating the Average Days to Pay (ADP) for each customer by fiscal year, but don’t want to include invoice where the ADP is less than 3. How do I create the highlighted parts of the SQL statement in BAQ

Here is the original SQL statement


Here is the statement in BAQ

Have you tried SubQuery Criteria in BAQ.

1 Like

I agree with @Vinaykamboj .

Go to the subquery criteria tab and select that calculated field and then you’d use the constant expression and you’d put your 3 in there. Try that and see if you get the desired outcome. It would look like this… but you’d use your calculated ADP field there.

The calculated field is different than in the Select, one calculates the Avg of the DateDiff, the other is just the DateDiff. If I create a second calculated field with just the DateDiff, then the query doesn’t work.

I resolved this another way by selecting the required records in a subquery and matched the invoice number using the IN statement. I would like to have done it the other way.