We have a SQL database with a large table (2million rows by 50 columns) which contains financial information about properties, such as rent, fees and interest payments. We would like to produce a simple report from it.
The report needs to pull out 360 rows (one row for each month) using this SQL command
SELECT * FROM PropertyMonthAsset WHERE PropertyID=X
(where X is defined by the user in the URL of the page)
Once this data is retrieved, the ASP.net page needs to summarise this data as follows:
For about 10 of the columns, it takes the first 12 rows and subtotals them - this is then displayed on the screen as the first year's value
It repeats this for each column and for the remaining rows, so that we get a table like this:
Year 1 2 3 4 5 6
Rent 20 20 20 20 20 20
Fees 0 0 10 0 0 0
Interest -5 -5 -5 -5 -5 -5
Total 15 15 25 15 15 15
The important thing is that the adding up is done in ASP.net rather than SQL as all the queries we have run in SQL run too slowly.
Note also that the line called 'fees' may in fact be the subtotal of several columns.
For the successful candidate, there are likely to be many other similar reports that we would need written.
All we need is the ASP.net code - we have a system here to compile it and can then see if it works
We have now attached a ZIP file - see Platform section
1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.
2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables):
a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment.
b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request.
3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement).
SQL, Windows, ASP.net
The ZIP file contains an XLS spreadsheet. The spreadsheet has 4 tabs.
PropertyMonthProperty is an extract from the table 'PropertyMonthAsset' where the raw data is held
PropertyCMonth is contains the labels for the column headings explaining how we summarise the monthly information to create yearly information
PropertyType gives the labels of the rows and therefore shows which rows are added together
Sheet4 is the example of the final report required from this data (ie. the output from where the SQL terms is PropertyID = one number)