Post reply

Help on Update/Query

  • September 22, 2008 at 5:10 pm

    #191216

    Hi,

    Can anybody help me out with the below issue please?

    I'm using SQl server 2005. Please find the attached file for the data sample and my comments. Basically i need an update command or select query to achive the result. If you can give an idea how i can get the desired result that would be a great help.

    Thanks a lot for the help in advance.

    --Neela

  • September 22, 2008 at 6:19 pm

    #875348

    Question of the day...

    You have severely limited the amount of help you can get by...

    1. Posting as a spreadsheet

    2. Posting as a spreadsheet that many can't read

    3. Not posting according to the methods recommended in the link below in my signature

    4. All the above

    --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 23, 2008 at 12:39 pm

    #875964

    Sorry for the mistakes. I'll post it correctly.

  • September 23, 2008 at 1:02 pm

    #875976

    Can anybody help me with the below issue?

    --===== Create the test table with

    CREATE TABLE [dbo].[mytable](

    [date] [datetime] NULL,

    [Area] [nchar](10) NULL,

    [Amount] [nchar](10) NULL,

    [AOD] [datetime] NULL

    ) ON [PRIMARY]

    --===== Insert the test data into the test table

    Insert into mytable

    SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Jan 1 2008 12:00AM' UNION ALL

    SELECT 'Feb 1 2008 12:00AM','BA ','50 ','Jan 1 2008 12:00AM' UNION ALL

    SELECT 'Mar 1 2008 12:00AM','BA ','40 ','Jan 1 2008 12:00AM' UNION ALL

    SELECT 'Apr 1 2008 12:00AM','BA ','30 ','Jan 1 2008 12:00AM' UNION ALL

    SELECT 'May 1 2008 12:00AM','BA ','20 ','Jan 1 2008 12:00AM' UNION ALL

    SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Feb 1 2008 12:00AM' UNION ALL

    SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Feb 1 2008 12:00AM' UNION ALL

    SELECT 'Mar 1 2008 12:00AM','BA ','30 ','Feb 1 2008 12:00AM' UNION ALL

    SELECT 'Apr 1 2008 12:00AM','BA ','40 ','Feb 1 2008 12:00AM' UNION ALL

    SELECT 'May 1 2008 12:00AM','BA ','50 ','Feb 1 2008 12:00AM' UNION ALL

    SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL

    SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL

    SELECT 'Jan 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL

    SELECT 'Feb 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL

    SELECT 'Mar 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL

    SELECT 'Apr 1 2008 12:00AM','BA ','0 ','Apr 1 2008 12:00AM' UNION ALL

    SELECT 'May 1 2008 12:00AM','BA ','20 ','Apr 1 2008 12:00AM' UNION ALL

    SELECT 'Mar 1 2008 12:00AM','BA ','0 ','Mar 1 2008 12:00AM' UNION ALL

    SELECT 'Apr 1 2008 12:00AM','BA ','20 ','Mar 1 2008 12:00AM' UNION ALL

    SELECT 'May 1 2008 12:00AM','BA ','10 ','Mar 1 2008 12:00AM'

    --Select the record using the below statement

    Select * from MyTable Order by AOD,Date

    I want to update the Amount column which has value zero with previous month's(AOD column) value (dont want to hardcode it)

    For example

    I want to Update

    Select Amount from MyTable Where AOD='02/01/2008' and date='02/01/2008'

    with

    Select Amount from mytable where AOD='01/01/2008' and date='02/01/2008'

    Final result should look like this

    DateAreaAmountAOD

    2008-01-01 00:00:00.000BA 0 2008-01-01 00:00:00.000

    2008-02-01 00:00:00.000BA 50 2008-01-01 00:00:00.000

    2008-03-01 00:00:00.000BA 40 2008-01-01 00:00:00.000

    2008-04-01 00:00:00.000BA 30 2008-01-01 00:00:00.000

    2008-05-01 00:00:00.000BA 20 2008-01-01 00:00:00.000

    2008-01-01 00:00:00.000BA 0 2008-02-01 00:00:00.000

    2008-02-01 00:00:00.000BA 50 2008-02-01 00:00:00.000

    2008-03-01 00:00:00.000BA 30 2008-02-01 00:00:00.000

    2008-04-01 00:00:00.000BA 40 2008-02-01 00:00:00.000

    2008-05-01 00:00:00.000BA 50 2008-02-01 00:00:00.000

    2008-01-01 00:00:00.000BA 0 2008-03-01 00:00:00.000

    2008-02-01 00:00:00.000BA 50 2008-03-01 00:00:00.000

    2008-03-01 00:00:00.000BA 30 2008-03-01 00:00:00.000

    2008-04-01 00:00:00.000BA 20 2008-03-01 00:00:00.000

    2008-05-01 00:00:00.000BA 10 2008-03-01 00:00:00.000

    2008-01-01 00:00:00.000BA 0 2008-04-01 00:00:00.000

    2008-02-01 00:00:00.000BA 50 2008-04-01 00:00:00.000

    2008-03-01 00:00:00.000BA 30 2008-04-01 00:00:00.000

    2008-04-01 00:00:00.000BA 20 2008-04-01 00:00:00.000

    2008-05-01 00:00:00.000BA 20 2008-04-01 00:00:00.000

    Thanks alot for the help

    Neela

  • September 23, 2008 at 11:27 pm

    #876118

    I didn't forget ya... I just had a really long day and haven't been able to do the necessary writeup along with the code. I'll finish this when I get home later "tonight". It's 01:25 here right now and I need a nap.

    --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 24, 2008 at 6:40 pm

    #876746

    I think this might do it...

    SELECT hi.Date,

    hi.Area,

    CASE WHEN hi.Amount = 0 THEN ISNULL(lo.Amount,0) ELSE hi.Amount END AS Amount,

    hi.AOD

    FROM MyTable lo

    RIGHT JOIN MyTable hi ON lo.Date = hi.Date

    AND lo.AOD = DATEADD(mm,-1,hi.AOD)

    ORDER BY hi.AOD,hi.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)

  • September 24, 2008 at 8:56 pm

    #876764

    Thanks alot for the reply Jeff.

    this query worked really well. But i still have the zeros in amount field for the below records.

    DateAreaAmountAOD

    2008-02-01 BA 0 2008-03-01

    2008-02-01 BA 0 2008-04-01

    2008-03-01 BA 0 2008-04-01

    These needs to be filled with previous month's amounts.

    with below records respectively

    2008-02-01 BA 50 2008-02-01

    2008-02-01 BA 50 2008-03-01

    2008-03-01 BA 30 2008-03-01

    Thanks alot for the help.

    Neela

  • September 24, 2008 at 11:12 pm

    #876778

    Ok... let's do this... here's the original data... mark each zero row with the rownum from where it's supposed to get it's data. If you leave any at zero, please explain why... and tell me if I have them sorted correctly... right now, I have them sorted by AOD and Date which met the requirements of your second post.

    RowNum UpdateFrom date Area Amount AOD

    --------- ---------- ----------------------- ---------- ---------- -----------------------

    1 2008-01-01 00:00:00.000 BA 0 2008-01-01 00:00:00.000

    2 2008-02-01 00:00:00.000 BA 50 2008-01-01 00:00:00.000

    3 2008-03-01 00:00:00.000 BA 40 2008-01-01 00:00:00.000

    4 2008-04-01 00:00:00.000 BA 30 2008-01-01 00:00:00.000

    5 2008-05-01 00:00:00.000 BA 20 2008-01-01 00:00:00.000

    6 2008-01-01 00:00:00.000 BA 0 2008-02-01 00:00:00.000

    7 2008-02-01 00:00:00.000 BA 0 2008-02-01 00:00:00.000

    8 2008-03-01 00:00:00.000 BA 30 2008-02-01 00:00:00.000

    9 2008-04-01 00:00:00.000 BA 40 2008-02-01 00:00:00.000

    10 2008-05-01 00:00:00.000 BA 50 2008-02-01 00:00:00.000

    11 2008-01-01 00:00:00.000 BA 0 2008-03-01 00:00:00.000

    12 2008-02-01 00:00:00.000 BA 0 2008-03-01 00:00:00.000

    13 2008-03-01 00:00:00.000 BA 0 2008-03-01 00:00:00.000

    14 2008-04-01 00:00:00.000 BA 20 2008-03-01 00:00:00.000

    15 2008-05-01 00:00:00.000 BA 10 2008-03-01 00:00:00.000

    16 2008-01-01 00:00:00.000 BA 0 2008-04-01 00:00:00.000

    17 2008-02-01 00:00:00.000 BA 0 2008-04-01 00:00:00.000

    18 2008-03-01 00:00:00.000 BA 0 2008-04-01 00:00:00.000

    19 2008-04-01 00:00:00.000 BA 0 2008-04-01 00:00:00.000

    20 2008-05-01 00:00:00.000 BA 20 2008-04-01 00:00:00.000

    --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 25, 2008 at 9:21 am

    #877095

    Good Morning Jeff,

    Here is my explanation:

    We have zero for rownum 1. I want to keep this as zero as we dont have data for AOD= 2007-12-01(Previous month of 2008-01-01). This zero should be there for rownum 6 ,11, 16.

    rownum 7 should be updated with the amount in rownum 2 which is 50

    rownum 12 should be updated with the amount in rownum 7 which is 50(the value we just updated)

    rownum 13 should be updated with the amount in rownum 8 which is 30

    rownum 17 should be updated with the amount in rownum 12 which is 50

    rownum 18 should be updated with the amount in rownum 13 which is 30

    rownum 19 should be updated with the amount in rownum 14 which is 20

    Thanks a lot for the your time to solve this. I really appreciate it.

    ---Neela

  • September 26, 2008 at 5:51 am

    #877570

    Hmmm... alright, Neela... thanks. I've got this. I'm on my way to work... I'll bang it out tonight.

    --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 26, 2008 at 9:59 am

    #877815

    Cross Post http://www.sqlservercentral.com/Forums/Topic574818-338-1.aspx

  • September 27, 2008 at 7:15 am

    #878075

    Thanks... I hate it when that happens... just splits resources and makes it difficult for others to search for a correct answer.

    Since the other post has more activity, I'll continue this on the other post. If anyone else sees this, please don't post anything else here... go to the other post that ggraber identified above.

    Thanks.

    --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 27, 2008 at 11:10 pm

    #878129

    After looking at the first comments by RBAR, I have deleted the this post. Then i added a new post for the same as per the guidelines by RBAR. After psoting new one, i noticed that this post is so there and not been deleted. :unsure:

    Sorry for the confusion.

    --Neela

  • September 28, 2008 at 5:45 am

    #878142

    Only the moderators can delete an entire thread. If you do create a new post on an existing issue, please post in the old thread and put the url of the new one.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 14 posts - 1 through 13 (of 13 total)

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