Compare dates to calculate salary amount increase

Hi,

I have the following tables and columns:

Tbl_assignment (asg)
Assignment_id
Assignment_start_date
Assignment_end_date

Tbl_salary (sal)
Salary_id
Assignment_id
Salary_amount
Salary_start_date
Salary_end_date

If We join the tables, I get the following result set:
Asg.assignment_id  sal.salary_amount.     Sal. Sal_Start_date.       sal.sal_end _date

1.             100.         2015-01-25.         2015-08-04
1.              150.         2015-08-05.       2015-08-09
1.              230.         2015-08-10.       2015-11-25
1.              


Question: I need to calculate the salary_increase for each row and display it in the calculated salary_increase.
The result should be added to the above result set (as a new calculated column within query) and values will be:

Salary_increase
0
50
80

How can I write a query to compare the dates and calculate the increase????

Thanks in advance
shmzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Please provide sample data per table (i.e. not combined), then, also, provide the "expected result".

I'm afraid that right now I don't see enough information to solve the problem. The "expected result" helps us understand what is needed for a solution.
0
shmzAuthor Commented:
Tbl_assignment
Assignment_id |stat_date. | End_date
1|2015-01-01|2016-11-12

Tbl_salary
Salary_id | assignment_id| salary_amount| sal_satrt_date|sal_end_date
123|1|100|2015-01-25|2015-08-04
124|1|150|2015-08-05|2015-08-09
125|1|230|2015-08-10|2015-11-10

The result:
Salary_id|Assignment_id|salary_Amount|sal_start_date|sal_end_date|salary_increment

123|1|100|2015-01-25|2015-08-04|0
124|1|150|2015-08-05|2015-08-09|50
125|1|230|2015-08-10|2015-11-10|80

Thanks in advance
0
Ryan ChongCommented:
you can try this:

Select asg.Assignment_id, sal.salary_amount, 
pre_salary.salary_amount pre_salary_amount,
case when pre_salary.salary_amount is null then 0 else
sal.salary_amount-ISNULL(pre_salary.salary_amount, 0)
end increment,
sal.salary_Start_date, sal.salary_end_date
from
Tbl_assignment asg inner join Tbl_salary sal
on asg.Assignment_id = sal.Assignment_id
left join 
Tbl_salary AS pre_salary ON 
pre_salary.salary_Start_date = (SELECT Max(salary_Start_date) FROM Tbl_salary WHERE salary_Start_date < sal.salary_Start_date)
      

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
This result:
| Salary_id | Assignment_id | salary_Amount |            sal_start_date |               sal_end_date | salary_increment |
|-----------|---------------|---------------|---------------------------|----------------------------|------------------|
|       123 |             1 |           100 | January, 25 2015 00:00:00 |   August, 04 2015 00:00:00 |                0 |
|       124 |             1 |           150 |  August, 05 2015 00:00:00 |   August, 09 2015 00:00:00 |               50 |
|       125 |             1 |           230 |  August, 10 2015 00:00:00 | November, 10 2015 00:00:00 |               80 |
        

Open in new window

Produced by this query:
select
      s.Salary_id
    , s.Assignment_id
    , s.salary_Amount
    , s.sal_start_date
    , s.sal_end_date
    , case when s.sal_end_date < getdate() then 0
           when getdate() <  s.sal_start_date then 80
           when s.sal_start_date <= getdate() and s.sal_end_date >= getdate() then 50
           else -1
       end as salary_increment
from Tbl_salary s
inner join Tbl_assignment a on s.Assignment_id = a.Assignment_id

Open in new window

based on the sample data:
CREATE TABLE Tbl_assignment
    ([Assignment_id] int, [stat_date] datetime, [End_date] datetime)
;
    
INSERT INTO Tbl_assignment
    ([Assignment_id], [stat_date], [End_date])
VALUES
    (1, '2015-01-01 00:00:00', '2016-11-12 00:00:00')
;


CREATE TABLE Tbl_salary 
    ([Salary_id] int, [assignment_id] int, [salary_amount] int, [sal_start_date] datetime, [sal_end_date] datetime)
;
    
INSERT INTO Tbl_salary 
    ([Salary_id], [assignment_id], [salary_amount], [sal_start_date], [sal_end_date])
