CODEWITHSUNDEEP
sqlsql-servert-sql
LAffair
LAffair

Reputation: 1998

SQL Select where first value and last value of daterange

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

SELECT b.Name, a.*
FROM a INNER JOIN b ON a.VId = b.Id
WHERE  b.SId = 100
       AND a.Day >= '2016-05-05'
       AND a.Day < '2016-05-09';

and adding 2 more columns to the first selected data (SCap and ECap from table c). From what I've tried my code looks like this:

    SELECT b.Name,a.*,
       c.MaxDay,
       c.Cap,
FROM   a INNER JOIN b
       ON a.VId = b.Id
       INNER JOIN
       (SELECT   VId,
                 MAX(TimestampLocal) AS MaxDay,
                 CAST (TimestampLocal AS DATE) AS Day,
                 Cap,
        FROM Info
        GROUP BY VId, 
                 CAST (TimestampLocal AS DATE), 
                     Cap) AS c
       ON a.VId = c.VId
          AND a.Day = c.Day
WHERE  b.SId = 33
       AND a.Day >= '2016-05-05'
       AND a.Day < '2016-05-09';

But I get more rows than needed.

I need the earliest and the latest TimestampLocal for a given vehicle in a daterange. That would come from two records in Info, and each would have its proper values for Cap. For example: I have two Names with values inside table Info for 2 days (Name 1) and only one day for Name 2:

- Day 1 2016-05-07:
    - Name 1: Values at 2:45, 10:10 and 3.10
    - Name 2: Values at 5:13 and 8:22
- Day 2 2016-05-09:
    - Name 1: Values at 4:13, 6:15 and 9:20

I need to display (if I select daterange: 2016-05-05 to 2016-05-09) for Name 1 for SCap the value from 2016-05-07 from 2:45 and for ECap the value from 2016-05-09 from 9:20 and for Name 2 for SCap the value from 2016-05-07 from 5:13 and for ECap the value from 2016-05-07 from 8:22. This should be displayed in 2 lines.

Is there a way I can add those 2 columns into my query without adding more rows for the same Name?

EDIT! Table a I have:

VId   | Day
5251  | 05/09/2016
5382  | 05/09/2016

Table b:

Id    | Name
5251  | N1
5382  | N2

Table Info: 

VId   | TimestampLocal                      |  Cap
5251  | 2016-05-09 11:33:46.2000000 +03:00  |   0
5251  | 2016-05-09 11:37:11.4000000 +03:00  |   7
5251  | 2016-05-09 11:38:11.4000000 +03:00  |   4
5251  | 2016-05-09 11:39:11.7000000 +03:00  |   2
5382  | 2016-05-09 09:30:56.6000000 -04:00  |   5
5382  | 2016-05-09 09:31:56.6000000 -04:00  |   3

And I need to display - if I select a daterange from 2016-05-03 to 2016-05-10:

Id    | Name | SCap | ECap
5251  | N1   | 0    | 2
5382  | N2   | 5    | 3

Upvotes: 3

Views: 649

Answers (3)

LAffair
LAffair

Reputation: 1998

I found a solution for my query after some further investigations and here is what I managed to do:

SELECT      b.Id,
            b.Name,
            c.S_Time,
            c.E_Time,
            s.Cap AS S_Cap,
            e.Capa AS E_Cap,
FROM        b
INNER JOIN   (SELECT  VId,
                     MIN(TimestampLocal) AS S_Time,
                     MAX(TimestampLocal) AS E_Time
            FROM     Info
            where CAST (TimestampLocal AS DATE) >='2016-04-05'
            and CAST (TimestampLocal AS DATE) <'2016-05-10'
            GROUP BY VId
            ) AS c
        ON  b.Id = c.VId
INNER JOIN   Info AS s
        ON  s.VId = c.VId
        AND s.TimestampLocal = c.S_Time
INNER JOIN   Info AS e
        ON  e.Vd = c.VId
        AND e.TimestampLocal = c.E_Time
WHERE   b.SId = 100

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

First, get the max and min timestamp for each id for a given date. Then join it twice, once with the max timestamp value and then with the min timestamp value for each id to tblinfo,to get the start and end cap values for those rows.

Add a where clause with a date filter to filter for specific days.

Sample demo

with maxandmin as 
(select vid
        ,max(timestamplocal) maxtime
        ,min(timestamplocal) mintime
 from tblinfo
 group by vid,cast(timestamplocal as date))
select m.vid,b.name,i1.cap as Scap,i2.cap as Ecap
from maxandmin m 
join tblinfo i1 on m.vid=i1.vid and m.mintime = i1.timestamplocal
join tblinfo i2 on m.vid=i2.vid and m.maxtime = i2.timestamplocal
join tableb b on b.id=m.vid

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can use a CTE with ROW_NUMBER in order to retrieve the two records:

;WITH Info_CTE AS (
   SELECT   VId,            
            CAST (TimestampLocal AS DATE) AS Day,
            Cap,
            ROW_NUMBER() OVER (PARTITION BY VId 
                               ORDER BY TimestampLocal) AS rn1,
            ROW_NUMBER() OVER (PARTITION BY VId 
                               ORDER BY TimestampLocal DESC) AS rn2
   FROM Info
)
SELECT b.Name,
       a.*,
       c.Day,
       c.Cap
FROM  a 
INNER JOIN b ON a.VId = b.Id
INNER JOIN Info_CTE AS c ON a.VId = c.VId AND 
                            a.Day = c.Day AND
                            1 IN (c.rn1, c.rn2)
WHERE  b.SId = 33
       AND a.Day >= '2016-05-05'
       AND a.Day < '2016-05-09';

If you want ECap and SCap as separate columns then instead of 

c.Cap

you can use:

CASE WHEN c.rn1 = 1 THEN c.Cap END AS ECap,
CASE WHEN c.rn2 = 1 THEN c.Cap END AS SCap

Upvotes: 0

Related Questions