I have a table with travel details. Details are getting saved in distributed manner. I need to merge the rows based on Source and Destination. My Source is A and Final Destination is D, I need to merge all the 3 rows into 1 with sum of time and distance. Here is an example.

Table #1: trip details

Table #2: TravelPlan

Output needed:

I tried using concatenate but not able to do based on conditions. Not sure how to combine rows of one table based on values of another. 'Between' is also not giving me desired output.

CodePudding user response：

Using your example data to construct DDL and DML (which is really useful for questions like this):

DECLARE @TripDetails TABLE (CarID INT, Source NVARCHAR(20), Destination NVARCHAR(20), Distance DECIMAL(5,2), Time DECIMAL(5,2), Date DATE)
INSERT INTO @TripDetails (CarID, Source, Destination, Distance, Time, Date) VALUES
(1, 'A', 'P', 10, 1, '1 Jan 2022'),
(1, 'P', 'R', 20, 2, '1 Jan 2022'),
(1, 'R', 'D', 30, 3, '1 Jan 2022'),
(2, 'S', 'A', 20, 1, '1 Jan 2022'),
(2, 'A', 'F', 10, 2, '1 Jan 2022'),
(2, 'F', 'G', 30, 3, '1 Jan 2022'),
(2, 'S', 'A', 10, 1, '2 Jan 2022')

DECLARE @TripPlan TABLE (CarID INT, Source NVARCHAR(20), Destination NVARCHAR(20), Date DATE)
INSERT INTO @TripPlan (CarID, Source, Destination, Date) VALUES
(1, 'A', 'D', '1 Jan 2022'),
(2, 'S', 'G', '1 Jan 2022'),
(2, 'S', 'A', '2 Jan 2022')

This then becomes a fairly straight forward JOIN and GROUP BY operation.

SELECT tp.CarID, tp.Source, tp.Destination, tp.Date, SUM(t.Distance) AS Distance, SUM(t.Time) AS Time
  FROM @TripPlan tp
    INNER JOIN @TripDetails t
      ON tp.CarID = t.CarID
      AND tp.Date = t.Date
 GROUP BY tp.CarID, tp.Source, tp.Destination, tp.Date

CarID   Source  Destination Date        Distance    Time
--------------------------------------------------------        
1       A       D           2022-01-01  60.00       6.00
2       S       A           2022-01-02  10.00       1.00
2       S       G           2022-01-01  60.00       6.00

To deviate from the question a little: I changed from the obvious data types for both Distance and Time as I could see both values needing to be expressed as decimals. There is no indication in the example data as to what the units for these columns is. Detailing the units in your column names is a good idea, it's pretty much self documenting that way. If we're recording Time in minutes, say so in the column name: TimeMinutes, if we're recording distance in kilometers: DistanceKM.