SQL Stored Procedure Help - Doing Actions on Multiple Recordsets And Then Comparing Results

My experience with Stored Procedures is limited and I'm hoping this will solve my payroll problems. Further, for what I'm attempting to accomplish a Stored Procedure may not be the best answer.

I'm attempting a Payroll function in which the goal of the Stored Procedure is to fetch the data for a given Payroll Period, do math on the first returned employee, output a calculated result, then move to the next. Let me explain a bit more...

Based on a Pay Period ID which is given to the Stored Procedure, I would like it to do the following:

Select the employee in that period, add a total of his hours worked, then add that employees total commissions, whichever amount is greater - return that value (along with the totals - such as the total hours and total commission) - then move to the next employee until completed.

The data is laid out as follows:

Payroll Period Table PayrollID, DateBegin, DateEnd The Stored Procedure is given the Payroll ID (as the QueryString) in the beginning, so I would have to take the corresponding DateBegin & DateEnd data, and grab the DateTimeIn and DateTimeOut from the EmployeeTimeTable to get the appropriate attendance records.

PayrollID - The ID of the Pay Period.

DateBegin - Start Date of the Pay Period.

DateEnd - End Date of the Pay Period.

Employee Time Table EmployeeID, DateTimeIn, DateTimeOut, H开发者_开发问答ourlyRate, CalculatedHours, AmountPaid - there is an instance for each day worked, so the Stored Procedure would calculate worked hours for the day.

EmployeeID - The ID of the Employee who's record this relates to.

DateTimeIn - The Date and Time the Employee punched in.

DateTimeOut - The Date and Time the Employee punched out.

HourlyRate - The monetary rate that the employee is paid at per hour.

CalculatedHours - The amount of hours worked for this record.

AmountPaid - Simply HourlyRate x CalculatedHours.

Commission Table EmployeeID, PayrollID, AmountPaid - There is a record for each "Commission" earned for each sale.

EmployeeID - The ID of the Employee who's record this relates to.

PayrollID - The ID of the Pay Period.

AmountPaid - The monetary amount of commission earned for this record.

So my question is - how am I able to create the functionality described? Is it even possible? Would this take a miracle to accomplish!? Or is there a better way for me to about this?

Also, in the end this would theoretically be output to a WebForm GridView.

---

@Damien_The_Unbeliever is right in his recommendations.

However, I think you have 3 options available to you:

• write a single SQL query to retrieve the data you're looking for. I think it's possible, though it could be a little complex. It will be the fastest solution and no mistake. The way you'd do your comparison might be through a union and subqueries. With a proper schema, I'm sure people will have a punt at this option. It's the most philosophically pure way to do it, but also quite tricky if you're not ninja-level SQL.
• Write one query to retrieve all employees active during the payroll period, return that to your VB front end, and iterate through the result set to run individual queries to retrieve their hours and payroll; do the business logic in VB. This is a little sub optimal in terms of performance, but allows you to work step by step, and using the VB IDE, which some developers prefer.
• Write a stored procedure, in which you create a temporary table to hold your result set, insert employee records into that temporary table for all employees valid in the payroll period, and then write specific queries to update the temporary table with their hours worked, commission values etc. This localizes all your business logic in the database, and is likely to perform pretty quickly; depending on the hairiness of your SQL, it may well be faster than option 1. It also allows you to work up the business logic step by step, which most developers find easier than embodying it all in a single SQL statement.

I am on the road, and don't have an instance of SQL Server to play with, so the example below may contain some syntax errors, but option 3 would work broadly as follows:

       create proc calculatePaySlipForPeriod @periodID int
    as
    begin

    create table #results
    (emp_id   int,
    emp_name varchar(255), 
    commission_earnt money, 
    labour_earnt money,
    amount_to_pay money)

    insert into #results
    select emp_id
    from employee_time
    where timeIn between (select dateBegin from PayrollPeriod where PayrollID = @periodID)
          and (select dateEnd from PayrollPeriod where PayrollID = @periodID)

    insert into #results
    select emp_id 
    from Commission
    where payrollID = @periodID
    and emp_id not in
      (select emp_id from #results)
   /** We now have all the employees in the table, so we can populate their earnings **/

   update #results
   set labour_earnt_money = sum(AmountPaid)
   from #results   r, 
        employee_time et
   where r.emp_id = et.emp_id
    and timeIn between (select dateBegin from PayrollPeriod where PayrollID = @periodID)
          and (select dateEnd from PayrollPeriod where PayrollID = @periodID)

  update #results
  set    commission_earnt = sum(AmountPaid)
  from   #results r, 
         Commission c
  where r.emp_id = c.emp_id
  and   c.payroll_id = @periodID

  update #results
  set amount_to_pay = commission_earnt
  where commission_earnt > labour_earnt

  update #results
  set amount_to_pay = labour_earnt
  where labour_earnt >= commission_earnt

  /** We now have all the data populated, so we return the table to the front end. 

  select * from #results

  /** no need to explicitly drop the table, happens automatically at the end of the proc.

    end

This is the broad outline - you may need to deal with date/time weirdness (is a shift counted when the operator starts or end during the payroll period? I've assumed start).

By building up the results step by step, you can easily work out what's going on; to debug, you can select * form your temporary table during the execution.

---

Assuming the EmployeeTime table looks something like this:

CREATE TABLE EmployeeTime (
     EmployeeID int not null,
     DateTimeIn datetime not null,
     DateTimeOut datetime not null,
     HourlyRate decimal(10,2) not null,
     HoursWorked as (CONVERT(int,DATEDIFF(minute,DateTimeIn,DateTimeOut)/15.0)+1)/4.0,
     AmountPaid as (CONVERT(int,DATEDIFF(minute,DateTimeIn,DateTimeOut)/15.0)+1)/4.0 * HourlyRate
)

(Unfortunately, you can't build one computed column based on another. You could, however, move the formula (CONVERT(int,DATEDIFF(minute,DateTimeIn,DateTimeOut)/15.0)+1)/4.0 into a user defined function)

Then you'd write a query that looks like this:

;WITH PayrollPayments as (
     SELECT PayrollID,EmployeeID, SUM(HoursWorked) as HoursWorkedTotal,SUM(AmountPaid) as TotalPay,0 as Commission from EmployeeTime et inner join Payroll p on p.DateBegin < et.DateTimeOn and p.DateEnd > et.DateTimeOut GROUP BY PayrollID,EmployeeID
     UNION ALL
     SELECT PayrollID,EmployeeID,0,SUM(AmountPaid) as TotalPay,1 from Commissions
)
SELECT PayrollID,EmployeeID,MAX(HoursWorkedTotal) as TotalHours,MAX(TotalPay) as MaxPay,MAX(CASE WHEN Commission=0 THEN TotalPay END) as TotalPayment,MAX(CASE WHEN Commission=1 THEN TotalPay END) as TotalCommission
FROM PayrollPayments
WHERE
     PayrollID = @PayrollID

If your EmployeeTime table doesn't look as above, and you're unable to alter your existing EmployeeTime table, you can always create a Common Table Expression (similar to PayrollPayments in the above query) that adds the hours worked/amount paid calculations first.