Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sql table missing data HELP!

Posted on 2005-04-06
17
Medium Priority
?
248 Views
Last Modified: 2012-05-05
Hello Experts!

One of my sql tables is doing something strange and I need help cleaning it up.  This would be an easy fix in excel if the whole table would fit!  This entire table has somewhere around 1.4 million rows I think.  Here's what the data looks like:

SRC     Date     AcctNum     RefNum     Amt
  9      4-3-05     1001             &          500
  9      4-3-05     1001             &          -500
  9      4-3-05     1001             &          100
  9      4-3-05     1001          12345       300

So the entire table goes in blocks like this.  In excel to fix this (usually the table is very small because I only import current month) I would simply reverse the tables order so the correct refnum is first, then I would write a simple formula to copy the refnum down and overwrite the "&"'s until it hits a new refnum.

Now due to the table size I can't clean this data up in excel.  So I'm kinda lost in ms sql on cleaning this.  I don't even know how to import it into sql and reverse the table order so the correct refnum starts first.  

Can this be done with a stored procedure?  I've been looking at the sql commands and I can't seem to find anything on filling down based on some constraints.  The thing is that the src and date will always be the same per "group" of data.  This table is just a journal of all accounting postings.  So the refnum should be the same but the system that uses this data knows how to copy down the refnum for each line.  
I'm lost at this point and I have to get this cleaned up as I can't link any of my reports based on refnum.

Help!
0
Comment
Question by:jay-are
  • 7
  • 6
  • 4
17 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13717535
Please explain the logici of how RefNum is filled again?  In the example provided is RefNum '12345' for all instances of '9'/'4-3-05'/'1001' or speaking generally is RefNum the same for all each DISTINCT SRC, Date, AcctNum?

Gonna take a stab...

UPDATE myTable
SET RefNum = tblRefNum.MaxRefNum
FROM myTable
INNER JOIN (SELECT SRC, Date, AcctNum, Max(RefNum) MaxRefNum FROM myTable) AS tblRefNum
     ON myTable.SRC = tblRefNum.SRC AND myTAble.Date = tblRefNum.Date AND myTable.AcctNum = tblRefNum.AcctNum
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13717539
correction...

UPDATE myTable
SET RefNum = tblRefNum.MaxRefNum
FROM myTable
INNER JOIN (SELECT SRC, Date, AcctNum, Max(RefNum) MaxRefNum FROM myTable GROUP BY SRC, Date, AcctNum) AS tblRefNum
     ON myTable.SRC = tblRefNum.SRC AND myTAble.Date = tblRefNum.Date AND myTable.AcctNum = tblRefNum.AcctNum
0
 

Author Comment

