Hi ,
I need to write query to sum up and compare current and last month count.
If the current month count is more than 50% of last month (ex. last_month is 100 and current_month is 155) then display as "Current month GREATER than las month".
If the current month count is lesser than 50% of last month (ex. last_month is 100 and current_month is 149) then display as "Current month LESSER than las month".
If sum of both month count is equal then don't display the records.
If sum of last month is 'zero' and current month is having count -- Don't display the records.
If sum of last month is having count and current month is 'Zero' -- Don't display the records.
Expected output:
source status
-----------------
src1 current month lesser than last month
src3 current month GREATER than last month
DDL and Insert statement:
create table src_table (source varchar2(20), src_count number, create_date date);
-- Display as "current month lesser than last month" as it reached lesser than 50%
Insert into src_table values ('src1', 20, '25-MAR-2018');
Insert into src_table values ('src1', 50, '24-MAR-2018');
Insert into src_table values ('src1', 50, '02-FEB-2018');
Insert into src_table values ('src1', 0, '03-FEB-2018');
-- Do not display as it count is not even reached last month count
Insert into src_table values ('src2', 55, '25-MAR-2018');
Insert into src_table values ('src2', 100, '23-FEB-2018');
-- Display as "current month GREATER than last month" as it reached 50% more than last month
Insert into src_table values ('src3', 30, '25-MAR-2018');
Insert into src_table values ('src3', 50, '24-MAR-2018');
Insert into src_table values ('src3', 50, '02-FEB-2018');
Insert into src_table values ('src3', 0, '03-FEB-2018');
-- Do not display as count is coming for first time
Insert into src_table values ('src4', 148, '25-MAR-2018');
Insert into src_table values ('src4', 0, '01-FEB-2018');
-- Do not display as both are EQUAL
Insert into src_table values ('src4', 10, '25-MAR-2018');
Insert into src_table values ('src4', 10, '04-FEB-2018');
Please let me know if you need any details..