Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007

    Unanswered: Filling out missing data in subsequent records?


    I receive several datafiles from another system that are more or less in a Excel pivot table like format.
    That is the first row representing the current data is fully filled, while subsequent rows, representing historic data are left partly empty.
    Current rows and historic rows have different identifiers, e.g. rectype=0 or 1
    Filling out the missing data on the historic record should be simple, if only all current rows would be filled.
    Some current rows aren't filled, so the stuff like the following doesn't work:
    update t1
    set t1.colA =
    select top 1 t2.colA
    from mytable AS t2
    where t2.rowid <= t1.rowid
    and t2.cola <> 0
    order by t2.rowid desc
    from mytable AS t1

    Somehow I need to check for the rectype, so I don't fill out rows with data from a previous entity

    Any suggestions before I revert to using a cursor?

    And while we are at it: I am in for an easy way to do this for all (about 60) colums in one move?

    Before you ask:
    After filling everything out we process the file to arrive at a few handy fromto tables, so we can use the correct data about the entity's status at a particular point in time elsewhere

    I am using MS SQL Server 2005, and solutions are allowd to use any specific trickery that MSSQL allows.

    Many thanks for any constructive thoughts



  2. #2
    Join Date
    Jan 2007
    Provided Answers: 10
    Would this do the job? My changes are highlighted
    UPDATE	t1
    SET	t1.colA = 
    	SELECT TOP 1 t2.colA 
    	FROM	 MyTable AS t2 
    	WHERE	 t2.rowid < t1.rowid
    	AND	 t2.colA <> 0 
    	ORDER BY t2.rowid DESC
    FROM	mytable AS t1
    WHERE	rectype = 0
    I havn't tested this code - it's only in my head (/on the screen) so don't use it on your live data
    Home | Blog

  3. #3
    Join Date
    Jun 2007
    Thnak you georgev:
    for the tagline (I won't do it again; a real eye-opener)
    for the small correction in my code and for the direction.

    It goes wrong where there are history record after the current reccord with no data.Obvioulsy they get filled from the previous current record that hadd data.

    My interim solution
    1. update all current records wh data with a dummy value
    2. use the fill out query
    We then have to check the marked records and see if we can find a pattern that allows us the handle them in code (otherwise someone has to go through them manually; only .25% of total)

    Thanks for you swift response



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts