Here is what i am trying to do
I have table with fields and some other which i don't need.
Contract#
Division#
Group#
Trans Date
Effective Date
Date will look like
Contract# Group# Division # Trans Date Effective Date End Date
123 g10 d1 2/19/2007 3/1/2007 12/31/2007
123 g11 d2 12/28/2007 1/1/2008 12/31/2008
345 g18 d1 3/10/2007 4/1/2007 2/17/2007
345 g19 d1 1/19/2008 2/18/2007 12/31/2008
There will be more then two rows for each contract# with the diffrent values in other fields in the table.
First i need to check if there is a change in the group# for the most current transactions of 2007 based on trans date for each contract# which means the New group# belongs to Max(TransDate) in 2007 and checked against group# belongs to one before that, if there is a change in Group# then get that contract and provide the details mentioned below.For some there might be Division# change also so i just need to get division# from both the records
So my out put should look like
Contract# Group# New Group# Old Division New Division Ymd Trans Ymd Eff
123 g10 g11 d1 d2 12/24/2007 1/1/2008
345 g18 g19 d1 d1 12/10/2007 2/18/2008
Thanks!!
Start Free Trial
