sub - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sub

Author

Topic

zypsy

6 Posts

Posted - 2008-05-08 : 05:57:59

ques

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 06:02:48

What will happen if you have more than two records with Same VehicleID,TransactDate? You need the aggregated value for new variables?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-08 : 11:23:41

[code];
With CTE (RowNo,VehID,TrDt,TrTime,Odometer,TrCity,TrState) AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY VehID,TrDt ORDER BY TrTime) AS RowNo,
TrDt,TrTime,Odometer,TrCity,TrState
FROM YourTable
)

SELECT c1.VehID,
c1.TrDt,
c1.TrTime,
DATEDIFF(ss,ISNULL(c2.TrTime,c1.trTime),c1.trTime) AS TimeDiff,
c1.Odometer,
ISNULL((c2.Odometer-c1.Odometer),0) AS Miles,
c1.TrCity,
c1.TrState
FROM CTE c1
LEFT JOIN CTE c2
ON c2.VehID=c1.VehID
AND c2.TrDt=c1.TrDt
AND c2.RowNo=c1.RowNo + 1[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources