July 19, 2013 at 3:57 am
ClientFundingTotal Commissioned ValuePayment Frequency
111004 weeks
221584 weeks
332544 weeks
44364 weeks
55214 weeks
My solution to the problem of creating a funding stream is to create a new table with two variable and one fixed data columns.
Increment – This is determined by the Payment Frequency value within the Funding Details table
Date – This shows the days on which funding payments are received. It is determined by adding the Increment value to the Start Date for each record.
Income – This is a fixed value taken directly from the Funding Details table (Total Commissioned Value).
Client 1 Funding Stream
IncrementDateIncome
101/04/2013100
2829/04/2013100
5526/05/2013100
8222/06/2013100
10919/07/2013100
13615/08/2013100
16311/09/2013100
19008/10/2013100
21704/11/2013100
24401/12/2013100
27128/12/2013100
29824/01/2014100
32520/02/2014100
35219/03/2014100
37915/04/2014100
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FundingReportTable]') AND type in (N'U'))
DROP TABLE [dbo].[FundingReportTable];
declare @date datetime = '20100101'
CREATE TABLE [DBO].[FundingStream]
(
Id_num INT IDENTITY(1,28),
Date DATETIME NOT NULL,
Income NUMERICAL (5,2)
)
INSERT INTO [FundingStream]
(
Date,
Income
)
SELECT
(
[SELECT dateadd(dd,id_num,'20100101') FROM master..spt_values WHERE dateadd(dd,id_num,'20100101')<dateadd(yy,1,@date)],
[FundingDetails].[Total Commissioned Value]
)
FROM FundingDetails;
When this code is run I get the following error message ….
Microsoft Server error 102.
can anyone help?
July 19, 2013 at 8:31 am
Your post is extremely unclear but you are getting a 102 (Syntax error) because of your select statement at the end.
SELECT
(
[SELECT dateadd(dd,id_num,'20100101') FROM master..spt_values WHERE dateadd(dd,id_num,'20100101')<dateadd(yy,1,@date)],
[FundingDetails].[Total Commissioned Value]
)
FROM FundingDetails;
I can't even begin to figure out what you are trying to do there. You have a select statement that has no columns or a from.
SELECT
(
Then you have the inner portion that sort of looks like a subselect but the syntax is wrong. Not sure what the [] is for here but that won't work. Even if you removed the [] you then have a query that has a where clause followed by a comma.
If you can explain what that query is trying to do maybe we can help you figure out how to fix this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 19, 2013 at 9:09 am
As Sean said we are not cleared what you are trying to achieve and your Select statement is also incorrect as you have nt specified any column names.....
Can you please provide us more information with the resultant output
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply