Post reply

TSql help

  • September 10, 2011 at 6:41 am

    #244626

    I cannot get this to work.

    I believe it is because of the date columns (they do not match exactly).

    UPDATE Report

    SET ShipID = itst.shipid ,

    PassengerCount = itst.PassengerCount ,

    ShipCode = itst.ShipCode ,

    MediaCompany = itst.MediaCompany

    FROM Report f ,

    [10.1.1.10\db20].iclhq.dbo.shipnschedule AS itst

    WHERE f.Ship = itst.ship

    AND f.docdate = itst.shipout

    the "f.docdate" is in format 2008-03-19 00:00:00.000 with the time always 0's

    the "itist.shipout" is in format 2008-11-20 08:43:27.000 with a time always there

    I only need the date to match.

    Please help me.

  • September 10, 2011 at 8:59 am

    #1379601

    This will do it...

    UPDATE Report

    SET ShipID = itst.shipid ,

    PassengerCount = itst.PassengerCount ,

    ShipCode = itst.ShipCode ,

    MediaCompany = itst.MediaCompany

    FROM Report f ,

    [10.1.1.10\db20].iclhq.dbo.shipnschedule AS itst

    WHERE f.Ship = itst.ship

    AND itst.shipout >= f.docdate

    AND itst.shipout < DATEADD(dd,1,f.docdate)

    To explain... let's consider a value of f.docdate for today. It would be today at midnight. Now, pick any time of day for today... all of the times today have two things in common with today... They're all greater than or equal to today at midnight and they all less then tomorrow at midnight no matter of thinly you slice the time.

    The only problem I see with this whole thing is what guarantee do you have that f.docdate will ALWAYS be a "whole" date? We could do it in the code above but that would make it so an INDEX SEEK wasn't possible.

    --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)

  • September 10, 2011 at 1:43 pm

    #1379626

    Thank you my friend this works perfectly.

  • September 11, 2011 at 1:54 am

    #1379666

    npeters 86796 (9/10/2011)

    Thank you my friend this works perfectly.

    Thank you for the feedback but I'm still curious... what do you have in place that guarantees that f.docdate will always be a "whole" date?

    --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)

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

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