Sql Coding Help

[sonyshah]

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]

[krishnaindia2007]

Post what you have tried so far.

[krishnaindia2007]

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

[sonyshah]

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

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

[krishnaindia2007]

>>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.