I have information in some database tables. I would like to do two things (in two separate applications)
1) Expand the information on the tables by adding columns. For example:
A) I have tables with the names AAPL, MSFT and CSCO, all with the same rows: INDEX,OPEN,CLOSE,HIGH,LOW,VOLUME
B) I provide the program with a XML file like this:
_MOVING_AVERAGES
SELECT [INDEX],[OPEN],[CLOSE],HIGH,LOW,VOLUME,
(SELECT AVG([CLOSE]) FROM {1} B WHERE B.[INDEX] > (A.[INDEX] - 5) AND B.[INDEX] (A.[INDEX] - 10) AND B.[INDEX] (A.[INDEX] - 5) AND B.[INDEX] (SELECT AVG([CLOSE]) FROM {1} B WHERE B.[INDEX] > (A.[INDEX] - 10) AND B.[INDEX] < A.[INDEX]) THEN 'YES' ELSE 'NO' END AS MA5OVERMA10
FROM {1} A
MSFT,AAPL,CSCO
C) The program will create 3 new tables MSFT_MOVING_AVERAGES, AAPL_MOVING_AVERAGES, CSCO_MOVING_AVERAGES with the original columns plus MA5, MA10 and MA5OVERMA10. Note that the program needs to iterate through all the values in the VARIABLES.1 tag, and that it must replace the {1} in the query for the different values MSFT, AAPL, CSCO. It also needs to create the table, but I think a SELECT INTO statement would take care of that.
This will be a command line application that takes the following parameters:
///
/// args: (case sensitive)
/// -src:[Path of the XML file]
/// -S:[Db Server Name]
/// -D:[Db Name]
/// -U:[Db User Id]
/// -P:[Db Password]
/// -E ;use Db Integrated Security
2) Another program that will generate some very specific reports that I provide in a similar format to the one in the previous requirement.
a) I provide an XML configuration file like this:
SELECT {4}, B.[OPEN] AS {2}_OPEN_D1, B.[CLOSE] {2}_CLOSE_D1, [login to view URL] AS {2}_HIGH_D1, [login to view URL] AS {2}_LOW_D1, C.[OPEN] AS {3}_OPEN_D1, C.[CLOSE] AS {3}_CLOSE_D1, [login to view URL] AS {3}_HIGH_D1, [login to view URL] AS {3}_LOW_D1, D.[OPEN] AS {4}_OPEN_D1, D.[CLOSE] AS {4}_CLOSE_D1, [login to view URL] AS {4}_HIGH_D1, [login to view URL] AS {4}_LOW_D1 FROM {1} A LEFT JOIN {1} B ON A.[INDEX] = B.[INDEX]+1 LEFT JOIN {2} C ON A.[INDEX] = C.[INDEX] + 1 LEFT JOIN {3} D ON A.[INDEX] = D.[INDEX] + 1
AAPL,MSFT,CSCO
AAPL
MSFT
CSCO
"CASE WHEN ([login to view URL] - [login to view URL]) > .1 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([login to view URL] - [login to view URL]) > .2 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([login to view URL] - [login to view URL]) > .3 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([login to view URL] - [login to view URL]) > .4 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL
"CASE WHEN ([login to view URL] - [login to view URL]) > .5 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([login to view URL] - [login to view URL]) > .6 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([login to view URL] - [login to view URL]) > .7 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([login to view URL] - [login to view URL]) > .8 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([login to view URL] - [login to view URL]) > .9 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
b) The program generates and executes queries similar to this one:
SELECT CASE WHEN ([login to view URL] - [login to view URL]) > .1 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL, B.[OPEN], B.[CLOSE], [login to view URL], [login to view URL], C.[OPEN], C.[CLOSE], [login to view URL], [login to view URL], D.[OPEN], D.[CLOSE], [login to view URL], [login to view URL] FROM AAPL A LEFT JOIN AAPL B ON A.[INDEX] = B.[INDEX]+1 LEFT JOIN MSFT C ON A.[INDEX] = C.[INDEX] + 1 LEFT JOIN CSCO D ON A.[INDEX] = D.[INDEX] + 1
By iterating through the different values of the parameters. In this example it will run 27 times, because it needs to iterate through 3 values of {1} and 9 values of {5}
c) The program runs with similar parameters as part one, but it also provides an option to save the results to a table, a .csv file (with the headers), or both. Name the files or tables the same as the xml file plus numbers 01, 02, etc.