Closed

Expand data on SQL Database

This project was awarded to dvfabia for $60 USD.

Get free quotes for a project like this
Employer working
Awarded to:
Skills Required
Project Budget
$30 - $250 USD
Total Bids
6
Project Description

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, [url removed, login to view] AS {2}_HIGH_D1, [url removed, login to view] AS {2}_LOW_D1, C.[OPEN] AS {3}_OPEN_D1, C.[CLOSE] AS {3}_CLOSE_D1, [url removed, login to view] AS {3}_HIGH_D1, [url removed, login to view] AS {3}_LOW_D1, D.[OPEN] AS {4}_OPEN_D1, D.[CLOSE] AS {4}_CLOSE_D1, [url removed, login to view] AS {4}_HIGH_D1, [url removed, login to view] 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 ([url removed, login to view] - [url removed, login to view]) > .1 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([url removed, login to view] - [url removed, login to view]) > .2 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([url removed, login to view] - [url removed, login to view]) > .3 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([url removed, login to view] - [url removed, login to view]) > .4 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL
"CASE WHEN ([url removed, login to view] - [url removed, login to view]) > .5 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([url removed, login to view] - [url removed, login to view]) > .6 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([url removed, login to view] - [url removed, login to view]) > .7 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([url removed, login to view] - [url removed, login to view]) > .8 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",
"CASE WHEN ([url removed, login to view] - [url removed, login to view]) > .9 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL",



b) The program generates and executes queries similar to this one:

SELECT CASE WHEN ([url removed, login to view] - [url removed, login to view]) > .1 THEN 'TRADE' ELSE 'NO_TRADE' END AS LABEL, B.[OPEN], B.[CLOSE], [url removed, login to view], [url removed, login to view], C.[OPEN], C.[CLOSE], [url removed, login to view], [url removed, login to view], D.[OPEN], D.[CLOSE], [url removed, login to view], [url removed, login to view] 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.

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online