question

bhanupratapsngh9 avatar image
bhanupratapsngh9 asked

Group Calculation in sql

Respected Geeks, I am having some issues with a flow as i am new to sql server i have a table create table objective ( csid int not null, memid tinyint not null, ObjtId tinyint not null, ObjNm varchar(50) not null, Freq tinyint not null, ObjAmt money not null ) create table objectiveRslt ( csid int not null, memid tinyint not null, ObjtId tinyint not null, TtlObj money not null ) I have to store result for every memid after an event from front end what would be appropriate action to perform it. Thanks in advance. [edited to move code from comments to question] Full table definitions: CsObjective create table csobjective ( CsId int , ObjtId tinyint , Memid tinyint , ObjName varchar(500) , Priority tinyint , StartYr smallint , EndYr smallint , PrstCost money , LoanFctr money , LoanTrm tinyint , AstId smallint , AstAmt money , Remark varchar(100) , Oth varchar(2) ) CSBPObjective create table csBPobjective ( CsId int , ObjtId tinyint , MemId tinyint , Priority tinyint , ObjName varchar(500) , StartYr smallint , EndYr smallint , PrstCost money , LoanFctr money , LoanTrm decimal(18, 4) , AstTypId smallint , AstAmt money , Remark varchar(100) , RtonInv money , Inflid tinyint , Yrs2Goal decimal(18, 2) , FCGoal money , LoanAmt money , ReqFund money , RetId tinyint , Lumpsum money , YrlySameInv money , YrlyContrSGrth money , ValPrvsnMade money , NetReqFund money , TaxRaOfApplicnt decimal(5, 2) , EffRtofRtn decimal(18, 2) , ) client saves and updates data in csobjective table Employee takes an action that stores data in csbpobjective so how may i accomplish his task there is also another table which stores data of sum of some csbpobjective table according to csid composite key is csid+objtid in both tables i want to write a procedure which will brun when employee will click abutton and pass a value from procedure procedure is as - declare @Yr2Goal decimal(5,2), @Infla decimal(5,2), @AstRtnVal decimal(5,2), @SalGrwth decimal(5,2), @FCofGoal decimal(18,2), @ValOfPrvMad decimal(18,2), @EffRat decimal(12,2), @LoanAmt decimal(18,2), @ReqFund decimal(18,2), @NetReqFund decimal(18,2), @Lumpsum decimal(18,2), @YrlyInvwithSalGrwth decimal(18,2), @MemTax decimal(5,2)=20.00, @PrVal decimal(18,2)=40000, @StYr varchar(4)=2004, @AstAmt decimal(18,2)=26000.36, @LoanFctr decimal(5,2)=12 --years to goal SELECT @Yr2Goal=isnull((DATEDIFF(month, convert(smalldatetime, '01/01/'+@StYr),dbo.getdateist(1) )/12),0.00) --set @Yr2Goal=isnull(cast(cast(datediff(month,dbo.getdateist(0),(SELECT CAST(CAST(2004 AS VARCHAR(4)) +'01'+'01'AS DATETIME))) as decimal(5,2))/12 as decimal(18,2)),0.00) --Future cost of goal select @Infla=isnull(RtnVal,0.00) from csbpasmptn where asmptnid=18; set @FCofGoal=dbo.GetFV(@PrVal,@Yr2Goal,@Infla) --Effective Return select @AstRtnVal=isnull(RtnVal,0.00) from csbpasmptn where AssetId=/*Asset*/3; set @EffRat=(@AstRtnVal*(1-(@MemTax/100))) --value of Provison Made set @ValOfPrvMad=dbo.GetFV(@AstAmt,@Yr2Goal,@EffRat) --Loan Amount set @LoanAmt=((@FCofGoal-@ValOfPrvMad)*@LoanFctr/100) --Required Fund set @ReqFund=@FCofGoal-@LoanAmt --Net Required Fund set @NetReqFund=@FCofGoal-@ValOfPrvMad-@LoanAmt --Lumpsum set @Lumpsum=dbo.getPV(@NetReqFund,@Yr2Goal,@AstRtnVal) --Yrly Investment with Salary Growth select @SalGrwth=isnull(RtnVal,0.00) from csbpasmptn where Asmptnid=17; --sp_helptext GetPMTwithGrowth(contri,year,cg,r,target) select @YrlyInvwithSalGrwth= PMTGrowth from dbo.GetPMTwithGrowth(100,@Yr2Goal,@SalGrwth,@AstRtnVal,@NetReqFund)
calculations
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

When the employee clicks the button, the application should call the stored procedure and the stored procedure should calculate all of its variables then use them to insert a row into `CSBPObjective`?
2 Likes 2 ·
can the front end just insert a row into objectiveRslt "after an event" could you clarify the question a bit?
1 Like 1 ·
no dear Kenj , I will insert record in Objective table only.
0 Likes 0 ·
Okay. Can the front end just insert a row into Objective "after an event" This probably sounds similar to my first question/comment, but it's not entirely clear what you are trying to accomplish or even how the few pieces you have mentioned fit together.
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.