amount difference between 2 dats

  • Hi All,

    I have a table like this

    Date Amount

    09/01/2008 500

    09/02/2008 1500

    09/03/2008 3500

    I want a output which is as follows -

    Date Amount

    09/02/2008 1000

    09/03/2008 2000

    where the amount column is the diference from the previous date so in this example 2nd sept amount is the difference bet 2nd and 1st sept so also for 3rd sept amount.

    Can some help to write a query for this.

  • Hi Mathew

    What do you have in the way of pk / indexes in this table?

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • no PK and Indexes are there.

  • mathewspsimon (9/11/2008)


    no PK and Indexes are there.

    That pretty much identifies this as a homework question. Rather than solve this for you, here's an article which discusses more than one method for dealing with this type of problem, and the relative merits and drawbacks of each. Read the article, try one or two of the methods, and if you're still unsure, then repost. Good luck!

    http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • 2000 or 2005?

    What happens if a date is missing?

    Is it homework?

    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
  • this is 2000 and its not homework.the first table is a sales table which gets updated w when a sale happnes.to arrive at the sale for a specific date I have to get the difference from previous date.

    also these were created,triggers wrote before.i will not be able to modifiy them

  • Please post SQL 2000 questions in the SQL 2000 forums in the future. Posting in the 2005 forums means that people will give you 2005-specific solutions.

    Are the dates always consecutive? Is it possible for there ever to be a date missing? Do the dates have various times associated with them, or is the time always set to midnight?

    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
  • Hi,

    Yes,dates can miss and it is not consecutive.Also dates are all midnight.

  • Any chance of duplicate dates?

    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
  • no chance for duplicates

  • Does this work?

    -- first, some setup.

    CREATE TABLE #Amounts (

    SomeDate DATETIME,

    SomeAmount INT

    )

    insert into #Amounts values ('2008/09/01',500)

    insert into #Amounts values ('2008/09/02',1500)

    insert into #Amounts values ('2008/09/03',3500)

    -- Setup finished

    CREATE TABLE #WorkTable (

    RowID INT IDENTITY PRIMARY KEY,

    SomeDate DATETIME,

    SomeAmount INT

    )

    INSERT INTO #WorkTable (SomeDate, SomeAmount)

    SELECT SomeDate, SomeAmount

    FROM #Amounts

    ORDER BY SomeDate

    SELECT CurrentDay.SomeDate, CurrentDay.SomeAmount-PreviousDay.SomeAmount from #WorkTable PreviousDay inner join #WorkTable CurrentDay

    on PreviousDay.RowID = CurrentDay.RowID - 1

    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
  • Thanks Gila

Viewing 12 posts - 1 through 11 (of 11 total)

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