Professional Web Applications Themes

Repeating records - Microsoft SQL / MS SQL Server

Hi, can somone please help me with this problem. I'm parsing a text file that holds call information. I've dumped the text file into a table and pd it the best that I know how. However, I'm having a problem getting the date on every call record because the date will only appear once and then you have all of the calls for that day. When the next day comes, you have the date stamped record and then call records. Here is an expample... CALL_DATE PHONE_NUMBER 8/15/2003 NULL NULL 555-555-5555 NULL 555-123-4568 8/16/2003 NULL NULL 123-678-9589 NULL 987-456-1234 NULL 567-897-1532 ...

  1. #1

    Default Repeating records

    Hi, can somone please help me with this problem.

    I'm parsing a text file that holds call information. I've
    dumped the text file into a table and pd it the best
    that I know how. However, I'm having a problem getting
    the date on every call record because the date will only
    appear once and then you have all of the calls for that
    day. When the next day comes, you have the date stamped
    record and then call records.
    Here is an expample...

    CALL_DATE PHONE_NUMBER

    8/15/2003 NULL
    NULL 555-555-5555
    NULL 555-123-4568
    8/16/2003 NULL
    NULL 123-678-9589
    NULL 987-456-1234
    NULL 567-897-1532
    8/17/2003 NULL
    NULL 987-897-8888

    What I would like to do is have the date show up in the
    CALL_DATE field for every call. Any thoughts?

    Thanks in advance.

    Bruce

    BORR Guest

  2. #2

    Default Re: Repeating records

    The problem as specified is impossible to solve because you haven't captured
    the information about which date applies to which phone number. If you add a
    line number to your source table you can do it:

    CREATE TABLE CallDataSource (line_no INTEGER PRIMARY KEY, call_date DATETIME
    NULL, phone_number VARCHAR(15) NULL)

    INSERT INTO CallDataSource VALUES (1,'20030815',NULL)
    INSERT INTO CallDataSource VALUES (2,NULL,'555-555-5555')
    INSERT INTO CallDataSource VALUES (3,NULL,'555-123-4568')
    INSERT INTO CallDataSource VALUES (4,'20030816',NULL)
    INSERT INTO CallDataSource VALUES (5,NULL,'123-678-9589')
    INSERT INTO CallDataSource VALUES (6,NULL,'987-456-1234')
    INSERT INTO CallDataSource VALUES (7,NULL,'567-897-1532')
    INSERT INTO CallDataSource VALUES (8,'20030817',NULL)
    INSERT INTO CallDataSource VALUES (9,NULL,'987-897-8888')

    Now transform the source data:

    CREATE TABLE CallData (call_date DATETIME, phone_number VARCHAR(15), PRIMARY
    KEY (call_date, phone_number))

    INSERT INTO CallData (call_date, phone_number)
    SELECT DISTINCT
    (SELECT MAX(call_date)
    FROM CallDataSource
    WHERE line_no <= C.line_no)
    AS call_date, phone_number
    FROM CallDataSource AS C
    WHERE phone_number IS NOT NULL

    This assumes that the source file is in date order to start with.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  3. #3

    Default Re: Repeating records

    Hello Bruce !

    Is that the content of your SQL Server table, its not even normalized in the
    first normalisation ?!?!?!?!
    First try to dump the textfile with normalisation in the table. Further you
    will be thankful for that.

    Now you have to p the values with a cursor:

    Sorry for some errors, i am not in office to test this.

    Try kind of this:

    ---------------

    SET NO_COUNT ON

    Create Table #DestTable(Call_Date datetime, Phonenumber varchar(50))

    DECLARE Test CURSOR FOR

    SELECT * from Tablename

    FETCH NEXT INTO CALLDate,PhoneNumber

    While Fetch_Status=0

    BEGIN

    IF CallDate=DateReapeater
    BEGIN
    Insert into #DestTable values (CallDate,Phonenumber)
    END
    ELSE
    Insert into #DestTable values (DateReapeater,Phonenumber)

    SET DateRepeater = Calldate

    FETCH NEXT INTO CALLDate,PhoneNumber
    END

    SELECT * from #DestTable

    DROP TABLE #Desttable

    SET NO_COUNT OFF



    Jens Süßmeyer.


    "BORR" <com> schrieb im Newsbeitrag
    news:0e0c01c367e4$f4fea780$gbl... 


    Jens Guest

  4. #4

    Default Re: Repeating records

    Jens,

    You're assuming that the cursor "SELECT * from Tablename" will always
    retrieve the data in the order in which it started life in the text file.
    That's a dangerous assumption considering we don't know what import method
    Bruce is using, whether there's a clustered index on this table, etc, etc.
    The "safe" way is to add a line number to the data.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  5. #5

    Default Re: Repeating records

    Hello David,

    of corse you are right, i also though about this. I just guessed that he
    imported it the way i wrote the cursor.

    It is how i said, just rewrite the import to have s solid base to work with.

    Jens Süßmeyer.


    Jens Guest

  6. #6

    Default Re: Repeating records

    > It is how i said, just rewrite the import to have s solid base to work
    with.
    Definitely the best advice :-)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Jens Süßmeyer" <jsuessmeyer(Remove_ME]web.de> wrote in message
    news:%23AMT4t%phx.gbl... 
    with. 


    David Guest

  7. #7

    Default Re: Repeating records

     
    you haven't captured 
    number. If you add a 
    call_date DATETIME 
    VARCHAR(15), PRIMARY 
    start with. 
    BORR Guest

Similar Threads

  1. repeating records when using cfoutput
    By brokerandy25 in forum Coldfusion - Advanced Techniques
    Replies: 13
    Last Post: July 11th, 12:49 PM
  2. Many form records to many table records
    By Anne in forum Microsoft Access
    Replies: 4
    Last Post: September 3rd, 02:12 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139