CODEWITHSUNDEEP
sqloracle-database
F0cus
F0cus

Reputation: 625

SQL Query to fetch data between minutes and days

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..

Upvotes: 0

Views: 517

Answers (2)

Hardik Parmar
Hardik Parmar

Reputation: 1051

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

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

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)

Upvotes: 2

Related Questions