Post reply

Table design help .

  • August 29, 2012 at 10:51 am

    #280496

    I need to develop a code where I update col1 of ADTABLE1 which has WHERE clause criteria coming from a table

    Update ADTABLE1

    SET col1 = 'newvalue'

    WHERE datediff(dd,RCVD,getdate()) > tbl1.Days

    And clientcode =tbl1.Client

    AND Code > tbl1.startcode

    AND Code < tb1.endcode

    AND code not in (tbl1.notcode1,tbl1.notcode2)

    The problem here is we can have many notcode values "notcode1, notode2 ....notcodeN for each client

    And all these values should come from the table so that user can control the WHERE criteria.

    I started with creating a table

    tbl1

    client,days,startcode,endcode, notcode1, notcode2

    then realized that cannot have specific number of notcode. Is should be a way to do this. Any help would be greatly appreciated.

    Thanks

  • August 29, 2012 at 12:20 pm

    #1531565

    Try to look for First Normal Form or Database normalization.

    Here's a start:

    http://www.sqlservercentral.com/blogs/jamesserra/2012/01/06/normalizing-your-database/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 29, 2012 at 12:24 pm

    #1531567

    This is what I have done so far

    Table1

    tbl1ID, client code days startcode endcode

    1,clientA, 60,200, 300

    2, clientB, 35, 200, 400

    3, clientC, 22,100,200

    Table2

    tbl2Id,tbl1Id,excludecode

    1,1,250

    2,1,280

    3,2,250

    4,3,115

    5,3,120

    Now how do I construct my update statement's where clause values based on this table entry.

  • August 29, 2012 at 12:43 pm

    #1531579

    You might be able to use something like this:

    I'm not sure if it would work as I can't see the DDL, but it can give you an idea.

    Update A

    SET col1 = 'newvalue'

    FROM ADTABLE1 A

    JOIN Table1 t1 ON A.clientcode =t1.Client

    AND datediff(dd,A.RCVD,getdate()) > t1.Days

    AND A.Code > t1.startcode

    AND A.Code < t1.endcode

    AND A.code NOT IN (SELECT t2.excludecode

    FROM Table2 t2

    WHERE t2.tbl1Id = t1.tbl1ID)

    --

    --OR

    --

    Update A

    SET col1 = 'newvalue'

    FROM ADTABLE1 A

    JOIN Table1 t1 ON A.clientcode =t1.Client

    JOIN Table2 t2 ON t2.tbl1Id = t1.tbl1ID

    WHERE datediff(dd,A.RCVD,getdate()) > t1.Days

    AND A.Code > t1.startcode

    AND A.Code < t1.endcode

    AND A.code <> t2.excludecode

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 29, 2012 at 1:20 pm

    #1531607

    Works great! Thanks a bunch.

Viewing 5 posts - 1 through 4 (of 4 total)

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