hi.. i have 4 tables
shipment_assignment
field = id , member_id,partner_id,vehicle_id,shipment_no, begin_date_time,end_date_time,create_date,active
members
field = member_name
vehicles
field = vehicle_name
trips
field = vehicle_id , start_date_ time , stop_date_time, distance
--------------------------------------------------------------------
Then I have Design in my view with some join table i want sum distance in table trips based between begin_date_time and end_date_time IN start_date_time > from trips
SELECT sa.id
,sa.shipment_no
,v.vehicle_name
,m.member_name
,sa.begin_date_time
,sa.create_date
,sa.member_id
,sa.partner_id
,sa.vehicle_id
,sa.end_date_time
,sa.active
, CONVERT(VARCHAR(5),DateDiff(dd, sa.begin_date_time, sa.end_date_time)) + ' Days '
+ CONVERT(VARCHAR(5),DateDiff(hh, sa.begin_date_time, sa.end_date_time) % 24) + ' hours '
+ CONVERT(VARCHAR(5),DateDiff(mi, sa.begin_date_time, sa.end_date_time) % 60) + ' minutes '
+ CONVERT(VARCHAR(5),(DATEDIFF(S, sa.begin_date_time, sa.end_date_time)%60))+' seconds' As TotalDiff
FROM dbo.shipment_assignment AS sa INNER JOIN
dbo.vehicles AS v ON v.vehicle_id = sa.vehicle_id INNER JOIN
dbo.members AS m ON sa.partner_id = m.member_id