Post reply

amount difference between 2 dats

  • September 11, 2008 at 4:16 am

    #192650

    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.

  • September 11, 2008 at 4:29 am

    #870192

    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

  • September 11, 2008 at 4:32 am

    #870195

    no PK and Indexes are there.

  • September 11, 2008 at 4:38 am

    #870200

    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

  • September 11, 2008 at 4:45 am

    #870206

    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

  • September 11, 2008 at 4:50 am

    #870210

    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

  • September 11, 2008 at 5:15 am

    #870225

    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

  • September 11, 2008 at 5:20 am

    #870228

    Hi,

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

  • September 11, 2008 at 6:12 am

    #870243

    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

  • September 11, 2008 at 6:25 am

    #870255

    no chance for duplicates

  • September 11, 2008 at 9:45 am

    #870429

    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

  • September 14, 2008 at 12:34 am

    #871520

    Thanks Gila

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

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