0

First table

 Complaint_id   Status  Reopen
    127493          5   1
    127494          5   0

Second table

Parent_id   Currentstatus   Reopen  datetime    usertype
127493  0   0   2019-04-08 12:40:31.209103+05:30    c
127493  5   1   2019-04-08 12:40:31.209103+05:30    c
127493  0   0   2019-04-08 12:36:22.662995+05:30    c
127493  5   1   2019-04-08 12:36:22.662995+05:30    c
127493  0   0   2019-04-08 12:34:25.459444+05:30    c
127493  5   1   2019-04-08 12:34:25.459444+05:30    c
127493  5   0   2019-04-08 12:29:58.581771+05:30    E
127493  5   0   2019-05-08 12:29:58.581771+05:30    E

Output

complaintid currenstatus usertype
127493        0            c

I am trying using below query

SELECT
 currentstatus
FROM
  (
    SELECT
      Currentstatus,
      Datetime,
      Open_reopen_status,
      Parent_id,
      MAX(Datetime) OVER (PARTITION BY Parent_id) AS MaxDatetime
    FROM
      secondtable
  ) AS derived
WHERE Datetime = MaxDatetime

but i don't know how to connect with first table basically i want the records form first table is where reopen=1

select * from firsttable where reopen=1 

and fetch the current status and usertype from the second table according to max datetime

0
SELECT t1.Complaint_id,
       t2.currentstatus,
       t2.usertype
FROM firsttable t1
JOIN ( SELECT currentstatus, 
              usertype,
              Parent_id
       FROM ( SELECT Currentstatus,
                     Datetime,
                     Parent_id,
                     usertype,
                     MAX(Datetime) OVER (PARTITION BY Parent_id) AS MaxDatetime
              FROM secondtable
            ) AS derived 
       WHERE Datetime = MaxDatetime
     ) t2 ON t1.Complaint_id = t2.Parent_id
         AND t1.reopen = 1

?

PS. Does secondtable (Parent_id, Datetime) is unique by index/constraint?

PPS. Use CTE to make the query more readable...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.