by:jay-are
ID: 13717760
For some clarification the data itself.  The import I do pulls the data in just as you see it above.  So for each block of postings you'll have 1 constant date and src.  The only thing that can change is the acctnum, amount.
Here is a copy/paste from enterprise manager:

      1111      -19500      233758      PAYMENT      &      5      3/1/2004
      1111      -12385      &      PAYMENT      &      5      3/1/2004
      1111      -8685      &      PAYMENT      &      5      3/1/2004
      1111      -1885      &      PAYMENT      &      5      3/1/2004
      1111      -16585      &      PAYMENT      &      5      3/1/2004
      1111      -2885      &      PAYMENT      &      5      3/1/2004
      1111      -6085      &      PAYMENT      &      5      3/1/2004
      1111      -15085      77678      PAYMENT      &      5      3/1/2004
      1111      -16425      500521      PAYMENT      030104      5      3/1/2004
      1111      100      102792      TOWN ANI      KS012076      2      3/1/2004
      2429      -725             PARTS PHYSE0001-0104      13      1/1/2004
      1111      16885      &      GEORGIA DEALERS AUTO AUCTION      2LA03355      2      3/2/2004
      1111      6535      &      GEORGIA DEALERS AUTO AUCTION      9UJ26183      2      3/2/2004
      1111      6985      &      GEORGIA DEALERS AUTO AUCTION      YE057758      2      3/2/2004
      1111      17585      &      GEORGIA DEALERS AUTO AUCTION      3Z265973      2      3/2/2004
      1111      8285      &      GEORGIA DEALERS AUTO AUCTION      WKA76860      2      3/2/2004
      1111      9485      &      GEORGIA DEALERS AUTO AUCTION      2J038393      2      3/2/2004
      1111      10685      &      GEORGIA DEALERS AUTO AUCTION      &      2      3/2/2004
      1111      -10685      &      GEORGIA DEALERS AUTO AUCTION      9NB60332      2      3/2/2004
      1111      10685      &      GEORGIA DEALERS AUTO AUCTION      &      2      3/2/2004
      1111      -10685      77678      GEORGIA DEALERS AUTO AUCTION      9NB60332A      2      3/2/2004
      1111      -6000      &      WHSL PAYMENT W1151615      &      5      3/3/2004
      1111      -500      &      WHSL PAYMENT LU261994      &      5      3/3/2004
      1111      -800      &      WHSL PAYMENT SK183502      &      5      3/3/2004
      1111      -1700      &      WHSL PAYMENT NU086652      &      5      3/3/2004
      1111      -800      &      WHSL PAYMENT NZ214085      &      5      3/3/2004
      1111      -50      &      WHSL PAYMENT KY652201      &      5      3/3/2004
      1111      -1700      &      WHSL PAYMENT VKC44054      &      5      3/3/2004
      1111      -400      &      WHSL PAYMENT MB129744      &      5      3/3/2004
      1111      -8000      102792      WHSL PAYMENT YEA99084      030304      5      3/3/2004

Sorry it doesn't come out as well as I hoped but you get the idea of what is happening.  Usually I'd just export this table into excel, reverse it's order (just my preference so I'm working down instead of up) and write this formula to clean it up:  =IF(I11="&",L10,I11).  So I create a whole new colum called "cleanref" or something like that and fill it with that formula.  So when I come to a record that has multiple lines of "&"'s I look at the previous cleanref data for the answer.  Hopefully that makes sense!  :)

What I failed to mention in my first post was the control number column which is doing the same thing as the refnum.  
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13717840
UPDATE myTable
SET RefNum = tblFiller.MaxRefNum, [control number] = tblFiller.MaxControlNumber
FROM myTable
INNER JOIN (SELECT AcctNum, Max(RefNum) MaxRefNum, Max([control number]) AS MaxControlNumber
     FROM myTable GROUP BY AcctNum) AS tblFiller
     ON myTable.AcctNum = tblFiller.AcctNum
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13717866
my bad...that won't work.  It's starting to look like you're going to need a CURSOR because there is no logical means to group the records with '&' with the corresponding record with a value
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13717924
Do you have an IDENTITY column on your current table?  If not, create a new table the same as the current one except that it includes an IDENTITY column:

SELECT IDENTITY(INT, 1, 1) AS ident, oldTable.*
INTO newTable
FROM oldTable

ALTER TABLE newTable
ADD CONSTRAINT newTable_Ix1
UNIQUE CLUSTERED (ident)


Then you can update the missing values like so:

UPDATE newTable
SET RefNum = (
    SELECT Refnum
    FROM newTable nt2
    WHERE nt2.ident = (
        SELECT MIN(ident)
        FROM newTable nt3
        WHERE nt3.ident > nt.ident
            AND Refnum <> '&'
        )
    )
WHERE refNum = '&'

And again, substituting AcctNum for RefNum.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13717984
Finally, if the original table has no indexes/fks/checks/constraints/etc. that are difficult to re-create, simply drop the original table, drop the ident column on the new table, then re-name the new table to the old table name.

If the orig table has too many indexes/fks/checks/constraints/etc., then just TRUNCATE TABLE the original table (if possible; use DELETE FROM if TRUNCATE not allowed), then insert all rows in the newTable to the oldTable, then delete the new table.


NOTE: Technically there needs to be an IDENTITY column on the original table when it is loaded; otherwise you can't be sure be sure that SQL will later read/copy the rows in the same order as originally loaded.
0
 

Author Comment

by:jay-are
ID: 13718006
Sorry, I do have an identity column called IDS.ID, it's incremental.  
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 13718071
Great!!  Then no need to create a new table, just use this update on your existing table:


