CODEWITHSUNDEEP
sqlsql-server
sriki
sriki

Reputation: 81

SQL MIN Datetime based on first occuranceof a value in another column

This is what I have

ID  Name    DateTime    Value   Group

1   Mark    1/1/2010     0        1
2   Mark    1/2/2010     1        1
3   Mark    1/3/2010     0        1
4   Mark    1/4/2010     0        2
40  Mark    1/5/2010     1        2
5   Mark    1/9/2010     1        2
6   Mark    1/6/2010     1        2
7   Kelly   1/1/2010     0        3
8   Kelly   1/2/2010     1        3
9   Kelly   1/3/2010     1        3
10  Nancy   1/4/2010     0        4
11  Nancy   1/5/2010     0        4
12  Nancy   1/6/2010     1        5 
13  Nancy   1/7/2010     0        5

What I want is to get the rows per "name" per "group" with minimum datetime after the value becomes 1. From the above example, I would need to get

3   Mark    1/3/2010     0        1
6   Mark    1/6/2010     1        2
9   Kelly   1/3/2010     1        3
13  Nancy   1/7/2010     0        5

Upvotes: 1

Views: 1175

Answers (4)

roman
roman

Reputation: 117606

using window functions:

with cte as (
    select
        *,
        row_number() over(partition by [Group], Name order by [DateTime]) as rn,
        dense_rank() over(order by [Group], Name) as rnk
    from Table1
)
select c1.*
from cte as c1
     inner join cte as c2 on c2.rn = c1.rn - 1 and c2.rnk = c1.rnk and c2.Value = 1
where
    not exists (select * from cte as c3 where c3.rn <= c1.rn - 2 and c3.rnk = c1.rnk and c3.Value = 1)

or apply:

select t1.*
from Table1 as t1
    cross apply (
        select top 1 t2.Value, t2.DateTime
        from Table1 as t2
        where
            t2.[Group] = t1.[Group] and t2.Name = t1.Name and
            t2.[DateTime] < t1.[DateTime]
        order by t2.[Datetime] desc
    ) as t2
where
    t2.Value = 1 and
    not exists (
        select *
        from Table1 as t3
        where
            t3.[Group] = t1.[Group] and t3.Name = t1.Name and
            t3.[DateTime] < t2.[DateTime] and t3.Value = 1
    )

sql fiddle demo

update forgot to mention that your output seems to be incorrect - there should id = 6 instead of 5 in second row (see sql fiddle).

Upvotes: 0

Krzysztof
Krzysztof

Reputation: 16150

In SQL Server 2012 you can do this:

SELECT * FROM (
  SELECT DISTINCT
    ID,
    Name,
    DateTime,
    Value,
    Gr,
    LAG(ID) OVER (PARTITION BY Name, Gr ORDER BY DateTime) F
  FROM (
    SELECT
      ID,
      Name,
      DateTime,
      Value,
      Gr,
      CASE WHEN LAG(Value) OVER (PARTITION BY Name, Gr ORDER BY DateTime) = 1 THEN 1 ELSE 0 END F
    FROM
      T
  ) TT

  WHERE F = 1

) TT WHERE F IS NULL

ORDER BY Gr, Name, DateTime

Fiddle: http://www.sqlfiddle.com/#!6/5a0fa2/19

Upvotes: 0

Registered User
Registered User

Reputation: 8395

Based on the description of your rules, I believe the output will actually be a bit different since 2010-01-05 was the first DateTime where the Value = 1 for Group 2 for Mark.

ID  Name    DateTime    Value   Group
3   Mark    2010-01-03  0       1
6   Mark    2010-01-06  1       2
9   Kelly   2010-01-03  1       3
13  Nancy   2010-01-07  0       5

The below code will work as demonstrated in this SQLFiddle. 

SELECT sub.ID
    , sub.Name
    , sub.[DateTime]
    , sub.Value
    , sub.[Group]
FROM 
    (SELECT t.ID
        , t.Name
        , t.[DateTime]
        , t.Value
        , t.[Group]
        , SequentialOrder = ROW_NUMBER() OVER
            (PARTITION BY t.Name, t.[Group]
            ORDER BY t.[DateTime])
    FROM Test t
    JOIN 
        (SELECT Name
            , [Group]
            , MinimumDateTime = MIN([DateTime])
        FROM Test
        WHERE Value = 1
        GROUP BY Name
            , [Group]) mint
        ON t.Name = mint.Name
        AND t.[Group] = mint.[Group]
    WHERE t.[DateTime] > mint.MinimumDateTime) sub
WHERE sub.SequentialOrder = 1
ORDER BY ID;

Upvotes: 1

Santhosh
Santhosh

Reputation: 1791

Below is my query and it goes on assumption that records are received in order of their dates

WITH TBL_1 AS
(
SELECT A.*, ROW_NUMBER() OVER(PARTITION BY NAME, GROUP ORDER BY DATE) AS RN
FROM TABLE
WHERE (NAME, GROUP) IN
(SELECT NAME, GROUP FROM TABLE WHERE VALUE = 1)
),
TBL_2 AS
(
SELECT * FROM TBL_1 WHERE VALUE = 1
),
TBL_3 AS
(
SELECT A.*
FROM TBL_1 AS A
INNER JOIN TBL_2 AS B
ON  B.NAME = A.NAME
AND B.GROUP = A.GROUP
AND A.RN > B.RN
)

SELECT *
FROM TBL_3
WHERE (NAME, GROUP, DATE) IN
(SELECT NAME, GROUP, MIN(DATE) FROM TBL_3 GROUP BY NAME, GROUP)

Upvotes: 0

Related Questions