July 30, 2015 at 6:32 am
Help please I am very new to SQL Server and I can't get the following right.
There is one table in a current database with the following fields:
DealNo, InvestmentDate, Amount, InterestRate, ExpiryDate.
I need to create a temporary table with the following fields: Date. AmountInvested, InterestAmount, EndingValue. The new table must be daily dates.
Please help
July 30, 2015 at 7:43 am
Could you post sample data and expected results?
I have an idea of what you want, but I'm not completely sure. If possible follow the advice of this article to post the data: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
July 30, 2015 at 9:37 am
Luis Cazares (7/30/2015)
Could you post sample data and expected results?I have an idea of what you want, but I'm not completely sure. If possible follow the advice of this article to post the data: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
to echo what Luis says.....it will be easier for us to give you a tested answer if you can provide the sample data
as you are new here.....please see below as as an example of what helps us to help you
USE tempdb;
IF OBJECT_ID('tempdb..MyTable', 'U') IS NOT NULL DROP TABLE tempdb..MyTable ;
CREATE TABLE MyTable(
DealNo INTEGER NOT NULL PRIMARY KEY
,InvestmentDate DATETIME NOT NULL
,Amount NUMERIC(5,2) NOT NULL
,InterestRate NUMERIC(8,6) NOT NULL
,ExpiryDate DATETIME NOT NULL
);
SET NOCOUNT ON ;
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (1,'2014-09-06 00:00:00.000',50.45,0.080200,'2017-11-07 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (2,'2014-08-13 00:00:00.000',36.04,0.049400,'2016-07-14 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (3,'2014-01-31 00:00:00.000',89.19,0.029900,'2016-01-24 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (4,'2014-10-23 00:00:00.000',28.73,0.066100,'2017-05-31 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (5,'2014-08-13 00:00:00.000',28.22,0.050900,'2016-07-07 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (6,'2014-05-17 00:00:00.000',31.06,0.010700,'2017-01-19 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (7,'2014-07-07 00:00:00.000',56.32,0.096400,'2016-01-17 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (8,'2014-12-02 00:00:00.000',86.84,0.019500,'2016-12-28 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (9,'2014-11-10 00:00:00.000',75.15,0.045000,'2016-08-17 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (10,'2014-01-12 00:00:00.000',75.18,0.079000,'2016-05-14 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (11,'2014-05-11 00:00:00.000',82.37,0.051600,'2016-03-09 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (12,'2014-08-17 00:00:00.000',76.82,0.027400,'2016-04-18 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (13,'2014-08-31 00:00:00.000',18.54,0.085700,'2017-11-27 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (14,'2014-04-25 00:00:00.000',95.28,0.023500,'2016-01-20 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (15,'2014-09-30 00:00:00.000',81.06,0.038300,'2016-06-22 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (16,'2014-11-07 00:00:00.000',50.18,0.050900,'2017-02-22 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (17,'2014-10-07 00:00:00.000',69.63,0.013500,'2017-06-14 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (18,'2014-07-26 00:00:00.000',82.30,0.094000,'2016-01-08 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (19,'2014-06-13 00:00:00.000',38.66,0.050000,'2017-09-03 00:00:00.000');
INSERT INTO mytable(DealNo,InvestmentDate,Amount,InterestRate,ExpiryDate) VALUES (20,'2014-02-06 00:00:00.000',97.26,0.049300,'2017-09-17 00:00:00.000');
SET NOCOUNT OFF ;
I am reasonably sure that what you require is achievable.....one possible area that require clarification is how you are storing the "interest rate".....is that annual/monthly/daily etc and also please explain the calculation rules to be applied.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 31, 2015 at 1:45 am
Thank you so much this helps a lot.:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply