Help Needed For T-sql Query

Posted by Sharpcnet under Sql Server on 12/10/2013 | Points: 10 | Views : 1016 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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#

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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


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

0	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

0	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

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

0	"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

Latest Posts