CODEWITHSUNDEEP
sqlt-sql
Sam
Sam

Reputation: 1514

T-sql How to get all rows 'between' two other rows

Suppose I have the following table:

Table: Score
field: ID: uniqueidentifier
field: Departmentid: int
field: Score: float
field: EnteredOn: DateTime

How can I devise a query which gives me all scores that are between an entry in department 115 and department 119?

To clarify: if I have the following records:

Id, departmentid, score
<some guid>, 115, 1
<some guid>, 100, 2
<some guid>, 119, 3
<some guid>, 115, 2
<some guid>, 102, 1
<some guid>, 119, 4
<some guid>, 115, 2
<some guid>, 100, 4
<some guid>, 120, 4

The query would need to retrieve the following records:

<some guid>, 100, 2
<some guid>, 102, 1

because they are between 115 and 119 records.

The records will be sorted on EntereOn by default.

Upvotes: 3

Views: 732

Answers (3)

MatBailie
MatBailie

Reputation: 86808

I wanted to avoid correlated sub-queries, but with the need for multiple records between 115 and 119, I think it's needed. Here is an alternative to MarkBanister's answer (using one correlated sub-query, rather than two, but with three joins instead of two).

I haven't tested which performs better.

SELECT
  data_between.*
FROM
  Score      AS data_115
INNER JOIN
  Score      AS data_119
    ON data_119.EnteredOn = (SELECT MIN(EnteredOn) FROM Score WHERE DepartmentId IN (115, 119) AND EnteredOn > data_115.EnteredOn)
INNER JOIN
  Score      AS data_between
    ON  data_between.EnteredOn > data_115.EnteredOn
    AND data_between.EnteredOn < data_119.EnteredOn
WHERE
    data_115.DepartmentId = 115
AND data_119.DepartmentId = 119

Upvotes: 2

user359040
user359040

Reputation:

Try:

select m.*
from MyTable m
join (select ms.EnteredOn StartDate,
             (select min(me.EnteredOn) 
              from MyTable me 
              where me.Departmentid = 119 and 
                    me.EnteredOn > ms.EnteredOn) EndDate
      from MyTable ms
      where ms.Departmentid = 115) mr
on m.EnteredOn > mr.StartDate and m.EnteredOn < mr.EndDate
where not exists
(select null
 from MyTable mn 
 where mn.Departmentid = 115 and 
       mn.EnteredOn > mr.StartDate and 
       mn.EnteredOn < mr.EndDate)

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

It's not pretty but it works for your sample data.

declare @Score table
(
  ID int identity primary key,
  DepartmentID int,
  Score int,
  EnteredOn int
)

insert into @Score values
(115, 1, 1),
(100, 2, 2),
(119, 3, 3),
(115, 2, 4),
(102, 1, 5),
(119, 4, 6),
(115, 2, 7),
(100, 4, 8),
(120, 4, 9)

;with C1 as
(
  select *,
         row_number() over(order by EnteredOn) as rn
  from @Score
), C2 as
(
  select rn,
         row_number() over(order by EnteredOn) as rn2
  from C1
  where DepartmentID = 115
), C3 as
(
  select rn,
         row_number() over(order by EnteredOn) as rn2
  from C1
  where DepartmentID = 119 and rn > (select min(rn) from C2)
), C4 as
(
  select C2.rn as FromRn,
         C3.rn as ToRn
  from C2 
    inner join C3
      on C2.rn2 = C3.rn2
)
select C1.ID, C1.DepartmentID, C1.Score
from C1
  inner join C4
    on C1.rn > C4.FromRn and
       C1.rn < C4.ToRn

Upvotes: 3

Related Questions