UPDATE yourTable
SET RefNum = (
    SELECT Refnum
    FROM yourTable yt2
    WHERE yt2.ident = (
        SELECT MIN(ident)
        FROM yourTable yt3
        WHERE yt3.ident > yourTable.ident
            AND yt3.Refnum <> '&'
        )
    )
WHERE refNum = '&'
0
 

Author Comment

by:jay-are
ID: 13718469
Scott:

Looks good, I'm running this in query analyzer right now to test it.  So far it's up to 14 minutes.  Will let you know once it finishes!
0
 

Author Comment

by:jay-are
ID: 13719054
Ok this is close.  I had to kill the update that was running on the main table because it was up to 55 minutes and it was taking up 100% cpu usage!  So I shortened the table to just a few entries to test on.  Here is the test data before the update:

account      amount      contnum      desc      refnum      src      date      ids.id
1111      -19500      233758      PAYMENT      &      5      3/1/2004      
1111      -12385      &      PAYMENT      &      5      3/1/2004      
1111      -8685      &      PAYMENT      &      5      3/1/2004      
1111      -1885      &      PAYMENT      &      5      3/1/2004      
1111      -16585      &      PAYMENT      &      5      3/1/2004      
1111      -2885      &      PAYMENT      &      5      3/1/2004      
1111      -6085      &      PAYMENT      &      5      3/1/2004      
1111      -15085      77678      PAYMENT      &      5      3/1/2004      
1111      -16425      500521      PAYMENT      30104      5      3/1/2004      
1111      100      102792      TOWN AN KS012076      2      3/1/2004      
2429      -725             PARTS PH      SE0001-0104      13      1/1/2004      
1111      16885      &      GEORGIA       2LA03355      2      3/2/2004      
1111      6535      &      GEORGIA       9UJ26183      2      3/2/2004      
1111      6985      &      GEORGIA       YE057758      2      3/2/2004      
1111      17585      &      GEORGIA       3Z265973      2      3/2/2004      
1111      8285      &      GEORGIA       WKA76860      2      3/2/2004
1111      9485      &      GEORGIA       2J038393      2      3/2/2004
1111      10685      &      GEORGIA       &      2      3/2/2004
1111      -10685      &      GEORGIA       9NB60332      2      3/2/2004
1111      10685      &      GEORGIA       &      2      3/2/2004
1111      -10685      77678      GEORGIA       9NB60332A      2      3/2/2004
1111      -6000      &      WHSL PAY      &      5      3/3/2004
1111      -500      &      WHSL PAY      &      5      3/3/2004
1111      -800      &      WHSL PAY      &      5      3/3/2004
1111      -1700      &      WHSL PAY      &      5      3/3/2004
1111      -800      &      WHSL PAY      &      5      3/3/2004
1111      -50      &      WHSL PAY &      5      3/3/2004
1111      -1700      &      WHSL PAY &      5      3/3/2004
1111      -400      &      WHSL PAY &      5      3/3/2004
1111      -8000      102792      WHSL PAY 30304      5      3/3/2004

After running this update:

UPDATE jrnlhistory_test
SET RefNum = (
    SELECT Refnum
    FROM jrnlhistory_test yt2
    WHERE yt2.[ids.id] = (
        SELECT MIN([ids.id])
        FROM jrnlhistory_test yt3
        WHERE yt3.[ids.id] > jrnlhistory_test.[ids.id]
            AND yt3.Refnum <> '&'
        )
    )
WHERE refNum = '&'

I got this date:

