Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
Empty Rate field in timetracking query |
Author |
Message |
|
Posted : 2009-01-02 06:43:23 |
When I run this query:
select txndate,entityreflistid,entityreffullname, ClassRefFullName,rate,durationminutes,payrollitemwagereffullname,payrollitemwagereflistid,notes from timetracking where txndate >= {d '2008-12-01'} and txndate <= {d '2008-12-31'} AND ( entityreflistid = '20D0000-957970645')
The rate field is always empty.
One of our state unemployment forms requires hours, payrate and wages separated by day of the week and the payrate for each day is not always the same.
I have been challenged by this for a long time.
Right now I'm using this query to extract the pay rate by matching the AmountRate from the query below to the payrollitemreffullname from the query above (instead of the preferred payrollitemreflistid that never changes):
sp_report PayrollSummary show AmountHours_Title, Label, AmountHours, AmountRate, Amount parameters Datefrom={d '2008-12-28'}, Dateto= {d '2009-01-03'}, SummarizeColumnsBy='Employee' , EntityFilterListIDs='20D0000-957970645'
But sometimes the rate field is also empty in the sp_report Payroll Summary.
HELP! |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2009-01-02 08:47:28 |
|
|
|
|
Posted : 2009-01-03 01:51:52 |
Oh. Thanks.
Select txndate,entityreflistid, durationminutes,payrollitemwagereffullname,payrollitemwagereflistid,notes from timetracking where txndate >= {d '2008-12-01'} and txndate <= {d '2008-12-31'}
gets this result
I need the payrate for each timetracking record (or line). Is that possible? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2009-01-03 08:28:10 |
Try:
Select timetracking.txndate, timetracking.entityreflistid, timetracking.durationminutes, timetracking.payrollitemwagereffullname, timetracking.payrollitemwagereflistid, EmployeeEarning.PayrollInfoEarningsRate, timetracking.notes from timetracking, EmployeeEarning where timetracking.entityreflistid = EmployeeEarning.ListID and timetracking.txndate >= {d '2008-12-01'} and timetracking.txndate <= {d '2008-12-31'} and timetracking.payrollitemwagereflistid = EmployeeEarning.PayrollInfoEarningsPayrollItemWageRefListID |
|
|
|
|
Posted : 2009-01-03 15:47:09 |
Though that's a good statement for current pay periods, it will show current payrates with historical time data so that when someone gets a raise, the payrate data is no longer accurate for prior-to-the-raise time records.
Am I wrong? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2009-01-04 11:14:48 |
Hey!, you said for you wanted a query for one of your state unemployment forms requires hours, payrate and wages separated by day of the week and the payrate for each day. Should be fine on the day. Don't forget Intuit doesn't want us doing anything to do with payroll. That's why the data links are not logical and the payroll tables are missing key data.
You need to go back to the standard Payroll Summary report:
sp_report PayrollSummary show AmountHours_Title, Label, AmountHours, AmountRate, Amount parameters DateMacro = 'Today', SummarizeColumnsBy='Employee'
or for a specific time period:
sp_report PayrollSummary show AmountHours_Title, Label, AmountHours, AmountRate, Amount parameters DateFrom = {d'2008-12-01'}, DateTo = {d'2008-12-31'}, SummarizeColumnsBy='Employee'
and look into why AmountRate is 0 sometimes! |
|
|
|
|
Posted : 2009-01-08 10:07:30 |
Tom:
Select timetracking.txndate, timetracking.entityreflistid, timetracking.durationminutes, timetracking.payrollitemwagereffullname, timetracking.payrollitemwagereflistid, EmployeeEarning.PayrollInfoEarningsRate, timetracking.notes from timetracking, EmployeeEarning where timetracking.entityreflistid = EmployeeEarning.ListID and timetracking.txndate >= {d '2008-12-01'} and timetracking.txndate <= {d '2008-12-31'} and timetracking.payrollitemwagereflistid = EmployeeEarning.PayrollInfoEarningsPayrollItemWageRefListID
|
is great code but when run for past periods, the payrate shown is always the employee's current payrate.
I will go back to the sp_report. That seems the best way to extract the info.
Thanks. |
|
|
|
|