Dynamic SQL with correlated subquery
I need help/input concerning a problem I am having writing a SQL statement.
I created a table called paytype with many different commission pay types and a formula to calculate the commission percentage. The data in the formula is a string containing a select statement to calculate commission.
I created a table called emppayrollmemb to apply these commission pay types to each employee for different durations.
Here is the relational diagram:
What I need to do is somehow combine dynamic sql and correlated subquerys to display a report showing all employees with a paytype assigned to them for a particular paydate and on the same row show the total commission earned with the calculated commission level based on the formula in the paytype row of paytypes for each employee. I am almost there, but dont know how to execute the string (containg the [formula] select sql statement.)
This sql statement gives me all employees and formulas with an assigned paytype for a paydate, but now I need to also execute the formula for each employee to show the total commission and corresponding commission percentage.
Here is an example of the results of the below sql:
As you can see I need to return a percentage instead of the formula.
declare @paydate datetime
declare @branchid tinyint
set @paydate = '7/5/2005'
set @branchid = 2
SELECT emppayrollmemb.empid, paytype.formula as formula
emppayrollmemb INNER JOIN paytype ON emppayrollmemb.paytypeid = paytype.paytypeid
(emppayrollmemb.startdate <= CONVERT(DATETIME, @paydate, 102)) AND (emppayrollmemb.enddate IS NULL) AND (paytype.paystyleid = 3) and (emppayrollmemb.branchid = @branchid)
(emppayrollmemb.startdate < CONVERT(DATETIME, @paydate, 102)) AND (emppayrollmemb.enddate >= CONVERT(DATETIME, @paydate, 102)) AND (paytype.paystyleid = 3) and (emppayrollmemb.branchid = @branchid)
order by empid
Click Here to Expand Forum to Full Width