SQL Server Performance

Query on time update

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by chintalagiriprakash2010, Dec 24, 2010.

  1. chintalagiriprakash2010 New Member

    HI Experts iam using sap b1 where i have one table OINV .
    I enter time filed U_timePre as 11:00 where my document no is 1
    Now when i open 2nd doc U_timePre SHULD come automatically by adding 5 min to the 1st doc 11:05
    Now when i open 3rd doc U_timePre SHULD come automatically by adding 5 min to the 2st doc 11:15
    MY Table Is OINV my fields are
    SELECT DOCNUM,U_TIMEPRE FROM OINV
    so plzzzzz kind solve this ASAP its very urgent
    Regards
    prakash

    chintalagiriprakash2010, Dec 24, 2010
    #2

  2. Adriaan New Member

    Create an INSERT TRIGGER. The body of the trigger should be an update query based on the inserted snapshot, joined to the underlying table on the document number (which had better be a numeric column) minus 1.
    Doesn't seem like there's much point in having an incremental number AND a time stamp at 5 minute increments, since you can calculate the timestamp for the further documents by adding (x * 5) minutes to the original timestamp.
    Adriaan, Dec 27, 2010
    #3

  3. FrankKalis Moderator

    Welcome to the forum!
    I'm not sure if I understand you correctly, but this might at least be a start:
    DECLARE @OINV TABLE (DOCNUM int, U_TIMEPRE smalldatetime);

    INSERT @OINV
    (DOCNUM, U_TIMEPRE)
    VALUES
    (1, '2010-12-27 11:00:00')

    INSERT @OINV
    (DOCNUM, U_TIMEPRE)
    VALUES
    (2, NULL)

    INSERT @OINV
    (DOCNUM, U_TIMEPRE)
    VALUES
    (2, NULL)

    ;WITH myCTE AS (
    SELECT
    ROW_NUMBER() OVER (ORDER BY O.DOCNUM) - 1 AS rn,
    O.DOCNUM,
    U_TIMEPRE,
    (SELECT MIN(U_TIMEPRE) FROM @OINV) AS MinDate
    FROM
    @OINV O
    )

    SELECT
    myCTE.rn,
    myCTE.DOCNUM,
    myCTE.U_TIMEPRE,
    DATEADD(minute, 5 * myCTE.rn, myCTE.MinDate)
    FROM
    myCTE;
    rn DOCNUM U_TIMEPRE
    0 1 2010-12-27 11:00:00 2010-12-27 11:00:00
    1 2 NULL 2010-12-27 11:05:00
    2 2 NULL 2010-12-27 11:10:00
    Warning: Null value is eliminated by an aggregate or other SET operation.

    (3 row(s) affected)


    FrankKalis, Dec 27, 2010
    #4

  4. FrankKalis Moderator

    Should have refreshed the thread before replying... [:)]
    FrankKalis, Dec 27, 2010
    #5

Share This Page