Cleaning up data

From: Munch (Munch_at_discussions.microsoft.com)
Date: 11/26/04 

Date: Fri, 26 Nov 2004 12:05:04 -0800

Here is some sample data.
ID INITIAL NAME SEX HIREDATE STATDATE STA ACT REA ENDDATE
123456 AA SMITH M 19680513 19680513
123456 AA SMITH M 19680513 20030115 A
123456 AA SMITH M 19680513 20030215 A
123456 AA SMITH M 19680513 20030315 A
123456 AA SMITH M 19680513 20030415 A
123456 AA SMITH M 19680513 20030510 A
123456 AA SMITH M 19680513 20030615 A
123456 AA SMITH M 19680513 20030715 A
123456 AA SMITH M 19680513 20030815 A
123456 AA SMITH M 19680513 20030915 A
123456 AA SMITH M 19680513 20031015 A
123456 AA SMITH M 19680513 20031118 A
123456 AA SMITH M 19680513 20031214 A
123456 AA SMITH M 19680513 20040118 A
123456 AA SMITH M 19680513 20040215 A
123456 AA SMITH M 19680513 20040328 P PLA MED
123456 AA SMITH M 19680513 20040411 P PLA MED
123456 AA SMITH M 19680513 20040516 P PLA MED
123456 AA SMITH M 19680513 20040620 P STD MED
123456 AA SMITH M 19680513 20040718 P STD MED
123456 AA SMITH M 19680513 20040819 P STD MED
123456 AA SMITH M 19680513 20040915 P STD DIS
123456 AA SMITH M 19680513 20041017 X

I need to clean the data in this table up to make it look like a history
table. I want to only have unique records in the table so that the table
reads as such

ID INITIAL NAME SEX HIREDATE STATDATE STA ACT REA ENDDATE
123456 AA SMITH M 19680513 19680513 20030115
123456 AA SMITH M 19680513 20030115 A 20040328
123456 AA SMITH M 19680513 20040328 P PLA MED 20040516
123456 AA SMITH M 19680513 20040620 P STD MED 20040819
123456 AA SMITH M 19680513 20040915 P STD DIS 20041017
123456 AA SMITH M 19680513 20041017 X

there are some other abnomalities in the source data aswell. There are
cases where the hire date is not consistant or can be null or zero filled.
For example:
ID INITIAL NAME SEX HIREDATE STATDATE STA ACT REA ENDDATE
123456 AA SMITH M 19680513 19680513
123456 AA SMITH M 19680614 20030115 A
123456 AA SMITH M 19680715 20030215 A
123456 AA SMITH M 20030315 A
123456 AA SMITH M 19680917 00000000 A
123456 AA SMITH M 00000000 20030510 A

Thanks for any help on trying to fix this terrible data.

Relevant Pages

  • Re: Are _T() and TEXT() macros equivalent?
    ... There is no need to read the history to use the stuff. ... so that you can understand why the crap is crap. ... Wait 10-20 years and let's see then how clean .NET will look like :-) ... Because it teaches me how to deal with an old legacy system, ...
    (microsoft.public.vc.mfc)
  • Re: Cleaning browser history
    ... Be sure that ALL instances of IE6 are CLOSED before you do ... A nice alternative just for selective of all Address history ... > I tried to clean up history of IE6 browser on a PC. ... > These actions clean up most history. ...
    (microsoft.public.windows.inetexplorer.ie6.browser)
  • How do I remove/Replace the index.dat file?
    ... >I have Norton SystemWorks 2003, ... No matter how much I clean, ... The settings indicate there are 3 history items ... Symantec says I should go to Microsoft Help ...
    (microsoft.public.windowsxp.general)
  • Re: HISTORY CLEAN UP
    ... At the right panel you will see all those URLs. ... > I clean my history every week or so, however, I have ... > the Yamaha website. ... I have already done the "clean ...
    (microsoft.public.security)
  • How do I remove/Replace the index.dat file?
    ... I have Norton SystemWorks 2003, ... IE history. ... it will only clean down to something ... Symantec says I should go to Microsoft Help ...
    (microsoft.public.windowsxp.general)