Avgbudget logic - SQL Server Forums

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)

Avgbudget logic

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-06-07 : 09:30:44

Hi all
we have created query in SSMS 2012 for analysing Budget values
i have created query i got below output

monthyear avgbudget avgbudgetasondate
jan-13 15.58 1.76
feb-13 15.58 0.58
mar-13 15.33 3.16
apr-13 15.34 1.67

but i need this format of analysatoin

jan-13 15.58 13.82(15.58-1.76)
feb-13 29.40(15.58+13.82) 28.82(29.40-0.58)
mar-13 44.15(28.82+15.33) 40.99(44.15-3.16)
apr-13 56.33(40.99+15.34) 54.66(56.33-1.67)

is there anyway to fetch the above output.pls guide on this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 10:44:53

can you show your current query?

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

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-06-07 : 10:56:45

Hi visakh
thks for reply.pls find the below copied query for the same
Select * into #temp3 from (
select distinct sum(zt.totalvalue) ACHIVEMENTASONDATE,
zt.emp_no EMP_NO , convert(varchar(7),zt.invoicedate,120) INVOICEDATE
from sap_dwh..ZSSR_REPORT_TABLE zt group by zt.emp_no,convert(varchar(7),zt.invoicedate,120)) kt
--------------------------------budgetfile---------------------
select * into #temp4 from (select sum(value) AVGBUDGET, bd.executive AS EXECUTIVE ,
convert(varchar(7),bd.[yearmonth],120) MONTHYEAR
from sap_dwh..JAnmar1 bd group by bd.[yearmonth],bd.executive) kt2
;with cte
as
(select #temp4.MONTHYEAR,#temp4.AVGBUDGET,#temp3.ACHIVEMENTASONDATE/100000 ACHIVEMENTASONDATE,
LEFT(((#temp3.ACHIVEMENTASONDATE/100000)/#temp4.AVGBUDGET)*100,5) "%OFACHIVEMENT",
#temp3.EMP_NO from #temp3 inner join #temp4 on #temp3.EMP_NO=#temp4.EXECUTIVE
and #temp3.INVOICEDATE=#temp4.MONTHYEAR
where #temp4.MONTHYEAR between '2013-01' and '2013-04'
)
select
CONCAT( CASE WHEN RIGHT( MONTHYEAR,2)=01 THEN 'Jan'
WHEN RIGHT( MONTHYEAR,2)=02 THEN 'Feb'
WHEN RIGHT( MONTHYEAR,2)=03 THEN 'Mar'
WHEN RIGHT( MONTHYEAR,2)=04 THEN 'Apr' ELSE '' END,'-',
RIGHT( LEFT(MONTHYEAR,4),2)) AS MONTHYEAR ,
LEFT(AVGBUDGET,5) as"Avg.Budget",LEFT(ACHIVEMENTASONDATE,4) as"Achmt as on Dt",[%OFACHIVEMENT] as"% Of Achmt", case when right(monthyear,2) ='01' then left( (cast(ACHIVEMENTASONDATE-ACHIVEMETNASDATEYEAREWISE as float)/12 )*100,5)
when right(monthyear,2) ='02' then left((cast(ACHIVEMENTASONDATE-ACHIVEMETNASDATEYEAREWISE as float)/11 ) *100,6)
when right(monthyear,2) ='03' then left((cast(ACHIVEMENTASONDATE-ACHIVEMETNASDATEYEAREWISE as float)/10)*100,6)
when right(monthyear,2) ='04' then left((cast(ACHIVEMENTASONDATE-ACHIVEMETNASDATEYEAREWISE as float)/09 )*100,6)
when right(monthyear,2) ='05' then left ((cast(ACHIVEMENTASONDATE-ACHIVEMETNASDATEYEAREWISE as float)/08)*100,6) end "Req.Avg to Ach Budget",
AVGBUDGETYEARWISE as"Budget",ACHIVEMETNASDATEYEAREWISE as"Achvmt",[%ACHVIVEMENTYEARWIS] AS"%OF Achmt"
---ed.Area_Covered,ed.Branch,ed.Division,ed.Budget_PA,ed.Budget_PM as Period,ed.Place_of_Posting,ed.Reporting_to_1st_Level,ed.Segment
from cte c cross apply(select LEFT(sum(avgbudget),5) AVGBUDGETYEARWISE
,LEFT(SUM(ACHIVEMENTASONDATE),5) ACHIVEMETNASDATEYEAREWISE,
LEFT(SUM(ACHIVEMENTASONDATE)/sum(avgbudget)*100,5) '%ACHVIVEMENTYEARWIS'
from cte where replace(monthyear,'-','')*1<=replace(c.MONTHYEAR,'-',''))c1
inner join SAP_DWH..EMPLOYEE_DETAILS ed on ed.EmpID=EMP_NO
where emp_no=725
ORDER BY AVGBUDGETYEARWISE

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-06-07 : 11:33:49

Hi vishak

I am Really sorry.for small modification
right now we have these 3 fieds as per the Shared query.
monthyear Avgbudget achivementasondate
jan-03 15.58 1.76
feb-03 15.58 0.58
mar-03 15.33 3.16
apr-03 15.34 1.67

we need output as this format
Monthyear Avgbudget Requiredachidvebudget
jan-01 15.58 13.82(15.58-1.76)
feb-03 29.40(15.58+13.82) 28.82(29.40-0.58)
mar-03 44.15(28.82+15.33) 40.99(44.15-3.16)
apr-03 56.33(40.99+15.34) 54.66(56.33-1.67)

pls guide me on this outpue

mohan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-07 : 11:47:39

hmm..how did year go behind?

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