acct      amt      ctrlnum      desc      refnum      src      date      id
1111      -19500      NULL      PAYMENT      KS012076      5      3/1/2004 0:00      1
1111      -12385      &      PAYMENT      KS012076      5      3/1/2004 0:00      2
1111      -8685      &      PAYMENT      KS012076      5      3/1/2004 0:00      3
1111      -1885      &      PAYMENT      KS012076      5      3/1/2004 0:00      4
1111      -16585      &      PAYMENT      KS012076      5      3/1/2004 0:00      5
1111      -2885      &      PAYMENT      KS012076      5      3/1/2004 0:00      6
1111      -6085      &      PAYMENT      KS012076      5      3/1/2004 0:00      7
1111      -15085      NULL      PAYMENT      KS012076      5      3/1/2004 0:00      8
1111      -16425      NULL      PAYMENT      NULL      5      3/1/2004 0:00      9
1111      100      NULL      TOWN AN      KS012076      2      3/1/2004 0:00      10
2429      -725             PARTS P      SE0001-0104      13      1/1/2004 0:00      11
1111      16885      &      GEORGIA      2LA03355      2      3/2/2004 0:00      12
1111      6535      &      GEORGIA      9UJ26183      2      3/2/2004 0:00      13
1111      6985      &      GEORGIA      YE057758      2      3/2/2004 0:00      14
1111      17585      &      GEORGIA      3Z265973      2      3/2/2004 0:00      15
1111      8285      &      GEORGIA      WKA76860      2      3/2/2004 0:00      16
1111      9485      &      GEORGIA      2J038393      2      3/2/2004 0:00      17
1111      10685      &      GEORGIA      9NB60332      2      3/2/2004 0:00      18
1111      -10685      &      GEORGIA      9NB60332      2      3/2/2004 0:00      19
1111      10685      &      GEORGIA      9NB60332A      2      3/2/2004 0:00      20
1111      -10685      NULL      GEORGIA      9NB60332A      2      3/2/2004 0:00      21
1111      -6000      &      WHSL PA      NULL      5      3/3/2004 0:00      22
1111      -500      &      WHSL PA      NULL      5      3/3/2004 0:00      23
1111      -800      &      WHSL PA      NULL      5      3/3/2004 0:00      24
1111      -1700      &      WHSL PA      NULL      5      3/3/2004 0:00      25
1111      -800      &      WHSL PA      NULL      5      3/3/2004 0:00      26
1111      -50      &      WHSL PA      NULL      5      3/3/2004 0:00      27
1111      -1700      &      WHSL PA      NULL      5      3/3/2004 0:00      28
1111      -400      &      WHSL PA      NULL      5      3/3/2004 0:00      29
1111      -8000      NULL      WHSL PA      NULL      5      3/3/2004 0:00      30

Did I mess up the update?  
0
 

Author Comment

by:jay-are
ID: 13719518
Wait, that was my fault on the import.  I put the data in an excel sheet then imported it into sql and it was right aligning some rows which caused something to mess up in sql.

I did it via csv with the data aligned the same way and it works fine!

Thanks!!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13719605
An index on id (or perhaps (id, refnum), although that will involve a lot of index updates) might help the performance considerably, if SQL uses the index.

Also, since you will be replacing "&" with a longer string, you should make the id a clustered index and set the FILLFACTOR to, say, about 70% to allow free space for the rows to expand without having to be moved to another page.  That will also help tremendously with performance.  When done with all updates, change the fillfactor to 90/95 and rebuild the clustered index, to compact the rows.
0
 

Author Comment

by:jay-are
ID: 13719628
One last question about this update.

I only have to run this once when I initially import this history file.
I will run this same update for both the refnum column and the ctrlnum column.  The table is over 1.4 million records long!  

When I first ran the update I ran it on the entire table and it got up to 55 minutes or so and I had to just kill the query because it basically shut the sql server down.  No one could access the server period while it was running this query.
After I stopped the query I took a look at the table and it looked like no rows had been affected.  How long will it take to actually complete this update?  Should I just start it at 2 a.m. and hope it finishes before everyone arrives in the morning?  Anyway to speed it up?  Maybe lower the cpu usage sql gets?

Thanks!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13719793
Use the index and FILLFACTOR as noted above.  That should speed it up.

Btw, when you cancel it, you won't see any of the changes because SQL will roll them back out :-)
0
 

Author Comment

by:jay-are
ID: 13818530
Just a follow up with this solution:

Scott, I indexed based on just the ID field.  I also set it's fillfactor to 90.

BIG help!  At first the update would simply run forever.  Now it cleans that whole table in 13 seconds!  :)

Thanks for your help!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13818632
Thanks for the follow up!  And I'm very glad that you finally got a good result.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question