0

Vessel data table showing the cargo balance daywise

The source table structure is as defined in the image. I have fetched the data grouped by VesselID, with max function applied on cargodate(Date), as shown in the next image.enter image description here

Now I need balance of the row fetched using group by. Please help.

  • What balance do you expect to get for a group? SUM() or the one associated with the date picked by the MAX()? – jkavalik Nov 9 '15 at 11:40
0

According to your SQL statement you always get a unique date from a group VesseleID since you are using MAX from group. By using join statement on date you can get your desired result.

Explanation:

-- Complete Data Set
1. Select * from DailyYardBalTable; 

-- Identical to your Query
2. SELECT VesseleID, Max(CargoDate) as CargoDate FROM DailyYardBalTable 
   WHERE CargoDate<'2015-11-10 16:47:49.493' GROUP BY VesseleID 

--Desired Result Set

3. SELECT T2.VesseleID,T2.CargoDate,T1.Balance FROM DailyYardBalTable T1
   INNER JOIN 
    (SELECT VesseleID, Max(CargoDate) as CargoDate FROM DailyYardBalTable
    WHERE CargoDate<'2015-11-10 16:47:49.493' GROUP BY VesseleID) T2
  ON T1.CargoDate=T2.CargoDate GROUP BY T2.VesseleID,T2.CargoDate,T1.Balance

Here is the data set respective to the SQL statement #1, #2, #3.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.