Rule Logic for missing dates in date range

[former_member293952]

Hi All,

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.

Thanks

Venky

[former_member187605]

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.

[adrian_storen]

My thoughts were similar to Dirk.

Create a calendar table (see DATE Dimension in data warehouse as you can build one with PL/SQL).  Then you could use the lookup function (Exists) on the date column to see if a date exists.  The downside is that the score would be calculated on the calendar table but I have found the performance of the Exists function faster than a custom DI view.  You could also investigate using other lookup functions.

[former_member221211]

Hello Venkata,

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.