SQL Query to fetch data between minutes and days

0
votes

I have requirement, this is for a report.

Requirement description:

Need data from a table which are in OPEN status for more than 30 minutes

Well, the above requirement can be obtained from

select * 
from xyz 
where status = 'OPEN' 
and last_update_date <= sysdate - (30/1440) --30 is minutes and its a parameter

the above query will fetch all the data which are in OPEN status from beginning to sysdate - (30/1440). so i want to modify the query to restrict the complete data , by adding another parameter like DAY

for example if i give 10 as day, it should fetch all the data only in the last 10 days and sysdate-30 minutes. we should use last_update_date column for restricting the day.

If I dont give any day as input if should fetch all the records from sysdate-30 minutes. If I dont give minutes it should fetch all the records in OPEN status.

is the question clear enough? My English is bad.

Please suggest me a query..

2
sqloracle
If you will provide suppose 10 days then query need to return last 10 days data & if you do not provide any day then it should display only last 30 miutes data Am i Correct? – Hardik Parmar
Correct ... and if we dont provide both, then just return all the records in OPEN status.. – TAT
will you provide just number of days or you will provide the Date upto which recoerd has to disply – Hardik Parmar
its a parameter.. say 10 – TAT

2 Answers

2
votes

Simply use OR expressions where you check the parameters for NULL:

select * 
from xyz 
where status = 'OPEN' 
and (last_update_date <= sysdate - (:minutes/1440) or :minutes is null)
and (trunc(last_update_date) >= trunc(sysdate - :days) or :days is null)
1
votes

This query is not tested. Please tell me this query is returning any error or somthing that i have missed.

select 
    * 
from 
    xyz 
where 
    status = 'OPEN' 
    and
    last_update_date <= CASE WHEN @No_Of_Days <> 0 THEN sysdate - @No_Of_Days ELSE NULL END
    and     
    minutes(last_update_date) <=  SYSDATE - 30/1440