InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 12:56:51 GMT20http://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspxThanks GilaSun, 14 Sep 2008 00:34:28 GMTmathewspsimonhttp://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspxDoes this work?[code]-- 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 finishedCREATE 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[/code]Thu, 11 Sep 2008 09:45:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspxno chance for duplicatesThu, 11 Sep 2008 06:25:45 GMTmathewspsimonhttp://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspxAny chance of duplicate dates?Thu, 11 Sep 2008 06:12:14 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspxHi,Yes,dates can miss and it is not consecutive.Also dates are all midnight.Thu, 11 Sep 2008 05:20:16 GMTmathewspsimonhttp://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspxPlease 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?Thu, 11 Sep 2008 05:15:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspxthis 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 themThu, 11 Sep 2008 04:50:31 GMTmathewspsimonhttp://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspx2000 or 2005?What happens if a date is missing?Is it homework?Thu, 11 Sep 2008 04:45:47 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspx[quote][b]mathewspsimon (9/11/2008)[/b][hr]no PK and Indexes are there.[/quote]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![url]http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]CheersChrisMThu, 11 Sep 2008 04:38:11 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspxno PK and Indexes are there.Thu, 11 Sep 2008 04:32:39 GMTmathewspsimonhttp://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspxHi MathewWhat do you have in the way of pk / indexes in this table?CheersChrisMThu, 11 Sep 2008 04:29:16 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic567580-1291-1.aspxHi All,I have a table like thisDate Amount09/01/2008 50009/02/2008 150009/03/2008 3500I want a output which is as follows -Date Amount09/02/2008 100009/03/2008 2000where 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.Thu, 11 Sep 2008 04:16:51 GMTmathewspsimon