Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

oracle date query

Need a parameter for No of weeks of schedule.

define d_first_day_week  date
= date(7- mod(days(sysdate) , 7)) + (days(sysdate)) - 7)

define d_weeks  date =
date(days(d_first_day_week) + nconvert(getsys(parameter_name)) * 7)

Need help inh translating this to oracle
so that I can use in the query

select  a.department_id,a.payroll_id,
        a.schedule_date,a.start_time,a.start_ampm,
        a.end_time,a.end_ampm,a.total_hours,
        a.short_description,s.site_name,s.city_name
from off_duty a, sites s,blood_drives bd
where a.site_code = s.site_code(+)
and a.site_code = bd.site_code(+)
and a.schedule_date = bd.drive_date(+)      
and a.department_id = 5
and a.schedule_date = parameter_name(no of weeks of schedule)
0
anumoses
Asked:
anumoses
  • 8
  • 5
  • 2
1 Solution
 
sdstuberCommented:
you have declarations that use non-Oracle functions  - what do those functions do?

you have a query that uses another non-Oracle function - what does that function do?


there is no input, nor expected output
what is the question?
0
 
anumosesAuthor Commented:
The logic is I have to use 1 to 5 weeks.
The first day has to be a sunday. so 1 - sunday to saturday. (april 8th to april 14th  2012)
2 weeks -( april 8th to april 21st 2012).
So when the user puts 1 then I have to to take the schedule_date = to that parameter. Internally convert 1 to sunday to saturday
0
 
anumosesAuthor Commented:
No I take back. I will post very soon what is required
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sdstuberCommented:
>>> convert 1 to sunday

I understand that part,    

DECODE(n,
              1, 'Sunday',
              2, 'Monday',
              3, 'Tuesday',
              4, 'Wednesday',
              5, 'Thursday',
              6, 'Friday',
              7, 'Saturday'
             )



the rest I still don't know what you want to do.  Please provide sample data and expected output.
0
 
sdstuberCommented:
sorry, didn't see the second post
0
 
anumosesAuthor Commented:
OK. here it is. If I am running the report today, then today is 4th april 2012. So I have to go back to sunday ie is the 1st april. Then from that sunday add no of weeks schedule, (1-5)
0
 
slightwv (䄆 Netminder) Commented:
Beginning of the week is:  trunc(sysdate, 'DAY')

>>add no of weeks schedule, (1-5)

Are you then wanting to addd 'weeks' to that?  if so, sumple math.
0
 
sdstuberCommented:
this will give you Sunday of the current week

trunc(sysdate,'d')


this will give you Sunday of the current week plus one week

trunc(sysdate,'d') + 7

this will give you Sunday of the current week plus two weeks

trunc(sysdate,'d') + 14

this will give you Sunday of the current week plus five weeks

trunc(sysdate,'d') + 35


if you have number of weeks as a number parameter "n",  try this...

trunc(sysdate,'d') + (7 * n)
0
 
anumosesAuthor Commented:
select trunc(sysdate, 'DAY') from dual

TRUNC(SYSDATE,'DAY')
4/1/2012

So its sunday. Then I have to add 1(1 week) 2 (2 weeks) to 4/1/2012 and that has to be used in the query

select  a.department_id,a.payroll_id,
        a.schedule_date,a.start_time,a.start_ampm,
        a.end_time,a.end_ampm,a.total_hours,
        a.short_description,s.site_name,s.city_name
from off_duty a, sites s,blood_drives bd
where a.site_code = s.site_code(+)
and a.site_code = bd.site_code(+)
and a.schedule_date = bd.drive_date(+)      
and a.department_id = 5
and a.schedule_date = ????????
0
 
anumosesAuthor Commented:
OK got you. Will try
0
 
anumosesAuthor Commented:
select trunc(sysdate,'d'), trunc(sysdate,'d') + (7 * 1) from dual

TRUNC(SYSDATE,'D')      TRUNC(SYSDATE,'D')+(7*1)
4/1/2012                             4/8/2012

4/1/2012 is correct. But I have to get 4/7/2012 ( saturday)
0
 
anumosesAuthor Commented:
select trunc(sysdate,'d'), trunc(sysdate,'d') + (6 * 1) from dual

This is what I did.
0
 
slightwv (䄆 Netminder) Commented:
>>This is what I did.

Does that get you what you want?

If so, don't forget to close out the question.

If not, what else do you need?
0
 
sdstuberCommented:
I suggest doing this...

select trunc(sysdate,'d'), trunc(sysdate,'d') + -1 + (7 * 1) from dual

if you try multiplying 6 for multiples of weeks you'll get the wrong result.
0
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now