VALUES
    (123, 1, 100, '2015-01-25 00:00:00', '2015-08-04 00:00:00'),
    (124, 1, 150, '2015-08-05 00:00:00', '2015-08-09 00:00:00'),
    (125, 1, 230, '2015-08-10 00:00:00', '2015-11-10 00:00:00')
;

Open in new window

I have assumed that the increments fall into 3 brackets based on a comparsion table dates to the current date/time

see:  http://sqlfiddle.com/#!3/ef020f/1
0
Ryan ChongCommented:
@shmz,

I tested it's working fine at sqlfiddle as well.

I'm using MS SQL Server 2008 environment.

full script is posted as follows:

create table Tbl_assignment
(
Assignment_id int,
Assignment_start_date datetime,
Assignment_end_date datetime
  );
insert into Tbl_assignment(Assignment_id,Assignment_start_date, Assignment_end_date) values (1, '25 Aug 2011', '13 Jan 2014');
  
create table Tbl_salary
(
Salary_id int,
Assignment_id int,
Salary_amount money,
Salary_start_date datetime,
Salary_end_date datetime
  );
  
 insert into Tbl_salary (Salary_id, Assignment_id, Salary_amount, Salary_start_date, Salary_end_date) values (1, 1, 100, '2015-01-25', '2015-08-04');
 insert into Tbl_salary (Salary_id, Assignment_id, Salary_amount, Salary_start_date, Salary_end_date) values (1, 1, 150, '2015-08-05', '2015-08-09');
 insert into Tbl_salary (Salary_id, Assignment_id, Salary_amount, Salary_start_date, Salary_end_date) values (1, 1, 230, '2015-08-10', '2015-11-25');
 
Select asg.Assignment_id, sal.salary_amount, 
pre_salary.salary_amount pre_salary_amount,
case when pre_salary.salary_amount is null then 0 else
sal.salary_amount-ISNULL(pre_salary.salary_amount, 0)
end increment,
sal.salary_Start_date, sal.salary_end_date
from
Tbl_assignment asg inner join Tbl_salary sal
on asg.Assignment_id = sal.Assignment_id
left join 
Tbl_salary AS pre_salary ON 
pre_salary.salary_Start_date = (SELECT Max(salary_Start_date) FROM Tbl_salary WHERE salary_Start_date < sal.salary_Start_date)

Open in new window

0
shmzAuthor Commented:
Paul,
The 0,50 and 80 should be calculated based on consecutive salaries!
Why did you hard code it in the query?!!
0
PortletPaulfreelancerCommented:
because I didn't understand your requirement, and obviously still don't.

where have you explained to us how the calculations are to be performed?
0
shmzAuthor Commented:
Paul, thanks for the solution but I have a table with many employees and salaries and assignments
0
shmzAuthor Commented:
Ryan,
Thanks for the solution.
There is a business rule that the start date of the new salary + 1 day is equal to end date of pre-salary. Can this be replace by max function?
0
Ryan ChongCommented:
>>There is a business rule that the start date of the new salary + 1 day is equal to end date of pre-salary. Can this be replace by max function?

Is the sample above provided by me returning the wrong records as expected? Any reason want to replace the max for row comparison? Above selection condition should manage to handle that unless you can provide us some sample data and then we may continue to enhance our existing purposed scripts.
0
PortletPaulfreelancerCommented:
If you are using SQL Server 2012 onward you can used LAG() OVER(...)
which is a more efficient way to get the "previous salary amount"
e.g.The following requires only a single pass of the salary table
select
      sal.Assignment_id
    , sal.salary_amount
    , ISNULL(sal.pre_salary_amount,0) pre_salary_amount
    , sal.salary_amount - ISNULL(sal.pre_salary_amount, sal.salary_amount) increment
    , sal.salary_Start_date
    , sal.salary_end_date
from (
      select
            *
          , lag(Salary_amount) over(partition by Assignment_id order by Salary_start_date ASC) as pre_salary_amount
      from Tbl_salary
     ) as sal
;

Open in new window


What do you mean by this?
There is a business rule that the start date of the new salary + 1 day is equal to end date of pre-salary. Can this be replace by max function?
Are you asking for another calculated column?
Please be more complete in describing what you want; providing an example would be useful.
0
shmzAuthor Commented:
Hi Ryan,
 this section of query doesn't work for me:

"(SELECT Max(salary_Start_date) FROM Tbl_salary WHERE salary_Start_date < sal.salary_Start_date)"

