Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

amount difference between 2 dats Expand / Collapse
Author
Message
Posted Thursday, September 11, 2008 4:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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 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.
Post #567580
Posted Thursday, September 11, 2008 4:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 7,219, Visits: 13,684
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
Exploring Recursive CTEs by Example Dwain Camps
Post #567584
Posted Thursday, September 11, 2008 4:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 7,219, Visits: 13,684
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
Exploring Recursive CTEs by Example Dwain Camps
Post #567595
Posted Thursday, September 11, 2008 4:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
2000 or 2005?
What happens if a date is missing?

Is it homework?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #567602
Posted Thursday, September 11, 2008 4:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
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

Post #567624
Posted Thursday, September 11, 2008 5:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
Any chance of duplicate dates?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #567648
Posted Thursday, September 11, 2008 6:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 4:31 AM
Points: 89, Visits: 231
no chance for duplicates
Post #567660
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse