The task seems so complex that I find it tough even to phrase my question, but i tried my best. So, here are the tables:
tblLeaveType
Id LeaveType CarryForward Accruable
------------------------------------------
10 AL True True
20 CL False False
tblTransactions
Id LeaveType Year EmpCode Debit Credit
---------------------------------------------------------------
1 AL 2012 e1 0 20
2 AL 2012 e1 15 0
3 AL 2013 e1 0 20
4 CL 2012 e1 0 6
5 CL 2012 e1 3 0
6 CL 2013 e1 0 6
This is the needed output
LeaveType ThisYearCredit TotalBalance Accrued
----------------------------------------------------------
AL 20 25 5
CL 6 6 0
ThisYearCredit - will just be a sum of values of 'Credit' column where year =2013.
TotalBalance -
- For AL, it will be (sum of all credit) - (sum of all debit) for year<=2013
because the carryforward for this leave type is true.
- For CL, it will be (sum of all credit) - (sum of all debit) for year=2013,
because the carryforward for this leave type is false.
Accrued - Lets say the current month is march (3rd month). So Accrued for AL will be 20*3/12
The LeaveTypes are unknown , so cant code them as case when 'AL' etc
If it would be easy if carryforward & accrued are present for each row in tbltransactions, then that can be done.
Can this be achieved in a single query. Should I use functions..or some other...
Any help is appreciated. Thank you.
Using sql server 2008, c#