CODEWITHSUNDEEP
sqlsql-server
troey
troey

Reputation: 23

SQL query min and max group by flag

I have a table as below :

Table

How can I craft a SQL select statement so that MIN AND MAX EVENT DATE groups results by FLAG (0,1)?

So the result would be:

Result

Upvotes: 2

Views: 252

Answers (2)

kc2018
kc2018

Reputation: 1460

An alternative if Window function does not work:

 SELECT 
       t1.card_no, t1.descr_reader,
       t1.event_date date_in,
       (select top 1 event_date from test t2
        where t2.card_no = t1.card_no and
              t2.reader_no = t1.reader_no and
              t2.descr_reader = t1.descr_reader and
              t2.event_date > t1.event_date and
              t2.flag = 1
        order by t2.event_date ) as date_out
FROM test t1 
WHERE t1.flag = 0

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Just do conditional aggregation with use of window function 

SELECT card_no, descr_reader,
       max(CASE WHEN flag = 0 THEN event_date END) date_in,
       max(CASE WHEN flag = 1 THEN event_date END) date_out 
FROM
(
     SELECT *,
               COUNT(flag) OVER (PARTITION BY flag ORDER BY id) Seq
     FROM table t
)t
GROUP BY card_no, descr_reader, Seq

Upvotes: 2

Related Questions