Search for project
More than 2000 projects issued through our platform and this
amount increases very fast.
Oracle SQL Proc (ID:2880)
| Project Creator: |
toepfer
FC Member For 6392 Days
Credits 170 Completed Proj. Num. 6 / 9 Total payment USD 655.00 Avg Daily Online 0.01 h (From 21/5/2007) Available on MSN/Skype Yes Last Login 8/7/2012 Peers Rating 100.00% 
|
|---|---|
| Budget: | Less than 250 |
| Created: | 8/15/2007 7:45:37 PM EST |
| Bidding Ends: | 8/30/2007 7:45:37 PM EST ( Expired ) |
| Development Cycle: | 15 Days |
| Bid Count: | 6
|
| Average Bid: | 120.83 |
| Project Description:
Given a table with a month VARCHAR2(3) and year field VARCHAR2(4), I need to write a stored proc that will accept a month and year parameter and extract the highest dates, changing the values to the passed date where they are less than or equal excluding lower dates and moving higher dates. The proc would have a parameter for month, year, table and new table. The proc would extract the data from table, filter it accordingly and place it in new table. Example scenarios ... in case 1, new table would end up with 8/2006 and 9/2006 case 2, 8/2007 and 9/2007 case 3 8/2007 Case 1 pass: 8 2006 3 2006 --- IGNORE 7 2006 --- Becomes 8 2006 9 2006 --- MOVE -------------------------------------------- Case 2 pass: 8 2007 3 2006 --- IGNORE 7 2006 --- Becomes 8 2007 9 2007 --- MOVE -------------------------------------------- Case 3 pass: 8 2007 3 2006 --- IGNORE 7 2006 --- IGNORE 1 2007 --- Becomes 8 2007 -------------------------------------------- Here's what the full table looks like: TRANSACTION VARCHAR2(256), COST_CENTER VARCHAR2(10), CENTER_IDENTIFIER VARCHAR2(3), EFFECTIVE_MONTH VARCHAR2(3), EFFECTIVE_YEAR VARCHAR2(4), CENTER_NAME VARCHAR2(256), CENTER_MANAGER VARCHAR2(256), FINANCE_MANAGER VARCHAR2(256), FUNCTION VARCHAR2(256), CENTER_ACTIVITY VARCHAR2(500), DESCRIPTION VARCHAR2(500), CCAMDESCRIPTION VARCHAR2(500), FIXED_BASIS VARCHAR2(500), CENTER_PERCENT NUMBER(5, 4), TEMPLATE_NUMBER VARCHAR2(10), ACCOUNT_DRIVER VARCHAR2(100), COMPANY VARCHAR2(100), MBU VARCHAR2(100), PRODUCT VARCHAR2(100), FUNDING_TYPE VARCHAR2(100), INCL_EXCL1 VARCHAR2(1), POINT1 VARCHAR2(100), PERCENT1 NUMBER(5, 4), TYPE1 VARCHAR2(56), INCL_EXCL2 VARCHAR2(1), POINT2 VARCHAR2(100), PERCENT2 NUMBER(5, 4), TYPE2 VARCHAR2(56), INCL_EXCL3 VARCHAR2(1), POINT3 VARCHAR2(100), PERCENT3 NUMBER(5, 4), TYPE3 VARCHAR2(56), INCL_EXCL4 VARCHAR2(1), POINT4 VARCHAR2(100), PERCENT4 NUMBER(5, 4), TYPE4 VARCHAR2(56), INCL_EXCL5 VARCHAR2(1), POINT5 VARCHAR2(100), PERCENT5 NUMBER(5, 4), TYPE5 VARCHAR2(56), USERID VARCHAR2(56), DATE_MAINTAINED DATE, DEFAULT_COMP_MBU VARCHAR2(256) Newly added descriptions: Here is some clarification: The relative date, and results table will change - so these need to be parameters in the proc. Only the rows with dates that are closest but less then the passed month and year get moved over. When they are inserted into the results table (the table name passed to the proc), the month and year get changed to the month and year passed in the proc. If the date is higher then the one passed, then those simply get copied over to the new table - the dates don't change. So the resulting table will contain all of the rows with dates higher then the one passed (nothing changes here) and all the rows with the dates immediately lower then the one passed - these dates changed to the mo/yr passed. Anything with a lower date just gets ignored. So in the sample scenarios In case 1, 8 is passed to the proc as the month parameter and 2006 as the year. 3 rows come up, one w/ 3/2006, one w/ 7/2006 and one w/ 9/2006 7/2006 is the nearest date to 8/2006 without going over, so all the columns in this row get moved to the results table and 7/2006 is changed to 8/2006 (the passed values) Because 7/2006 is closest to 8/2006 without going over, anything less is ignored. All of the rows w/ a mo/year greater then 8/2006 get copied over to the new table as well. In case 3 1/2007 is closest to 8/2007 without going over, so it gets copied to the new table and everything else is ignored. There's nothing greater to move either. The amount of rows in the table will change; the columns probably won't change (they're specified above). |
|
| Job Type | Other |
| Attached Files: | 20070815194524.txt |
