1gascan 1gascan - 4 months ago 7
SQL Question

SQL Query to return the sum of balances from 1 or more rows from the same table

My first post on stackoverflow - I hope you can assist this newbie please!

I have a requirement to return the sum of Leave Balances from 1 or more rows in the same table in SQL Server 2012. The result set must be grouped by EmployeeID and BalanceStartDate. There are instances where an employee has multiple LeaveType's, and there are instances where employees only has one leave type.

If only one LeaveType exists for the BalanceStartDate and Employee, then return the LeaveBalance. If multiple exist, sum the LeaveBalance across the LeaveType and return 1 result.

My source data on the table is as follows:

EmployeeID BalanceStartDate LeaveCategory LeaveType LeaveBalance
---------- ---------------- ------------- --------- ------------
1 01-JAN-2016 ANNUAL MANDATORY 2
1 01-JAN-2016 ANNUAL NON-MAN 3
1 01-JAN-2015 ANNUAL MANDATORY 5
1 01-JAN-2015 ANNUAL NON-MAN 2
2 01-JAN-2016 ANNUAL MANDATORY 6
2 01-JAN-2015 ANNUAL MANDATORY 3
2 01-JAN-2014 ANNUAL MANDATORY 1
2 01-JAN-2014 ANNUAL NON-MAN 1


My expected result set is:

EmployeeID BalanceStartDate LeaveCategory Sum
---------- ---------------- ------------- ---
1 01-JAN-2016 ANNUAL 5
1 01-JAN-2015 ANNUAL 7
2 01-JAN-2016 ANNUAL 6
2 01-JAN-2015 ANNUAL 3
2 01-JAN-2014 ANNUAL 2


So for each "year", we should have a unique row summing up the balance across the LeaveTypes (if more than one exists). If there is only 1 LeaveType, then only return the Leave Balance.

I wrote the following (which is almost there), but it is excluding rows where only 1 LeaveType exists, and is still returning 2 rows for a single year:

select A.LeaveBalance + B.LeaveBalance as 'Sum', A.EmployeeID
From
Table A
Inner Join Table B On A.EmployeeID = B.EmployeeID
AND A.LeaveCategory = 'ANNUAL'
AND A.LeaveCategory = B.LeaveCategory
AND A.BalanceStartDate = '01-JAN-2016'
AND A.BalanceStartDate = B.BalanceStartDate
AND A.EmployeeID = '12345'
AND A.LeaveType <> B.LeaveType


I hope this is enough Info?
Any assistance would be greatly appreciated. Please excuse my newbie code!

Answer
select  
    EMPLOYEEID  
    ,BALANCESTARTDATE  
    ,LEAVECATEGORY  
    ,SUM( LEAVEBALANCE ) as sum  
from  
    EMPLOYEES  
group by  
    EMPLOYEEID  
    ,BALANCESTARTDATE  
    ,LEAVECATEGORY  
order by  
    EMPLOYEEID  
   ,BALANCESTARTDATE desc;  

It will give result as you expected.