Hi,
I'm trying to get my wages costs and it requires me to pull from two tables. One table has the rate the other has the labor hours, there are also two sets of matching codes in both tables. The rate table has multiple rates for the same codes as the rates have been increased over time(there are effective dates for the rates), how do I return the appropriate rate based on the day worked and effective rate for that period? I need the lastest rate that has an effective date equal or less than the day worked.
Table 1 Table 2
Rate Hours worked
Effective date Day worked
Code 1 Code 1
Code 2 Code 2
Any help would be great, thanks
Date
Votes
6 comments
-
Jet Reports Historic Posts Hi,
So assuming you put the day worked into cell C2, your formula to get the rate might look something like this:=NL("Last","Table 1","Rate","+Effective date",NP("DateFilter",,$C$2))
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
Sorry, I need my day worked to be less than or equal to the effective date. The effective date is the last date that a wage adjustment was made active.
Here is my current formula, but it is just returning the last rate for the desired codes.
=IF(M5="R",NL("last","JOB_LAB_RATE_SCHD","REG_RATE","HIGH_PAY_RT_NUM",N5,"PR_CLASS_NUM",O5,"JOB_ID",$C$2),(NL("last","JOB_LAB_RATE_SCHD","TH_RATE","HIGH_PAY_RT_NUM",N5,"PR_CLASS_NUM",O5,"JOB_ID",$C$2)))
I need the last rate that has a work date less than or equal to the effective date.
The work date is from the TC_Base Table and the Effective date is from the Job_Lab_Rate_SCHD Table.
Thanks -
Jet Reports Historic Posts Okay maybe there's something I'm not understanding. My formula would give you the last rate where the values are being sorted by effective date and filtered for all effective dates less than or equal to the date in C2. From your description, this seems to be what you want. I'm assuming you are going to replicate rows from the TC_Base table and get the effective date from that table (so instead of $C$2 the cell reference will be changed to the effective date replicated from TC_Base table). You will probably also have to add filters for Code 1 and Code 2 to my formula to make it work. Is there something else I'm not understanding here?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
The Effective Date is in the table with the rates (Job_Lab_Rate_Schd Table) and the work date is in the table with the hours(TC-Base Table), here is a quick listing of what is in each table
TC_Base Table
Work Date
Hours
Code 1
Code 2
Job #
Job_Lab_Rate_Schd Table
Effective Date
Rate (includes multiple rates for the same codes with multiple effective rates)
Code 1
Code 2
Job #
Yes, I'm replicating the rows from the TC_Base table and this is the table that has the work date in it. I need the rate from the Job_Lab_Rate_Schd table where the work date is less than or equal to the Effective date.I believe my filter needs to be on the Work date.
So I'm replicating the rows from the TC_Base table and then have a formula that returns the rate from the Job_Lab_Rate_Schd table but I need it to return the last rate where the Work Date is less than or equal to the effective date.
Does that make any sense?
Thanks for your help -
Jet Reports Historic Posts Aaah, now I understand what you're trying to do. If you were using Navision, I could write a CalcFilter to do this, but with a Universal connector, I think the only way to do this (short of actually replicating out all the rates) would be to write a straight SQL query to do it using the Jet SQL= functionality.
Regards,
Hughes -
Jet Reports Historic Posts Hi,
We run Penta, and I don't know anything about SQL. I might try replicating both tables and filtering on that.
Thanks for your help
Please sign in to leave a comment.