Prefix - set effective date using SQL Expresssion

Linda Stelling-61686

Content

Hello - I'm hoping someone can help me with this please.

I have an OTBI analysis that I want to schedule on the first of each month that will show changes in the previous month. One of the 'changes' could be that the record has been ended but because the because the subject area is a Real Time one (Payroll - Element Entries Real Time) the record is no longer current when the report is being run so it will not report the change. 

Therefore, I am wondering if I could include the SQL expression below (to get to the 1st of the previous month) in the prefix to set the effective date to be the 'first of the previous month'. 

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

I hope that makes sense and if someone can help me do this I'd be very grateful. I tried several different ways to add this to the Prefix with no success so I think it might be a syntax issue, or maybe it's just not possible to do this?

Many thanks

Linda

 

Shakher Sharma

I am not sure if I understood your question correctly. I believe you want to pick all records modified since 1st of last month. Is that correct?

You should be able to do the same.

Here is the filter I applied:

"- Header Details"."Journal Creation Date" >= TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Here is the code of analysis if you would like to replicate this test case:

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT
   0 s_0,
   "General Ledger - Journals Real Time"."- Header Details"."Journal Creation Date" s_1,
   COUNT("General Ledger - Journals Real Time"."- Header Details"."Journal Name") s_2
FROM "General Ledger - Journals Real Time"
WHERE
("- Header Details"."Journal Creation Date" >= TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
ORDER BY 1, 2 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY

 

 

Attach is the screenshot of the filter

Rick Brobbel

Or you can simply say:

MONTH("Header Details"."Journal Creation Date") = MONTH(CURRENT_DATE) - 1

Which won't work for January, so in that case you need:

CASE WHEN MONTH(CURRENT_DATE) = 1 THEN MONTH("Header Details"."Journal Creation Date") = 12 AND YEAR("Header Details"."Journal Creation Date") = YEAR(CURRENT_DATE) - 1 ELSE MONTH("Header Details"."Journal Creation Date") = MONTH(CURRENT_DATE) - 1 AND YEAR("Header Details"."Journal Creation Date") = YEAR(CURRENT_DATE) END

Good luck