March 8, 2007 at 2:38 am
Hi all,
Sorry I'm a newbie so please forgive me if this is a simple question.
I have 3 tables which I need to extract data from: Employee, Budget, Expenses the key being Emp ID. I need to create a stored procedure to use on a pivot table which will show the Employee, the budget details and totals, expense details and totals include the remaining budget balance.
EMPLOYEE--------EXPENSE AMT----BALANCE AMT---BUDGET AMT
Amy Total*------1000-----------300-----------700
-(Budget row)---1000
-(Expense row)-----------------100
-(Expense row)-----------------200
Jane------------1000-----------300-----------700
John------------2000-----------100----------1900
* each employee should be collapsable in the pivot table but when expanded detailed data rows should be shown.
Simplified table details:
Employee: EmpID, EmpName, EmpType
Budget: EmpID, BudgetDesc, BudgetAmount, BudgetType
Expenses: EmpID, ExpenseDesc, Expense Amt, ExpenseType
I'm not sure how to create the balance column. I also have an added complexity where I will show only certain Budgets or Expenses based on Type e.g. only extract Budget lines if the BudgetType = 'Approved'.
Any help is appreciated.
Thanks
Andy
p.s. sorry about formatting, not sure how to add tabs to this message
March 8, 2007 at 8:06 am
Hi ,
Can u send the sample data to me.. so that i can check my logic..
Anyway as per ur requirement i can send u a script which is given below :
Select E.Emp_id,EmpName,BudgetType,BudgetAmount,Expense Amt,(BudgetAmount - Expense Amt) Balance from Employee e join budget b on e.emp_id=b.emp_id join Expenses ex on ex.emp_id=e.emp_id where BudgetType = 'Approved'
Use the any no of columns in above Sql Query... and tell me if u find any problem regarding this..
Regards,
Amit Gupta...
March 8, 2007 at 9:37 am
Perhaps this will work:
select Employee.EmpName
, COALESCE ( EmployeeBudgets.BudgetAmount, 0 ) as BudgetAmount
, COALESCE ( EmployeeExpenses.ExpenseAmt, 0 ) as ExpenseAmt
, COALESCE ( EmployeeBudgets.BudgetAmount, 0 )
- COALESCE ( EmployeeExpenses.ExpenseAmt, 0 ) as BalanceAmt
from Employees
left outer join
(select EmpID, SUM(BudgetAmount)
from Budget
group by EmpId
) as EmployeeBudgets
on EmployeeBudgets.EmpID = Employees.EmpID
left outer join
(select EmpID, SUM(ExpenseAmt) ExpenseAmt
from ExpenseAmt
group by EmpId
) as EmployeeExpense
on EmployeeBudgets.EmpID = Employees.EmpID
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply