SQL Server :: Calculate A Compound Interest For Each Row Based On The Previous Row?
Sep 12, 2010
I would like to calculate a compound interest for each row based on the previous row.
My table Table1 :
index sum quantity sum_after_interest
1 100 5 500(??)
2 500(??) 2 1000 (??)
3 1000(??) 3 3000 (??)
I marked in question marks the columns that I want to calculate, where I only have the sum for the first row and the quantity for all the rows.
Can this be done using views and inner joins or nested selects ?
View 1 Replies
Similar Messages:
Mar 23, 2011
If I have records in a table in this order:
Id - Date - Type
1 - 2011-03-01 - 1
2 - 2011-03-02 - 0
3 - 2011-03-03 - 0
4 - 2011-03-04 - (-1)
5 - 2011-03-04 - 0
Can I somehow select the records between a record having type 1 and type -1 without using a cursor,for example the records with ids 1-4 in this example?
View 9 Replies
Apr 11, 2014
I have a table where each month I need to find the SkipUtgang value of current month (ex: 779) and the value of SkipUtgang in previous month (ex: 788); then Sum the two values; and finally multiply the result with the number of day of current month (ex: 31). as example, I have the below table:
How to script the SQL for this scenario?
View 1 Replies
May 23, 2010
I am doing small banking project in that i want to create calculations on interest. in which i have to create that in stored procedures or userdefined funcations
View 1 Replies
Dec 22, 2010
The above is my gridview item template, I have used this javascript for row total
[Code]....
gridview row databound code behind
[Code]....
The above code is getting row calaculated on key up. With the same code in need column total also. I need total of column quantity and total in their respective footer textbox.
View 16 Replies
Nov 8, 2010
I had just started with Visual Studio 2008 + VB.net. I've now designed my tables, and basic gridview functions properly.Now I'm faced with the problem of having to calculate a value based on 2 - 3 columns/data in the gridview, display the result in an existing column in the same gridview, and saving the calculated value in the datasource.I had initially created the "Total" column as a textbox displaying a manually calculated amount that is saved in the datasource. Now, I want to change the field to auto-calculate based on 2 other columns.
View 4 Replies
Jul 17, 2015
I have 3 Textboxes in my Web page. When user key in value in these 3 textboxes,their result should show on a "Label"
1) Gross Amount
2) Taxable Amount
3) Tax Amount
I have to write a Stored Procedure for calculating the value of above textboxes.
Condition is:
If Gross Amount is > or < 0.00, then Taxable and Tax amount should calculate based on below formula:
@TaxableAmt = @GrossAmt / @Rate
@TaxAmt = @GrossAmt - @TaxableAmt
but If Taxable Aount is > or < 0.00, then Gross and Tax amount should calculate based on below forumla:
@GrossAmt = @TaxableAmt * @Rate
@TaxAmt = @GrossAmt - @TaxableAmt
and for "Tax Amount" I have 2 different conditions:
If Tax Amount is = 0.00, then Taxable and Gross amount should calculate based on below formula:
@TaxableAmt = @TaxAmt / (@Rate-1)
@GrossAmt = @TaxAmt + @TaxableAmt
but If Tax Amout is <> 0.00, then whatever the value of Tax Amount is, it should key-in/show as it is.
i.e., @TaxAmt = @TaxAmt
For this requirement, I wrote below SP:
ALTER PROCEDURE [Invoice].[usp_tbl_TaxCode_Update]
-- Add the parameters for the stored procedure here
@TaxCodeID nvarchar(50) ,
@GrossAmt decimal(18, 2),
@TaxableAmt decimal(18, 2),
@TaxAmt decimal(18, 2)
[code]...
But its not working as per required.
View 1 Replies
Oct 7, 2012
in my asp.net_vb web there is a text box (textbox1) in which a date is dispalyed from database (eg 21-Mar-2012) in another text box (textbox2) i want to display
if the date is less than 30 Jun then the textbox2 should display 1 Jul+the year of textbox1
if the textbox1 date is greater than 30 Jun the text box2 should display 1 Dec + the year of textbox1
my web is in asp.net and code is in vb
View 1 Replies
Jul 2, 2010
GridViewDetails uses templatefields. In edittemplate I put a button called "ButtonUpdateAdjustAmount" with OnClick="Update_AdjustAmount" in the EditItemTemplate for the "Amt to be Adj" column. There is also a textbox. My user's should be able to edit the textbox, but I also want the ability to click the button and the textbox populated with the correct calculated value. The formula varies based on the "code" column which is a dropdownlist called "DropListCode".
I am fairly new to asp.net, vb, etc...so forgive my code writing. Eventually I need to also put the same functionality on the insertrow for my gridview. But first wanted to get the edit one working. I am using templatefields for all columns and the FooterTemplate to perform the Insert New Detail Line.
I can get the functionality to work for the first data row and only if I take out the If code.SelectedValue = ... line and leave 1 static formula. However that isn't going to work. If DropListCode.SelectedValue is DNR then AdjAmt should be ((q_ordered - q_received) * cost). If DropListCode. SelectedValue is OV then AdjAmt should be (RKNumber * cost). If DropListCode.SelectedValue is NB the AdjAmt should be ((q_received - q_ordered) * cost). If DropListCode.SelectedValue is DAM then AdjAmt should be (RKNumber * cost)
Code behind for the OnClick:
[Code]....
.aspx page - GridViewDetails programming:
[Code]....
View 6 Replies
Mar 2, 2010
I'm getting up to speed with MVC. In all examples that I find, the database tables always seem to have a single autoincrement column as the key. Is this the standard for DB Design when the intent is to use MVC or can MVC cope with compound primary keys that dont necassarily have autoincrement?
Typical DB Design:
[code]....
It's just that obviously in the real world many companies already have a good DB design populated with data based on the classic design style. If I get clients contemplating updating their applications and want to use the MVC framework, will they also need to consider the redesign of the DB?
If MVC can cope with compound key's are there any good examples. I thought that by using binding exclude/include that compound keys are possible, but it wasn't clear to me.
View 1 Replies
Feb 7, 2010
I have recently started to examine asp.net mvc. I have studied quite a few examples and common to these are that they contain quite simple scenarios, where a view will map to either an instance of a type in the model or a list of a paritcular type from the model.
I'm looking for guidelines to compose/composite views. In the long term I would like Ajax to be part of the equation, but for now I'm looking for a simpler non Ajax setup.
The problem:
Here is a description of a contrieved problem. A domain model contains the types A and B.
class A
{ String ID, String Name, int Age
List<B> ListOfB
}
class B
{ String ID, String Name, String Url}
[Code]....
Since the solution has to allow for different combinations of data and functionality, I think it would be smart to have a parent view (not to be confused with a masterpage) that contained placeholders for parital views. Then, the parent views controller could make up the right combinations of model data and partial views and feed these to the parent view.
Finally; the questions:
Is my way of thinking in accordance with the asp.net mvc methodology? Can you explain/show (not necessarily all of it) how the controller can compile the right combination of partial views and feed these to the parent view?
View 1 Replies
Jan 8, 2010
i have some confusion using compund from in linq.I know the concept of compound from but in database table how can we use.can anyone give me example of it.
View 3 Replies
Jul 11, 2013
if I google it I get things like "you could try..." or "that doesn't work if...", etc. and I want some tried and true way. So, have you written any age-calculation routines and found they actually work when put to the test (that would be, when they go live ) or simply found one on the Internet?
I tried googling "vbforums .net calculate age" and I got some VB6 code. I am thinking there might be something more sophisticated in .NET.
I tried searching "calculate age" in the advanced search here, and got hits like "calculate tax" and "calculate discount" so it's not recognizing my combined keyword string.
View 7 Replies
Mar 18, 2011
I'm trying to progam a strongly typed "Create" view using MVC 3 and razor. I want the user to be able to select a customer from a dropdown list (I populated this from my database using ViewBag in the controller). When the user has selected a customer I want a separate dropdown list to generate a list of dogs belonging to that customer related by the customerID in the database. The create button on the form will then take both of these values along with the the other fields and save it to the database.
View 1 Replies
Feb 12, 2011
I have used a pattern of compound view models for a few of the forms in this project. It works very well.
In this case I have a VendorAddress view model. I use Address(es) in a few places in this project so I made and Address view model object that I can re-use. Then I desided I want to do a very thorough checking of the state codes and zips so, I desided I would try to use remote validation and check the state and zip code aganst a stored set of tables in the database.
The problem is I am not getting values back to the remote action methods for the fields I'm asking for (in this case State and PostalCode). I believe this is because of the name mangleing that the MVC framework does for compound or sub types for id(s) for the inputs in this case it is making names like 'Address_Line1' and 'Address_State' and 'Address_PostalCode'.
Ultimately the question I have is, is there a way of turning off the pre-pended 'Address_' from the base view model object in a way that MVC framework can put it bak into the object after ther post of the form?
[Code]....
Then in the form after rendering it looks like this:
[Code]....
I get the remote call but there is nothing in the parameters "State" and "PostalCode", so the unobtrusive routines are firing but I think it does not know where to look for the data even though I have type something in the mensioned fields. As you can see in the rendered code the id="Address_State" and the data-val-remote-additionalfields="*.State,*.PostalCode"
View 4 Replies
Jan 6, 2010
my code is calculate to current rowafter update quantity but other rows don't affect and not calculate i need to calculate all rows i think i need to use for loops but i dont know how to do it
[code]....
[Code]....
View 7 Replies
Jul 26, 2010
I am developing a web application in ASP.NET3.5 with C#. I also using Telerik RadAjax Control. When user click the menu then open the page with a new tab(Like web Browser).I want to create, when user back any previous tab(page) the will be show the previous data without loading 2nd time( like browser tab). Is it Possible ?
View 2 Replies
Mar 24, 2011
[Code]....
also what if i want to display all records which are entered in last 20min /30mins i m already havn a coloumn of datetime
View 5 Replies
Aug 29, 2010
this is my table
[IMG][URL][/IMG]
here i m calculating the date and time diff
[IMG][URL][/IMG]
now how i can calculate the total days which took in processing that's by adding both the late days the total late days should be 3.
View 2 Replies
Mar 24, 2011
[Code].... Save previous row value to next row?
View 8 Replies
Sep 3, 2010
my app requires an elapsed time calculation that I have done like this
CONVERT(Varchar, (DATEPART(dd, GETDATE() - DateAdded)-1) * 24 +
DATEPART(hh,GETDATE() - DateAdded)) + 'h:' + CONVERT(Varchar, DATEPART(mi, GETDATE() - DateAdded)) + 'm' AS ElapsedTime
This works.. I can then colour my grid view cells accordingly using the substring function.. the problem is that I have been told that using GETDATE three times is not a good use of resources, is it possible to use the Datediff function to get the same result?
View 3 Replies
Sep 25, 2010
i would like to query out the next record and previous record guid from the current record guid.But i don't want to use the ID + 1 or ID - 1 because the ID is not secure for query like this. I am using MS SQL.
May i know how can i write the sql query?
View 2 Replies
Mar 11, 2011
I had a sql statement to prepare dataset for generate staff overtime work and time-off balance.
SELECT Table1.staff_key,Table1.from_date,Table1.to_date,Table1.total_min ...... FROM Table1
UNION ALL
SELECT Table2.staff_key,Table2.from_date,Table2.to_date,Table2.total_min ...... FROM Table2
the output of the sql statement should like this:
From To No. of hours
total_min Balance
01/01/2011 12:00 01/01/2011 17:00 7 hr 0 min 420
420
07/03/2011 12:00 09/03/2011 14:00 ( 2 hr 0 min ) -120
360
07/03/2011 19:00 09/03/2011 20:30 1 hr 30 min 90
450
10/03/2011 17:00 10/03/2011 17:30 ( 0 hr 30 min ) -30
420
But I have no idea how to calculate the Balance in the sql statement that it will sum up the total_min of previous rows + existing row.
View 6 Replies
Mar 25, 2011
how to make it?? How to read next row and previous row in database / SQL
View 5 Replies
Feb 15, 2011
I have the following SQL:
SELECT SRFILE_1.SRCONMAE, COUNT(LBWF_1.Incidentx) AS SLAFailed,
(SELECT COUNT(Incidentx) AS Expr3
FROM LBWF AS LBWF_2) AS TotalCalls
FROM SRFILE AS SRFILE_1 INNER JOIN
LBWFAS LBWF_1 ON LBWF_1.Incidentx = SRFILE_1.SRONUMBER
WHERE (LBWF_1.KPIFailx = '1') OR
(LBWF_1.Chargex > '0.00') OR
(LBWF_1.LChargex > '0.00')
GROUP BY SRFILE_1.SRCONMAE
What I'm trying to do is divide TotalCalls by SlaFailed and * by 100 into a new variable such as Percentage. However, everytime I try to select SLAFailed or TotalCalls I receive a 'Column Names do not exist' message.
Is there any way I can reference said values?
View 5 Replies
