CODEWITHSUNDEEP
sql-servert-sql
LAffair
LAffair

Reputation: 1998

SQL Select where the date is min and max

I'm new to SQL queries and I need to make a join starting from this query: 

SELECT b.Name AS VehicleName,
       a.*
FROM   VehicleDay AS a
       INNER JOIN
       Vehicles AS b
       ON a.VehicleId = b.Id
WHERE  b.SiteId = 100
       AND a.Day >= '2016-04-22'
       AND a.Day < '2016-04-28';

and adding 6 more columns from the Info data table:

Starting (first value of the day) & Ending (last of day) - Cumulative [Cml] - Current Capacity [Cap] - Total charge [Chrg]

So I need to add on at the far right S Cml | E Cml | S Cap | E Cap | S Chrg | E Chrg

In the Info table I get values for the same VehicleId on different period of day and I need to select the first and the last data for each day in a daterange.

So far my query looks like this:

SELECT b.Name AS VehicleName,
       a.*,
       c.MaxDay,
       c.Cumulative,
       c.CurrentCapacity,
       c.TotalCharge
FROM   VehicleDay AS a
       INNER JOIN
       Vehicles AS b
       ON a.VehicleId = b.Id
       INNER JOIN
       (SELECT   VehicleId,
                 MAX(TimestampLocal) AS MaxDay,
                 CAST (TimestampLocal AS DATE) AS Day,
                 Cumulative,
                 CurrentCapacity,
                 TotalCharge
        FROM     Info
        GROUP BY VehicleId, 
                 CAST (TimestampLocal AS DATE), 
                     Cumulative, CurrentCapacity, TotalCharge) AS c
       ON a.VehicleId = c.VehicleId
          AND a.Day = c.Day
WHERE  b.SiteId = 100
       AND a.Day >= '2016-04-22'
       AND a.Day < '2016-04-28';

But the second query returns more rows that the first one and I need to display the number of rows as the first one.

Is there a way I can add those 6 columns into my query without adding more rows?

Upvotes: 0

Views: 133

Answers (1)

trincot
trincot

Reputation: 351298

The reason for the duplicates is that you group also by Cumulative, CurrentCapacity, TotalCharge, which may have different values on the same day. So then that GROUP BY will serve multiple records per day and so multiply the records already selected from the two other tables.

You could first just get the MIN/MAX values of the time stamps, and then with these values, join the table Info twice again, once to retrieve the columns related to the MIN time, and once for those related to the MAX time:

SELECT      b.Name AS VehicleName,
            a.*,
            c.S_Time,
            c.E_Time,
            s.Cumulative      AS S_Cml,
            e.Cumulative      AS E_Cml,
            s.CurrentCapacity AS S_Cap,
            e.CurrentCapacity AS E_Cap,
            s.TotalCharge     AS S_Chrg,
            e.TotalCharge     AS E_Chrg
FROM        VehicleDay AS a
INNER JOIN  Vehicles AS b
        ON  a.VehicleId = b.Id
LEFT JOIN   (SELECT  VehicleId,
                     CAST (TimestampLocal AS DATE) AS Day,
                     MIN(TimestampLocal) AS S_Time,
                     MAX(TimestampLocal) AS E_Time
            FROM     Info
            GROUP BY VehicleId,
                     CAST (TimestampLocal AS DATE)
            ) AS c
        ON  a.VehicleId = c.VehicleId
        AND a.Day = c.Day
LEFT JOIN   Info AS s
        ON  s.VehicleId = c.VehicleId
        AND s.TimestampLocal = c.S_Time
LEFT JOIN   Info AS e
        ON  e.VehicleId = c.VehicleId
        AND e.TimestampLocal = c.E_Time
WHERE       b.SiteId = 100
        AND a.Day >= '2016-04-22'
        AND a.Day < '2016-04-28'

There is one condition: the combination VehicleId and TimestampLocal must be unique, or you'll still get more rows than expected.

Upvotes: 1

Related Questions