How to substitute a year in a SQL statement

Questions
,
1

I have a query that gets sent to a ODBC database in Quickbooks

sp_report ProfitAndLossStandard show AccountFullName, AccountListId, AccountType, RowData, RowDataType, RowType,Label, ,Amount_1 as Amount_1,Amount_2,Amount_3,Amount_4,Amount_5,Amount_6, Amount_1_Title as “As of” parameters DateFrom={d’2005-01-01’}, DateTo = {d’2010-12-31’} , SummarizeColumnsBy = ‘Year’

This returns 6 columns since the parameters are asking for years. So depending on the date parameters the column amount_1 … amount_xxx changes in number. In this case it would return 6 columns as there are 6 years 2005,2006,2007,2008,2009,2010.

What I would like to do is be able to make a function that depending on the number of years adds in the appropriate number of columns - e.g. amount_1, amount_2 to the correct number depending on year and would have to change the year part of the date d’year-12-31’

So I am thinking there must be someway that you could have a function that would add the correct number to the year and the correct number of amount_xx column. So if the beginning year were 2005 and went to 2008
you could make a function that would substitute the year in d’year-01-01 and d’year-12-31
Or
Input beginning year
Input end year

Substitute the year in the " d’year-01-01 "
and add the correct number of amount columns in the SQL statement that gets passed to the ODBC interface.

I am new to Mathematica and would appreciate some thoughts on how to do this.
Thanks

2

Can you connect to QuickBooks using Mathematica DatabaseLink? If you can connect the remainder of the question is generating the SQL commands as string to get executed on database system. Something like:

SQLExecute[conn, “spreport ProfitAndLossStandard show AccountFullName, AccountListId, AccountType, RowData, RowDataType, RowType, Label, Amount1 as Amount1, Amount2, Amount3, Amount4, Amount5, Amount6”];
So first make certain you can connect using DatabaseLink: Mathematica to QuickBooks. Otherwise you are asking how to generate a this string given a range of n years will yield the string “Amount1 as Amount1, Amount2 as …, Amountn”. But with no successful connection?