Sql Query to calculate Salary till particular month

Question

I have as table as given below:

EmpID  SalaryMonth Salary
1       January    20000
1       February   15000
1       March      25000

I need to write a query to calculate salary till each month and output will be as given below. Each month salary will be added till last month salary.

EmpID  SalaryMonth Salary
1       January    20000
1       February   35000
1       March      60000

I tried solution using cursor but do we have any other solution by which we can achieve this.

@JackyMontevirgen Look above at the second formatted table. This is the expected output, a rolling sum of salaries. — Tim Biegeleisen, Mar 03 '17 at 05:56
@TimBiegeleisen Ahh I see. Thought its the same as sample the sample Data. — Jacky Montevirgen, Mar 03 '17 at 06:09
Check comments on this link https://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx I hope this might help. — Rachit, Mar 03 '17 at 05:38

score 1 · Answer 1 · answered Mar 03 '17 at 05:44

1

Try this !

select EmpID,SalaryMonth,SUM(Salary) OVER(ORDER BY EmpID desc 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS Salary
  FROM t

Demo

answered Mar 03 '17 at 05:44

vhadalgi

7,027
6
38
67

This is the answer assuming you are using a newer version of SQL Server (IIRC 2012 forwards) – JP. Mar 03 '17 at 05:46
This won't work because you have no ordering for the dates. Hence, I expect the rolling sum to be meaningless. – Tim Biegeleisen Mar 03 '17 at 05:49
@vijaykumar Hadalgi The user may wants to sum the salaries by month wise based on different empid.your query will not group the salaries based on empid. – Mahesh.K Mar 03 '17 at 05:58
Yes i can't try as i don't have updated version. So looking for some general solution. It will work for me even if it works for single ID . – user1770461 Mar 03 '17 at 10:00

score 0 · Answer 2 · edited May 23 '17 at 12:09

0

Please use "cumulative-sum" Like

select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum from @t t1 inner join @t t2 on t1.id >= t2.id group by t1.id, t1.SomeNumt order by t1.id

Source: how to get cumulative sum

edited May 23 '17 at 12:09

Community

1
1

answered Mar 03 '17 at 05:46

Singh Kailash

621
5
16

Mahesh.K · Answer 3 · 2017-03-03T06:02:51.580

0

Try This it will help you

select * into #emp_sal from
(
select 1 EmpID      ,'January'as SalaryMonth,    20000 Salary
union all
select 1   , 'February',   15000
union all
select  1       ,'March',      25000
union all
select  2       ,'January',      30000
union all
select  2       ,'February',      8000
)as a

select EmpID,  SalaryMonth ,Salary,
sum(Salary)over(partition by empid order by SalaryMonth rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)as Actual_Salary 
from #emp_sal

edited Mar 03 '17 at 06:02

answered Mar 03 '17 at 05:50

Mahesh.K

901
6
15

As given above as well these methods are available on newer version and i have sql server 2008. So looking for some general solution – user1770461 Mar 03 '17 at 10:03

Tim Biegeleisen · Answer 4 · 2017-03-03T05:58:38.860

Disclaimer: You should seriously consider representing the SalaryMonth using a standard SQL Server date or timestamp column. Using string month names creates all sorts of problems, hence my verbose answer below.

My approach is to use a CTE to assign a numerical position to each month name (cte1). Then, I join this to your original table (cte2). Finally, we can do a self join of cte2 to compute the rolling salary over the course of the table.

There is probably a way to use the month names to create a date object of some sort, but I couldn't think of it, and in any case doing so would probably require assigning a dummy year, and possibly other things.

WITH cte1 AS (
    SELECT 'January' AS SalaryMonth, 1 AS MonthPos
    UNION ALL
    SELECT 'Februrary', 2
    UNION ALL
    SELECT 'March', 3
    UNION ALL
    SELECT 'April', 4
    UNION ALL
    SELECT 'May', 5
    UNION ALL
    SELECT 'June', 6
    UNION ALL
    SELECT 'July', 7
    UNION ALL
    SELECT 'August', 8
    UNION ALL
    SELECT 'September', 9
    UNION ALL
    SELECT 'October', 10
    UNION ALL
    SELECT 'November', 11
    UNION ALL
    SELECT 'December', 12
),
WITH cte2 AS (
    SELECT t1.EmpID,
           t1.SalaryMonth,
           t2.MonthPos,
           t1.Salary
    FROM yourTable t1
    INNER JOIN cte1 t2
        ON t1.SalaryMonth = t2.SalaryMonth
)

SELECT t1.EmpID,
       t1.SalaryMonth,
       SUM(t2.Salary) AS Salary
FROM cte2 t1
INNER JOIN cte2 t2
    ON t1.MonthPos >= t2.MonthPos
GROUP BY t1.EmpID,
         t1.SalaryMonth

Sql Query to calculate Salary till particular month

4 Answers4