hello im studying dba, right now im stuck with project i have to do.
Here is what bothers me.
i have 2 tables one with tasks and one with salarys.
Task table describes who is working on what from when how many hours on so on. Salarys gives me info on how much ppl earn per hour and so on. Nothing strange so far.
SO i started to calculate hours spend on task * salary per hour under chosen time period. ANd here comes the catch. Person recieved rise in salary and query automaticly adds 2 values of salary per hour on same project.
lets say 2008-01-01 salary was 100 and in 2008-05-01 120 those 2 records are in table with diff id.So i should have 213 hours spend on project but now i have 426 hours. Is there any way to chose highest salary with help of a date wich is correct to chosen time period ?
lest say time period 2008-01-01 to 08-01-30 i can chose max(date) but i need some kind of statement that max date cant be higher then chosen time period. i was trying with
and cos_tasks.task_start_date between '2008-01-01' and '2009-08-30' and cos_salaries.salary_startdate <= cos_tasks.task_start_date
problem is after this person got more salary andi change time period for longer one statement is true for 2 options so old salary and new salary are calculated.