1. I am actually writing this query for Oracle database.

2. The assignment table is joined to  employee table and there are many records in the table, not just the three I gave as an example. when I check for Max(date_from) in salary table, it obviously finds many many records with that date which are not related to this particular assignment.

3. the business rule exist for prv_salary.date_to + 1day = current_salary.from_date
however this does not return the first record.(where there is no previous salary)

let me know if you need more clarification and thanks in advance
0
shmzAuthor Commented:
i modified it to:
SELECT Max(salary_Start_date) FROM Tbl_salary WHERE salary_Start_date < sal.salary_Start_date and assignment_id = sal.assignment_id
it looks better but still can't create a left join in oracle. as per your instruction.
and does not return the first record again.
0
PortletPaulfreelancerCommented:
>>"1. I am actually writing this query for Oracle database."
Goodness, that is a shame, so much time has been lost.

Here is a solution for Oracle. It does not require a left join because it uses the LAG() function. That function allows us to choose a prior value (in this case the previous salary amount) and that then allows us to complete the wanted calculation. Note also that NVL() is used to substitute a zero if the lagged value is null.

select
      sal.Assignment_id
    , sal.salary_amount
    , NVL(sal.pre_salary_amount,0) as pre_salary_amount
    , sal.salary_amount - NVL(sal.pre_salary_amount, sal.salary_amount) as sal_increment
    , to_char(sal.sal_start_date,'YYYY-MM-DD') as sal_start_date
    , to_char(sal.sal_end_date,'YYYY-MM-DD') as sal_end_date
from (
      select
            Tbl_salary.*
          , lag(Salary_amount) over(partition by Assignment_id 
                                    order by Sal_start_date ASC) as pre_salary_amount
      from Tbl_salary
     ) sal
;

Open in new window

That query produced this result:
| ASSIGNMENT_ID | SALARY_AMOUNT | PRE_SALARY_AMOUNT | SAL_INCREMENT | SAL_START_DATE | SAL_END_DATE |
|---------------|---------------|-------------------|---------------|----------------|--------------|
|             1 |           100 |                 0 |             0 |     2015-01-25 |   2015-08-04 |
|             1 |           150 |               100 |            50 |     2015-08-05 |   2015-08-09 |
|             1 |           230 |               150 |            80 |     2015-08-10 |   2015-11-10 |

Open in new window

The explain plan shows that using LAG (which avoids a self join) only requires a single pass of the data.explain plan, window sort for the LAG function
From this sample data:
CREATE TABLE Tbl_assignment
    ([Assignment_id] int, [stat_date] datetime, [End_date] datetime)
;
    
INSERT INTO Tbl_assignment
    ([Assignment_id], [stat_date], [End_date])
VALUES
    (1, '2015-01-01 00:00:00', '2016-11-12 00:00:00')
;

CREATE TABLE Tbl_salary 
    ([Salary_id] int, [assignment_id] int, [salary_amount] int, [sal_start_date] datetime, [sal_end_date] datetime)
;
    
INSERT INTO Tbl_salary 
    ([Salary_id], [assignment_id], [salary_amount], [sal_start_date], [sal_end_date])
VALUES
    (123, 1, 100, '2015-01-25 00:00:00', '2015-08-04 00:00:00'),
    (124, 1, 150, '2015-08-05 00:00:00', '2015-08-09 00:00:00'),
    (125, 1, 230, '2015-08-10 00:00:00', '2015-11-10 00:00:00')
;

Open in new window

also see http://sqlfiddle.com/#!3/ef020f/1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shmzAuthor Commented:
Thank you Paul. I tried your code and worked beautifully.

- What  does partition by assignment_id do here?

- For the operation cost, the graph shows 132 byte per 3 rows.
That means for 4 million rows the operational cost is 176 MB. what does this value mean?
0
PortletPaulfreelancerCommented:
>>"What  does partition by assignment_id do here?"

Within OVER( ...) "partition by" is a little similar to "group by"

in this particular case it is used to re-start the LAG() to look for the earliest date for an assignment_id

>>"For the operation cost, the graph shows 132 byte per 3 rows. "
that explain plan belongs to the very small sample I had access to use, difficult/impossible to extrapolate to the actual db/tables. Just run an explain plan of your own. (if you don't know how, that's a new question) but there are heaps of answers to that already.
0
shmzAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.