Post reply

Insert Week Number Field

  • June 6, 2007 at 5:16 am

    #117271

    I have imported a table with Employee,Date and total values.

    The columns are: EMPNAME, EDate and Total.

    The before data Looks like:

    000000, 2007-02-19 00:00:00, 14

    000000, 2007-02-20 00:00:00, 13

    000000, 2007-02-21 00:00:00, 10

    000000, 2007-02-22 00:00:00, 12

    000000, 2007-02-23 00:00:00, 9

    000000, 2007-02-24 00:00:00, 8

    000000, 2007-02-25 00:00:00, 12

    I need to add a column that would add the WeekNum of that date.

    EMPNAME, EDate, Total and WeekNum

    The data would then look like

    000000, 2007-02-19 00:00:00, 14, 15

    000000, 2007-02-20 00:00:00, 13 15

    000000, 2007-02-21 00:00:00, 10, 15

    000000, 2007-02-22 00:00:00, 12, 15

    000000, 2007-02-23 00:00:00, 9, 15

    000000, 2007-02-24 00:00:00, 8, 15

    000000, 2007-02-25 00:00:00, 12, 15

    I have over 700,000 records since the beginning of year, and I would hate to do this manually. Is there a way I could write an update query to do this?

    Thanks,

    CY

  • June 6, 2007 at 5:24 am

    #711060

    Add computed column.

    _____________
    Code for TallyGenerator

  • June 6, 2007 at 6:32 am

    #711078

    Thanks, I do not know I did not think of it. I used

    Update Total_Util set WeekNum = DateDiff("W",'01/1/2007',EDATE)/7 and it worked fine.

    CY

  • June 6, 2007 at 7:02 am

    #711089

    Probably stating the obvious, but now you have a column that can get out of sync with the date if the dates change.  It would be better if you removed the WeekNum column and added it back in as a computed column as Serqiy suggested.

    A computed column actually contains no data and cannot be updated... it just shows the result of a formula for each row.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • June 6, 2007 at 7:09 am

    #711093

    This would be true, however the date field will never change. This will just be used so that a week number can be used in a query instead of a date range.

    Thanks,

    CY

  • June 6, 2007 at 7:22 am

    #711101

    Understood.... just wanted you to know that the computed column would do the same and is just about as fast (can even be indexed if the formula is deterministic)... it also takes less disk space, etc, etc.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • June 6, 2007 at 7:28 am

    #711106

    If this table ever becomes dynamic....(Like most things some day it probably will).... I will put forth your suggestions.

    Thanks,

    CY

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply