I am trying to find a logic to get a missing dates from with in a date range.
Here is the scenario: We have a table that has all transactions data with the date column. As per the business process everyday some transactions has to happen and updated to that table with transaction details and with the date. Business wants to capture the date if there is no transactions happened on that day.
I tried writing SQL statement but having difficulty to passing parameter to that.
Below is the date range and need to write a rule to capture missing dates between these range and store in failed database.
Appreciate if some one can help me on this ASAP.
You will need a(n extra) Calendar table in your environment. Import its metadata in IS.
In Worksapece Home, select both tables. Select Manage Views > Create View with Join. Use Calendar as the left table, your source as the right Specify a condition on the date columns for a Left Outer join. You can optionally add a condition to limit your date range. Group by Date. And have 2 columns in the output schema, the date and a count.
Then you can simply create a rule on the count column: >0.
Have you tried creating a view on top of the database table.
If you have a specific date range then you create a view on top of the database table and then under the filter tab have the following condition:
"SAP connection".Date_Table.Date_Field >= to_date('2016.03.14','yyyy.mm.dd') AND "SAP connection".Date_Table.Date_Field <= to_date('2016.08.01','yyyy.mm.dd').
This would only show that date range.
Then after that you could create a validation rule that is "SAP CFL".Date_Table.Date_Field = to_date('1900.01.01','yyyy.mm.dd') (I believe that is auto generated blank/null date value) or you conversely select NULL or Blank check from the drop down menu's when you first try to create a validation rule.
After that bind the validation rule to the field in the view and run it under the task.
Let me know if that works for you.