Post reply

help on query..

  • December 14, 2006 at 3:22 pm

    #176051

    Hi,

    I have 2 problems and thought of seeking help here..

    I have a table called InvMngt and it has tons of records.some of it came from excel sheets, some came from notepads and it also gets updated daily from a oracle source.

    fields in the table are:

    productid nvarchar(20) (PK)

    [Date] nvarchar(10)

    Qty int(4)

    userid nvarchar(10)

    sample data:

    ProductID Date Qty userid

    100A 2006-07-16 10 fromnotepad

    100A 1/1/2006 10 excelimport

    100A 1/1/2006 20 excelimport

    100A 2006-11-25 10 fromoracle

    100A 2006-11-26 2 fromoracle

    now, my questions are:

    a.I need to put a negative value to the qty that comes from userid "fromoracle".

    b.I also need to correct the date values of "1/1/2006" to the format "2006-01-01".

    Your help is appreciated.

    Thanks,

  • December 14, 2006 at 3:40 pm

    #678229

    declare @table table (

        productid nvarchar(20),

        [Date] nvarchar(10) ,

        Qty int,

        userid nvarchar(20)

    )

    insert into @table

    select '100A', '2006-07-16', 10, 'fromnotepad' union all

    select '100A', '1/1/2006', 10, 'excelimport' union all

    select '100A', '1/1/2006', 20, 'excelimport' union all

    select '100A', '2006-11-25', 10, 'fromoracle' union all

    select '100A', '2006-11-26', 2, 'fromoracle'

    select * from @table

    update @table

    set Qty = -1

    where userid = 'fromoracle'

    update @table

    set [date] = CONVERT(varchar(10),CAST([date] as datetime),20)

    select * from @table

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • December 14, 2006 at 3:54 pm

    #678230

    Thanks John..the update for date worked perfect..the one for quantity failed..but made a small change to ur query and it worked fine too..

    i modified it as

    update @table

    set Qty = (Qty * -1)

    where userid = 'fromoracle'

    Thanks a lot.

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

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