CUMULATIVE AMT REQUIRED BASED EMP_NO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CUMULATIVE AMT REQUIRED BASED EMP_NO

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-06-27 : 11:00:42

hi all
i have created cte for calculating cumulative budget in ssms 2008 but i got the out put below manner
131 2013-01 13 7.036400 13
131 2013-02 13 6.758130 26
131 2013-03 13 16.188320 39
131 2013-04 13 18.601500 52
131 2013-05 13 7.685010 65
131 2013-06 13 10.079660 78
175 2013-01 13 10.142280 91
175 2013-02 13 23.531130 104

with cte
as
( select #temp4.MONTHYEAR,#temp4.AVGBUDGET,#temp3.ACHIVEMENTASONDATE/100000 ACHIVEMENTASONDATE,
LEFT(((#temp3.ACHIVEMENTASONDATE/100000)/nullif(#temp4.AVGBUDGET,0)*100),5) "%OFACHIVEMENT",
#temp3.EMP_NO from #temp3 inner join #temp4 on #temp3.EMP_NO=#temp4.EXECUTIVE
and #temp3.INVOICEDATE=#temp4.MONTHYEAR ---where #temp3.EMP_NO in (725,719)
-- where #temp4.MONTHYEAR between '2013-01' and '2013-04'
group by #temp4.monthyeAR,#temp4.AVGBUDGET,#temp3.ACHIVEMENTASONDATE,#temp3.EMP_NO,#temp4.EXECUTIVE
)
select emp_no, monthyear,avgbudget,ACHIVEMENTASONDATE,sum(avgbudget) over (order by emp_no,monthyear) as cum from cte
order by emp_no

but i need to get the cumulative based on employee wise
if anyone knows explain me the logic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 11:03:56

[code]
with cte
as
( select #temp4.MONTHYEAR,#temp4.AVGBUDGET,#temp3.ACHIVEMENTASONDATE/100000 ACHIVEMENTASONDATE,
LEFT(((#temp3.ACHIVEMENTASONDATE/100000)/nullif(#temp4.AVGBUDGET,0)*100),5) "%OFACHIVEMENT",
#temp3.EMP_NO from #temp3 inner join #temp4 on #temp3.EMP_NO=#temp4.EXECUTIVE
and #temp3.INVOICEDATE=#temp4.MONTHYEAR ---where #temp3.EMP_NO in (725,719)
-- where #temp4.MONTHYEAR between '2013-01' and '2013-04'
group by #temp4.monthyeAR,#temp4.AVGBUDGET,#temp3.ACHIVEMENTASONDATE,#temp3.EMP_NO,#temp4.EXECUTIVE
)
select emp_no, monthyear,avgbudget,ACHIVEMENTASONDATE,sum(avgbudget) over (partition by emp_no order by monthyear) as cum from cte
order by emp_no
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources