SAP Hana Store procedure to find days where no transaction occurred on our system, OPInt Business Situation

Think you have a system where people create transactions.
You want to find the days which people generated no transactions.

Lets assume you want to check last week.

Define a UNION ALL block to manually generate days of last week
Query day differences of transaction and today
Find days which is not in above difference data.

You can use this as detection procedure in OPINt.



SELECT mdays.mday
FROM
(
SELECT 1 mday from dummy UNION ALL
SELECT 2 mday from dummy UNION ALL
SELECT 3 mday from dummy UNION ALL
SELECT 4 mday from dummy UNION ALL
SELECT 5 mday from dummy UNION ALL
SELECT 6 mday from dummy UNION ALL
SELECT 7 mday from dummy
) mdays
where mdays.mday not in (

select
distinct DAYS_BETWEEN (to_date("CREATED_AT" ),now())
from
"MY_SCHEMA"."TRANSACTION_TABLE"
where DAYS_BETWEEN (to_date("CREATED_AT" ),now()) < 10
)