Stored Procedure Help 2
-
Name:
WEB_HR_GetRateHistory
Parameters:
@org_level_id, @emp_id
If @emp_id is 0 – get me all rate change history for all active employees within this org_level
Tables:
select * from employee
select * from employee_other
Resultset:
Employee Name
Center
Department
--Date
From Pay Rate
To Pay Rate
Changed by
Note: Date, FROM PAY RATE, TO PAY RATE, Changed by are all in XML format together in the column in the employee_other table named (rate_history).
Note: emp-name, center and departments are easy columns to retrieve from employee table.
Here is how the rate history column looks and sample xml file from one of the boxes in the column:
<Rates><Rate date_changed="Nov 10 2011 7:28AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="0" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 7:19AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="40" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 6:38AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="0" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Nov 10 2011 6:33AM" not_active_since="07/27/2011" rate="1720.78" emp_pay_type="1" emp_work_hours="40" rate2="0.00" rate3="0.00" benefit_hours="8" exempt_ind="Y" />
<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>
-
May I ask why you started a new thread for what looks like essentially the same problem?
-
I apologize, just nobody was responding to the old reply. OH yes, I do not need the time for the datetime field.
-
njdevils39 (3/19/2012)
I apologize, just nobody was responding to the old reply. OH yes, I do not need the time for the datetime field.Please remember we are volunteers and we answer when we can based on other time constraints, like our own work schedules. Please be patient and people will help as they have time.
-
As noted above this is a duplicate post. Please direct all replies to the original thread. http://www.sqlservercentral.com/Forums/Topic1268991-391-1.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply