# SQL Server 2008- Help calculating total for a day - group by day

Hi.. I need assistance , on how I can calculate the total miles driven for a given day by a car.

I need the total miles driven in a day for each Car

Here's what the data looks like

Car         DateTime             Job #                 Odometer Reading at End of Job
FORD      11/21/2013          1                               67
FORD       11/21/2013        2                               239
FORD         11/21/2013        3                             500
FORD          11/22/2013        4                            700
FORD          11/22/2013         5                          1100

The result I'm looking for would look like this

Car           DateTime                                  Miles Driven

FORD      11/21/2013                                   500
FORD     11/22/2013                                   600

thx!
LVL 1
###### Who is Participating?

Commented:
Assuming All starts at 0:
``````declare @logbook table (Car varchar(50), LogDate DateTime, Job_no int, OdometerReadingEOJ int);

insert into @logbook values ('FORD', '21-Nov-2013', 1, 67);
insert into @logbook values ('FORD', '21-Nov-2013', 2, 239);
insert into @logbook values ('FORD', '21-Nov-2013', 3, 500);
insert into @logbook values ('FORD', '22-Nov-2013', 4, 700);
insert into @logbook values ('FORD', '22-Nov-2013', 5, 1100);

select t1.Car
, t1.LogDate
, MAX(t1.OdometerReadingEOJ) - IsNull((SELECT top 1 OdometerReadingEOJ FROM @logbook i2 WHERE i2.Car = t1.Car AND i2.LogDate < t1.LogDate ORDER BY i2.Job_no desc), 0)
from @logbook t1
group by t1.Car, t1.LogDate
order by t1.Car, t1.LogDate
``````
0

Commented:
Thanks J
Just one more note: This SQL also assumes that your Job No is numeric and get sequentially bigger with time. Hope that is the case?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.