Message Boards Message Boards

1 Reply
0 Total Likes
View groups...
Share this post:

How to substitute a year in a SQL statement

Posted 9 years ago

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

spreport ProfitAndLossStandard show AccountFullName, AccountListId, AccountType, RowData, RowDataType, RowType,Label, ,Amount1 as Amount1,Amount2,Amount3,Amount4,Amount5,Amount6, Amount1Title 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 amount1 ... amountxxx 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. amount1, amount2 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

POSTED BY: David Kerr

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?

POSTED BY: Hans Michel
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
or Discard

Group Abstract Group Abstract