Good Morning Everyone,
Hope everyone is doing well. I need some help with a query where I am not getting the expected results. I am trying to write a query to fetch a result PASS/FAIL based on the record count WHERE INSERTED_DATE BETWEEN first day of the currrent month and last day of the current month. I have a situation where few files will be loaded every month in a table and I want to check record count for each file and if the record count is more than 2 for each file, then display PASS and even if one file is not loaded or have less than 2 records per file then display FAIL.
Create table and test data below.
create table ECS_MONTHLY_LOAD (
rec_id varchar2(50 byte),
file_name varchar2(25 byte),
inserted_date date);
INSERT INTO ECS_MONTHLY_LOAD (REC_ID,FILE_NAME,INSERTED_DATE) VALUES ('112233445566','86_ECS_MON_20221024.TXT','24-OCT-22');
INSERT INTO ECS_MONTHLY_LOAD (REC_ID,FILE_NAME,INSERTED_DATE) VALUES ('123456789','86_ECS_MON_20221024.TXT','24-OCT-22');
INSERT INTO ECS_MONTHLY_LOAD (REC_ID,FILE_NAME,INSERTED_DATE) VALUES ('987654321','86_ECS_MON_20221024.TXT','24-OCT-22');
INSERT INTO ECS_MONTHLY_LOAD (REC_ID,FILE_NAME,INSERTED_DATE) VALUES ('ABCD-EFGH-1234','76_ECS_MON_20221024.TXT','24-OCT-22');
INSERT INTO ECS_MONTHLY_LOAD (REC_ID,FILE_NAME,INSERTED_DATE) VALUES ('ZEDIC-9876-UJIE','76_ECS_MON_20221024.TXT','24-OCT-22');
INSERT INTO ECS_MONTHLY_LOAD (REC_ID,FILE_NAME,INSERTED_DATE) VALUES ('POIUYU-88768','96_ECS_MON_20221024.TXT','24-OCT-22');
Query I am using:
SELECT
CASE WHEN COUNT(*) IS NULL OR COUNT(*)<2 THEN 'FAIL' ELSE 'PASS' END AS ECS_RECORD_COUNT_CHECK
FROM ECS_MONTHLY_LOAD
WHERE
INSERTED_DATE BETWEEN TRUNC((SYSDATE),'MONTH') AND LAST_DAY(SYSDATE);
I have only one record for file 96_ECS_MON_20221024.TXT and still I am getting output as PASS. If the record count per file is less than 2 or if any of the file '96_ECS_MON_20221024.TXT', '76_ECS_MON_20221024.TXT' or '86_ECS_MON_20221024.TXT' is not loaded then output should be FAIL. Date inside the file name changes every month. I think I am not checking record count for each file and not sure how I can do that. Any help on this please?