April 30, 2013 at 10:21 am
Hi, I am pretty good creating access queries, but when our time and attendance software was recently updated, the use of big integers has resutled in me having to re-create all of my queries in Visual Studio 2008. I am NOT a SQL expert, so I apologize now for what is going to be my attempt at a fairly complicated query. I would really appreciate any advice on how to accomplish this. Here goes:
Our payroll admin needs to see how many sick, personal, etc days a person has as of today. But the database does not contain that, that would be too easy. Instead, the database contains a transaction table, which contains all balance related transactions, including the date.
This is the query that I have so far, that works to calcualte a person's balance, but I need to layer onto it to only factor in transactions that have already occured, not transactions written to the table that are to take place in the future.
SELECT employee.emp_fullname AS Name,
employee.emp_name AS EmpNumber,
employee_balance.empbal_value AS BALANCE,
balance.baltyp_id,
balance.bal_id AS BalanceID,
balance.bal_name AS BalanceName,
ent_emp_policy.entemppol_end_date,
( CASE
WHEN ent_emp_policy.entpol_id = 10003 THEN Cast (
employee_balance.empbal_value*** / 480 AS DECIMAL(8, 2))
ELSE Cast(employee_balance.empbal_value*** / 510 AS DECIMAL(8, 2))
END ) AS Days --this is b/c we have civilian employees who have 480 minute days, and sworn employees who have 510 minute days
FROM ((employee
INNER JOIN ent_emp_policy
ON employee.emp_id = ent_emp_policy.emp_id)
INNER JOIN employee_balance
ON employee.emp_id = employee_balance.emp_id)
INNER JOIN balance
ON employee_balance.bal_id = balance.bal_id
WHERE @balanceID = balance.bal_id
AND ent_emp_policy.entemppol_end_date = '01/01/3000'
AND ( CASE
WHEN ent_emp_policy.entpol_id = 10003 THEN Cast (
employee_balance.empbal_value*** / 480 AS DECIMAL(8, 2))
ELSE Cast(employee_balance.empbal_value*** / 510 AS DECIMAL (8, 2))
END ) > @DaysOver
The user will select the balance ID and the days over.
Here is what I am trying to add into my query, but I don't know exactly how the pieces fit together.
SELECT SUM(CASE WHEN wrks_work_date --this is the date when the transaction occured
<= "today" THEN eblog_delta --this is the total minutes of the transaction/could be a deduction or addition
ELSE 0 END) As Transactions
Assuming that the code above correctly calcualtes the value of transactions, I then want to take Balance-Transactions to get a Current Value.
From what I have researched, I think that the last piece of code would fit into my origianl query where I have indicated '***', but I am am stuck on the exact syntax, and if it is even possible for me to calculate a 'transaction' field, and then use it in the same query in another calculation.
Any help would be so much appreciated, and I thank you for taking the time to review this post.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply