Help Needed For T-sql Query

Posted by Sharpcnet under Sql Server on 12/10/2013 | Points: 10 | Views : 740 | Status : [Member] | Replies : 4
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#




Responses

Posted by: Bandi on: 12/10/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down

select LeaveType, EmpCode, SUM(case when YEAR= YEAR(getdate()) then Credit end) ThisYearCredit
,CASE WHEN LeaveType = 'AL' THEN SUM(Credit) - SUM(CASE WHEN Year <= YEAR(GETDATE()) THEN Debit END)
WHEN LeaveType = 'CL' THEN SUM(case when YEAR= YEAR(getdate()) THEN Credit-debit END) END AS TotalBalance
,CASE WHEN LeaveType = 'AL' THEN SUM(case when YEAR= YEAR(getdate()) then Credit end)* MONTH(GETDATE())/12 END Accrued
from tblTransactions
group by LeaveType, EmpCode


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 12/11/2013 [Member] Starter | Points: 25

Up
0
Down
You've missed out on my question. The LeaveTypes are unknown and hence cant use case when type='AL' etc. But thanks anyways.......

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/11/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
What about this?

select t.LeaveType, EmpCode, SUM(case when YEAR= YEAR(getdate()) then Credit end) ThisYearCredit
, CASE WHEN CarryForward = 'True' THEN SUM(Credit) - SUM(CASE WHEN Year <= YEAR(GETDATE()) THEN Debit END)
WHEN CarryForward = 'False' THEN SUM(case when YEAR= YEAR(getdate()) THEN Credit-debit END) END AS TotalBalance
,SUM(case when Accruable = 'True' AND YEAR= YEAR(getdate()) then Credit end)* MONTH(GETDATE())/12 AS Accrued
from tblTransactions t
JOIN tblLeaveType lt ON t.LeaveType = lt.LeaveType
group by t.LeaveType, EmpCode, CarryForward


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/23/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
"Mark as answer " if you the above post is helped U.....
If NOT and sorted out by yourself, post us back the solution

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response