Post reply

Need help in writing SP

  • July 17, 2007 at 2:59 pm

    #179704

    Hi

    I have the data as below, I need to update with new field with the value 'Y'.

    Need to check each record with Item No condition Complete Date and next record start date falls within 3 days, i need to update new field with 'Y' for new Record.

     

    Call No Item No Start Date Complete Date
    0000196622  170537 10/4/05 10/4/05
    0000208536  170537 11/11/05 1/30/06
    0000291716  170537 7/14/06 7/14/06
    0000336180  170537 2/14/07 2/15/07
    0000342048  170537 2/22/07 6/21/07
    0000346923  170537 2/23/07 2/26/07

    In the above Data one record is matching with the above condition.  The out put should be as below.

    Call No Item No Start Date Complete Date New Field
    0000196622  170537 10/4/05 10/4/05 N
    0000208536  170537 11/11/05 1/30/06 N
    0000291716  170537 7/14/06 7/14/06 N
    0000336180  170537 2/14/07 2/15/07 N
    0000342048  170537 2/22/07 6/21/07 N
    0000346923  170537 2/23/07 2/26/07 Y

    If somebody can help how to write SP.

    Thx

    Vijay

  • July 17, 2007 at 9:55 pm

    #720251

    Vijaya,

    Since you're brand new, lemme just tell you that posting data in the following manner is much more helpful to those that would help you... tell's us almost everything we need to know and gives us a vehicle to test our solutions with...

    --=================================================================

    --      Create the test table and test data

    --=================================================================

    --===== Create a test table to hold the test data in

     CREATE TABLE #YourTable

            (

            CallNo       INT PRIMARY KEY CLUSTERED,

            ItemNo       INT,

            StartDate    DATETIME,

            CompleteDate DATETIME,

            NewField     CHAR(1)

            )

    --===== Populate the test table with test data

     INSERT INTO #YourTable

            (CallNo, ItemNo, StartDate, CompleteDate)

     SELECT 0000196622,170537,'10/4/05','10/4/05' UNION ALL

     SELECT 0001196622,170000,'10/4/05','10/4/05' UNION ALL

     SELECT 0001346923,170000,'2/23/07','2/26/07' UNION ALL

     SELECT 0000208536,170537,'11/11/05','1/30/06' UNION ALL

     SELECT 0001291716,170000,'7/14/07','7/14/07' UNION ALL

     SELECT 0000291716,170537,'7/14/06','7/14/06' UNION ALL

     SELECT 0000336180,170537,'2/14/07','2/15/07' UNION ALL

     SELECT 0000342048,170537,'2/22/07','6/21/07' UNION ALL

     SELECT 0001208536,170000,'11/11/05','1/30/06' UNION ALL

     SELECT 0001336180,170000,'7/14/07','7/15/07' UNION ALL

     SELECT 0001342048,170000,'2/22/07','6/21/07' UNION ALL

     SELECT 0000346923,170537,'2/23/07','2/26/07'

    --===== Create a very important index that this won't work without

     CREATE INDEX IX_YourTable_ItemNo_StartDate

         ON #YourTable (ItemNo,StartDate)

    GO

     

    As you can see, I added a bit of test data in a bit of a random order...

    Ok... most folks would use some sort of self-join or maybe even a triangular join or some MAX's, etc.  I've not found anything that can beat the speed of the following method, though... and do notice the Index Hint on the FROM clause... this won't work without it

    --=================================================================

    --      Solve the problem

    --=================================================================

    --===== Declare and preset local variables

    DECLARE @PrevItemNo    INT

    DECLARE @PrevStartDate DATETIME

    DECLARE @PlaceHolder   CHAR(1)

        SET @PrevItemNo    = 0

        SET @PrevStartDate = '01/01/1753'

    --===== Do the update using SQL Server's proprietary UPDATE method of

         -- UPDATE @variable = column = formula

     UPDATE #YourTable

        SET @PlaceHolder = NewField = CASE

                                          WHEN @PrevItemNo = ItemNo

                                           AND DATEDIFF(dd,@PrevStartDate,StartDate) <= 3

                                          THEN 'Y'

                                          ELSE 'N'

                                      END,

            @PrevItemNo    = ItemNo,

            @PrevStartDate = StartDate

       FROM #YourTable WITH (INDEX(IX_YourTable_ItemNo_StartDate),TABLOCK)

    --===== Show the answer

     SELECT *

       FROM #YourTable

    Hope this helps...

     

     

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

  • July 18, 2007 at 8:30 am

    #720380

    Hi Jeff

    Thank you for your examples and code.

    It is working fine with Previous Item Start Date and Next Item Start Date.

    It should look for Previous Complete Date and Next item No Start Date.

    Thank you,

    Vijay

  • July 18, 2007 at 8:12 pm

    #720597

    Ok... you've got a good example... just change it

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

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

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