rushitshah
Registered User.
- Local time
- Yesterday, 16:20
- Joined
- Jul 27, 2005
- Messages
- 19
Hello People.
I need some help coding with an application.
I have a table that includes forecast of different parameters for different products. So when ever new forecast data is added the already existing data for same product and parameter is not deleted, but there is a field called revision, so old data record is marked as 0 and new is marked as 1 to indicated the revision 1 indicates new forecast. It can go like 2, 3....
What I want is to comapre the latest records for different products and their parameters from the table.
So I can do a duplicate record query, that will give me all the duplicates for certain product and parameter. The other records remaining in the table now are only single, so they can be latest. And from the duplicate query I need to take highest revision no. for the latest record.
After that I need to merge all those, highest revision no. records from duplicate query and the single records (The records in the table other than the duplicate query records)
Need some logical and code help with this.
but my data is kind of strange.
The result merged table...
Product Parameter jan'05........jan'06...mar'06.......jan'07 Owner Revision
Name Name
M1_p BI time .1 .2 .4 .6 Jim rev0
M1_p test time .2 .5 .3 .7 Ted rev0
m2_p Oven pop .3 .4 .3 .5 Mary rev0
M1_p BI time .4 .1 .1 .6 Jim rev1
m2_p Oven pop .2 .2 .2 .2 Mary rev1
Here rev 0 is old forecast for some product and a particular parameter, if there comes new forecast for same combination then it will be rev 1. It can be rev 2, rev 3 for different product or parameters.
So What I want is to generate a latest report out of this.
if I run duplicate query on product name and parameter name both then I will get a table with 1 ,3 ,4 ,5 records from previous table.
Product Parameter jan'05........jan'06...mar'06.......jan'07 Owner Revision
Name Name
M1_p BI time .1 .2 .4 .6 Jim rev0
m2_p Oven pop .3 .4 .3 .5 Mary rev0
M1_p BI time .4 .1 .1 .6 Jim rev1
m2_p Oven pop .2 .2 .2 .2 Mary rev1
From this table I need to select latest records based on revision field. Means for product name and parameter name combination I need to check for highest rev no. in that group and take that record out. And the remaining 2 record that was in original table but it was not having any update so it should be there in final report. Like this..
Product Parameter jan'05........jan'06...mar'06.......jan'07 Owner Revision
Name Name
M1_p test time .2 .5 .3 .7 Ted rev0
M1_p BI time .4 .1 .1 .6 J im rev1
m2_p Oven pop .2 .2 .2 .2 Mary rev1
Please help with this.
Thanks..
I need some help coding with an application.
I have a table that includes forecast of different parameters for different products. So when ever new forecast data is added the already existing data for same product and parameter is not deleted, but there is a field called revision, so old data record is marked as 0 and new is marked as 1 to indicated the revision 1 indicates new forecast. It can go like 2, 3....
What I want is to comapre the latest records for different products and their parameters from the table.
So I can do a duplicate record query, that will give me all the duplicates for certain product and parameter. The other records remaining in the table now are only single, so they can be latest. And from the duplicate query I need to take highest revision no. for the latest record.
After that I need to merge all those, highest revision no. records from duplicate query and the single records (The records in the table other than the duplicate query records)
Need some logical and code help with this.
but my data is kind of strange.
The result merged table...
Product Parameter jan'05........jan'06...mar'06.......jan'07 Owner Revision
Name Name
M1_p BI time .1 .2 .4 .6 Jim rev0
M1_p test time .2 .5 .3 .7 Ted rev0
m2_p Oven pop .3 .4 .3 .5 Mary rev0
M1_p BI time .4 .1 .1 .6 Jim rev1
m2_p Oven pop .2 .2 .2 .2 Mary rev1
Here rev 0 is old forecast for some product and a particular parameter, if there comes new forecast for same combination then it will be rev 1. It can be rev 2, rev 3 for different product or parameters.
So What I want is to generate a latest report out of this.
if I run duplicate query on product name and parameter name both then I will get a table with 1 ,3 ,4 ,5 records from previous table.
Product Parameter jan'05........jan'06...mar'06.......jan'07 Owner Revision
Name Name
M1_p BI time .1 .2 .4 .6 Jim rev0
m2_p Oven pop .3 .4 .3 .5 Mary rev0
M1_p BI time .4 .1 .1 .6 Jim rev1
m2_p Oven pop .2 .2 .2 .2 Mary rev1
From this table I need to select latest records based on revision field. Means for product name and parameter name combination I need to check for highest rev no. in that group and take that record out. And the remaining 2 record that was in original table but it was not having any update so it should be there in final report. Like this..
Product Parameter jan'05........jan'06...mar'06.......jan'07 Owner Revision
Name Name
M1_p test time .2 .5 .3 .7 Ted rev0
M1_p BI time .4 .1 .1 .6 J im rev1
m2_p Oven pop .2 .2 .2 .2 Mary rev1
Please help with this.
Thanks..
