-
Sql Coding Help
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]
-
Re: Sql Coding Help
Post what you have tried so far.
-
Re: Sql Coding Help
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
-
Re: Sql Coding Help
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
[QUOTE=krishnaindia2007;31307]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[/QUOTE]
-
Re: Sql Coding Help
[B]>>But with the second set of data i am not sure how NOT to capture the record where the salary was changed![/B]
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'
[B]AND DateTimeCreated between fromdate and todate[/B]
[B]Can I UNION 2 sets of data - one with new rate / salary where isratechange = y and one with MAX(dateTimeCreated)? [/B]
You can use union.