How can I effectively perform advanced processing on my DB?

Question

I'm developing a timesheet application using SQL Server as my backend and I am trying to figure out the best way to handle implementing relatively complex processing logic in my DB for reporting purposes.

I have a table (timesheet_entries) that is composed of the following fields:

• entry_id

• employee_id

• job_id

• job_state

• pto_code (this signifies that the entry is Paid Time Off and is the id of what type of PTO - Vacation, Sick, etc - that it is. It is always NULL when job_id is populated, and vice versa)

• entry_date

• total_hours

• ot_exempt (a boolean value that determines if the entry should be calculated in the overtime totals)

I need to create a report that can give a summary of time for each employee broken down by state. I know I can do this fairly easily by using a SUM(total_hours) and GROUP BY job_state, however I the way I need to calculate overtime has a fairly specific logic to it.

First things first I need to calculate the total number of hours for each employee for a given time period. Next I need to subtract any time that is either marked as PTO or OT Exempt to get me my total regular hours. From there I can begin counting any of the regular hours that are over 40 hours in a week into overtime. What makes it really difficult for me is that I have to be able to calculate OT by the state it was collected in by essentially tracking which entry (ordered by entry_date) pushes the user's regular hours over 40 and then make sure I begin totaling the OT for the state the particular job is in (job_state).

For example, if an works his first 35 hours for the week in a job in NC and then spends the next 15 on job in SC, the report would have to show something like the following:

Employee Name / Job State / Reg Hours / OT Hours

Smith, John / NC / 35 / 0

Smith, John / SC / 5 / 10

And if you were to say that after he worked the 15 hours on the SC job, he began working on another job in NC he would have to begin accruing OT in NC next, since he would already be over 40 regular hours in a week. In the event that John Smith finished the week by working an additional 15 hours in NC his final report would look like:

Employee Name / Job State / Reg Hours / OT Hours

Smith, John / NC / 35 / 15

Smith, John / SC / 5 / 10

I'm pretty sure that this is something that cannot be accomplished using regular SQL. Would I have to use a UDF or something like that to accomplish this task? Considering that I can rather easily get an employee's entries using a simple query, am I able dump the result set into the UDF to process and then output my desired result? If not, would I just have to include and execute the SQL statement within the UDF?

If anyone has any thoughts on the best way I might accomplish this task, I'd greatly appreciate it.

-Mike

Answer 1

You could do this with multiple queries inside of one user stored procedure (sp). You can return multiple result sets to your ASP.Net code to be consumed by a DataSet with DataTables in ASP.Net. Then you can use that to create your report.

Answer 2

Business logic belongs in the business layer. I think this is probably a little too much to be put in SQL (even if it's possible).

Answer 3

This would probably be best handled in code. What you need to look into are service busses, not fancy T-SQL.

Answer 4

Don't forget CLR Stored Procedures. You can write .NET dll's that are loading into your SQL Server 2005+ database to be executed as stored procedures. Logic like you are describing is probably easier to implement with a .NET language (e.g. C#) than TSQL.