Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 amount difference between 2 dats Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, September 11, 2008 4:16 AM
 SSC Journeyman Group: General Forum Members Last Login: Wednesday, May 9, 2012 4:31 AM Points: 89, Visits: 231
 Hi 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.
Post #567580
 Posted Thursday, September 11, 2008 4:29 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 Hi MathewWhat do you have in the way of pk / indexes in this table?CheersChrisM “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #567584
 Posted Thursday, September 11, 2008 4:32 AM
 SSC Journeyman Group: General Forum Members Last Login: Wednesday, May 9, 2012 4:31 AM Points: 89, Visits: 231
 no PK and Indexes are there.
Post #567588
 Posted Thursday, September 11, 2008 4:38 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 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/CheersChrisM “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #567595
 Posted Thursday, September 11, 2008 4:45 AM
 SSC-Forever Group: General Forum Members Last Login: 2 days ago @ 5:18 AM Points: 45,619, Visits: 44,147
 2000 or 2005?What happens if a date is missing?Is it homework? Gail ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #567602
 Posted Thursday, September 11, 2008 4:50 AM
 SSC Journeyman Group: General Forum Members Last Login: Wednesday, May 9, 2012 4:31 AM Points: 89, Visits: 231
 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
Post #567606
 Posted Thursday, September 11, 2008 5:15 AM
 SSC-Forever Group: General Forum Members Last Login: 2 days ago @ 5:18 AM Points: 45,619, Visits: 44,147
 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 ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #567624
 Posted Thursday, September 11, 2008 5:20 AM
 SSC Journeyman Group: General Forum Members Last Login: Wednesday, May 9, 2012 4:31 AM Points: 89, Visits: 231
 Hi,Yes,dates can miss and it is not consecutive.Also dates are all midnight.
Post #567627
 Posted Thursday, September 11, 2008 6:12 AM
 SSC-Forever Group: General Forum Members Last Login: 2 days ago @ 5:18 AM Points: 45,619, Visits: 44,147
 Any chance of duplicate dates? Gail ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #567648
 Posted Thursday, September 11, 2008 6:25 AM
 SSC Journeyman Group: General Forum Members Last Login: Wednesday, May 9, 2012 4:31 AM Points: 89, Visits: 231
 no chance for duplicates
Post #567660

 Permissions