Solved

Date Clause

Posted on 2010-11-30
13
213 Views
Last Modified: 2012-05-10
I need the following query to altered

select *
from BLOCKMEMBER
where trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO

Without disturbing the above I want check one more cndition
if EFFECTIVEDATEFROM is less than sysdate and if EFFECTIVEDATETO is null then it should appear in output of query.

if EFFECTIVEDATEFROM is greater than sysdate and if EFFECTIVEDATETO is null then it should NOT appear in output of query.

and I need to conver this stored procedure with input MEMBER_ID OR EMPLOYEE_ID OR CLUB_ID
0
Comment
Question by:GRChandrashekar
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34237597
what about this:
select *
from BLOCKMEMBER
where trunc(sysdate) between EFFECTIVEDATEFROM and NVL(EFFECTIVEDATETO, sysdate)

Open in new window

0
 

Author Comment

by:GRChandrashekar
ID: 34237610
Works ok but if EFFECTIVEDATEFROM is equal to sysdate then it is not coming in output. it should come
0
 
LVL 12

Expert Comment

by:enachemc
ID: 34237611
select *
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (EFFECTIVEDATEFROM < trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID)


p_ are paremeters
0
 

Author Comment

by:GRChandrashekar
ID: 34237620
angelIII:
enachemc:

Works ok but if EFFECTIVEDATEFROM is equal to sysdate then it is not coming in output. it should come
0
 
LVL 1

Expert Comment

by:sunny25
ID: 34237624
I hope this fulfils your requirement
select *
from BLOCKMEMBER
where trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or
(EFFECTIVEDATEFROM <TRUNC(sysdate) AND EFFECTIVEDATETO IS NULL )
0
 

Author Comment

by:GRChandrashekar
ID: 34237635
sunny25:
Works ok but if EFFECTIVEDATEFROM is equal to sysdate then it is not coming in output. it should come
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Expert Comment

by:enachemc
ID: 34237637
select *
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (EFFECTIVEDATEFROM <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID)
0
 

Author Comment

by:GRChandrashekar
ID: 34237644
enachemc: Stil not working
0
 

Author Comment

by:GRChandrashekar
ID: 34237649
I have given effectivefrom date as 30/11/2010 09:00:00 PM which is sysdate and effective to is null
0
 
LVL 12

Expert Comment

by:enachemc
ID: 34237659
select *
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (trunc(EFFECTIVEDATEFROM) <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID)
0
 
LVL 1

Expert Comment

by:sunny25
ID: 34237664
try this
EFFECTIVEDATEFROM <=TRUNC(sysdate) AND EFFECTIVEDATETO IS NULL
0
 

Author Comment

by:GRChandrashekar
ID: 34237671
@ enachemc:

This is perfect.
Request please give me stored procedure to pass date instead of sysdate and to pass MEMBER_ID OR
EMPLOYEE_ID
OR
CLUB_ID
0
 
LVL 12

Accepted Solution

by:
enachemc earned 500 total points
ID: 34237713
create or replace procedure prod(dt date, p_MEMBER_ID number, p_EMPLOYEE_ID number, p_CLUB_ID number)
res number;
begin
select id into re into
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (trunc(EFFECTIVEDATEFROM) <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID);

--if you want only one of the IDs, just make the others NULL in the calling sql
end prod;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now