Wednesday, February 6, 2019

List of Cases created between a certain Time frame within a certain Date Range

Generate a list of Cases created from March 1st to 31st 2013 between 6AM to 8AM.

Steps:
1. Navigate to Lists > Search > Saved Searches > New.
2. Click Case.
3. Criteria > Standard > Filters:
Date Created : is within 3/1/2013 and 3/31/2013
Formula (Numeric) : is 1 : Formula : CASE WHEN ((EXTRACT(HOUR FROM TO_TIMESTAMP(TO_CHAR({createddate},'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS'))) BETWEEN 6 AND 7) OR (((EXTRACT(HOUR FROM TO_TIMESTAMP(TO_CHAR({createddate},'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS'))) = 8) AND ((EXTRACT(MINUTE FROM TO_TIMESTAMP(TO_CHAR({createddate},'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS'))) = 0)) THEN 1 ELSE 0 END
4. Click Results tab.
5. Add/remove fields base on the needs. 
6. Click Preview or Save & Run.

Note: 
1. Date Created criteria should be equal to the date range.
2. BETWEEN 6 AND 7 part of the fomula means 6:00AM to 7:59AM. It contains the start time and end minus 1. Example: 5AM to 10AM: This part of the formula should be replaced by BETWEEN 5 AND 9. Which means from 5:00AM to 9:59AM.
3. The end time should replace 8 in the formula which would mean that 8:00AM is also included.

No comments:

Post a Comment