Hi ,
I am new to SQL coding; hopefully, you can help me!
I have this SQL

select ejhannsalary,
ejhdatetimecreated,
ejhhourlypayrate,
ejhisratechange,
ejhjobeffdate
from dbo.emphjob
where ejheeid = '5U8FSP0000K0'

This SQL returns

AnnSalary DateTimeCreated HourlyPayRate IsRateChange JobEffecDate

6240.00 2006-03-10 20:57:57.783 7.500000 N 2005-11-21 00:00:00.000

6240.00 2006-03-10 20:39:36.663 7.500000 N 2005-12-27 00:00:00.000

6240.00 2007-05-04 08:39:50.000 7.500000 N 2007-04-30 00:00:00.000

6656.00 2008-01-03 11:17:32.000 8.000000 Y 2007-12-31 00:00:00.000

6656.00 2008-01-11 16:04:59.000 8.000000 N 2008-01-01 00:00:00.000


I have to capture 2 records here - old salary [6240.00 2007-05-04 08:39:50.000 7.500000 N 2007-04-30 00:00:00.000]
New Salary [6656.00 2008-01-03 11:17:32.000 8.000000 Y 2007-12-31 00:00:00.000]

And the parameters will be EjhDateTimeCreated [From and To]. How can I do this in Stored Procedure?

Your help is greatly appreciated. Thanks [/size][/size]

Post what you have tried so far.

Information provided by you is not enough to write entire procedure.

Based upon the output provided by you I assume that only some of the employees are having both old and new salaries.

In that case first identify the list of those employees who salary is revised.

Cursor v_cur is
SELECT ejheeid
FROM dbo.emphjob
WHERE IsRateChange = 'Y'

Open another and pass this ejheeid as parameter to new cursor.
Capture old and new salaries based on isratechange flag.
IsRateChange flag 'N' - old salary
IsRateChange flag 'Y' - new salary


Regards
Krishna

Thanks for your reply.

My requirements are:
Enter the Date Parameter [From and To] for DateTimeCreated. If anyone's salary has changes in the date range, then display new rate / salary and pcik up the record before with old sal/rate.

Can I UNION 2 sets of data - one with new rate / salary where isratechange = y and one with MAX(dateTimeCreated)? But with the second set of data i am not sure how NOT to capture the record where the salary was changed!

Any thoughts, thanks
Sony

>>But with the second set of data i am not sure how NOT to capture the record where the salary was changed!

To solve this problem first write a cursor to identify employee id's whose salray was changed.

Cursor v_cur is
SELECT ejheeid
FROM dbo.emphjob
WHERE IsRateChange = 'Y'
AND DateTimeCreated between fromdate and todate



Can I UNION 2 sets of data - one with new rate / salary where isratechange = y and one with MAX(dateTimeCreated)?

You can use union.