SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 CUMULATIVE AMT REQUIRED BASED EMP_NO
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kond.mohan
Posting Yak Master

India
189 Posts

Posted - 06/27/2013 :  11:00:42  Show Profile  Reply with Quote

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

India
52309 Posts

Posted - 06/27/2013 :  11:03:56  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000