date diferent between 2 records

[angjbsca]

here is my data:
Date Type total Change
5/17 s 5037 1 =(5/17-5/16)
5/16 s 5038 1 =(5/16-5/15)
5/15 s 5049 3 =(5/15-5/12)
5/12 s 5043 0 =....


I need the change field, the field is the dife. between him and the next record.

 

[vbkris]

try this:
select Date,Type,Total,(select top 1 OutsideTable.Date-InsideTable.Date from Table1 InsideTable where InsideTable.Date<OutSideTable.Date) Change
from
Table1 OutsideTable

Known is handfull, Unknown is worldfull

 

[angjbsca]

the query take the top date and rest to the older one I need to rest the top agains the next one and on.

 

[gmmastros]

To accomplish this, I would put the data in to a table variable with an identity column so you can perform a self join on that identity column. Once you do this, the calculations become easy.

I have provided an example for you to look at. In the example, I create an @Temp table variable to store some sample input data. In your final query, you would want to change @Temp with your actual table name.

[green]-- Sample data for demonstration purposes[/green]
Declare @Temp Table(Date DateTime, Type VarChar(1), Total Integer)

Insert into @Temp Values('5/17/2006','s',5037)
Insert into @Temp Values('5/16/2006','s',5038)
Insert into @Temp Values('5/15/2006','s',5049)
Insert into @Temp Values('5/12/2006','s',5043)

[green]-- The Query[/green]
Declare @Data 
Table   (RowId Integer Identity(1,1), 
        Date DateTime, 
        Type VarChar(1), 
        Total Integer)

Insert Into @Data(Date, Type, Total)
Select Date, Type, Total
From   @Temp
Order By Date DESC

Select A.Date,
       A.Type,
       IsNull(B.Total - A.Total, 0) As Difference,
       IsNull(DateDiff(day, B.Date, A.Date), 0) As DateDifference
From   @Data A
       Left join @Data B
         On A.RowId = B.RowId - 1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

My approach would be this
Instead of date have From and Todate in the table
On the first insert the FromDate gets a date and the Todate a NULL
create an insert trigger on the table
when a insert occurs the triggers closes out the previuos entry by putting the date into the ToDate column and insert a new Row with the new date and a NULL for the Todate

that way it's very easy to do basic things for example
1 show all active rows
select * from table where ToDate IS NULL

2 do the calculation you wanted
select datediff(d,Fromdate, ToDate) from table

And that's it


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[angjbsca]

good work fine