CODEWITHSUNDEEP
mysql
esoj
esoj

Reputation: 11

How to query latest records from a table

I have a table similar to this:

ID  User_Code   ProcessID   DateCreated              Remarks
1     AAA           1        2020-01-01 08:40        N/A
2     AAA           2        2020-01-01 09:34        R123
3     AAA           2        2020-01-01 10:40        SUCCESS
4     AAA           3        2020-01-01 11:00        N/A
5     BBB           1        2020-01-01 11:01        N/A
6     BBB           1        2020-01-01 11:10        N/A 
7     BBB           2        2020-01-01 11:20        SUCCESS  
8     BBB           3        2020-01-01 11:30        N/A
9     CCC           1        2020-01-01 11:31        N/A
10    CCC           2        2020-01-01 11:40        R001 
11    CCC           2        2020-01-01 11:43        R002

What I want to accomplish is to create a result like this

TransDate  Remarks UserCode Process1 Process2 Process3
2020-01-01 SUCCESS AAA         OK       OK       OK   
2020-01-01 SUCCESS BBB         OK       OK       OK   
2020-01-01 R001    CCC         OK       OK       NOK

Where if there is a Record for a Process for the particular UserCode, the column value to be put is OK else if there is no Record, the value would be NOK. Also the Remarks pertains only to Process2, where the value should be the latest Remarks. The problem is it is not outputting the correct result instead it is displaying like this

TransDate  Remarks UserCode Process1 Process2 Process3
2020-01-01         AAA         OK       OK       OK   
2020-01-01 SUCCESS BBB         OK       OK       OK   
2020-01-01 R001    CCC         OK       OK       NOK

See below created SQL:

SELECT UserCode , DATE(DateCreated) AS TransDate,
IF (COUNT(
CASE
WHEN PROCESS = 1
THEN 1
ELSE NULL
END
) > 0, "OK", "NOK" ) AS 'Process1',
IF (COUNT(
CASE
WHEN PROCESS = 2
THEN 1
ELSE NULL
END
) > 0, "OK", "NOK" ) AS 'Process2',
IF (COUNT(
CASE
WHEN PROCESS = 3
THEN 1
ELSE NULL
END
) > 0, "OK", "NOK" )AS 'Process3'
FROM MyTable WHERE UserCode  = '######'
GROUP BY DATE(DateCreated)

Sorry got confused, got the wrong data. the record should be

ID  User_Code   ProcessID   DateCreated              Remarks
1     AAA           1        2020-01-01 08:40        N/A
2     AAA           2        2020-01-01 09:34        R123
3     AAA           2        2020-01-01 10:40        SUCCESS
4     AAA           3        2020-01-01 11:00        N/A
5     AAA           1        2020-01-02 11:01        N/A
6     AAA           1        2020-01-02 11:10        N/A 
7     AAA           2        2020-01-02 11:20        SUCCESS  
8     AAA           3        2020-01-02 11:30        N/A
9     AAA           1        2020-01-03 11:31        N/A
10    AAA           2        2020-01-03 11:40        R001 
11    AAA           2        2020-01-03 11:43        R002  
12    BBB           1        2020-01-03 11:32        N/A
13    BBB           2        2020-01-03 11:38        SUCCESS
14    BBB           3        2020-01-03 11:38        N/A

And the result of the query should be similar to this because the query should be based on a usercode.

TransDate  Remarks UserCode Process1 Process2 Process3
2020-01-01 SUCCESS AAA         OK       OK       OK   
2020-01-02 SUCCESS AAA         OK       OK       OK   
2020-01-03 R002    AAA         OK       OK       NOK

Upvotes: 0

Views: 66

Answers (2)

P.Salmon
P.Salmon

Reputation: 17665

The easy bit is to use conditional aggregation to transform the rows to columns the harder bit joins to a sub query which works out the last id per user_code and plucks the remarks.

select min(date(datecreated)) dt,
         max(x.remarks),
         t.user_Code,
         max(case when processid = 1 then 'ok' else 'nok' end) process1,
         max(case when processid = 2 then 'ok' else 'nok' end) process2,
         max(case when processid = 3 then 'ok' else 'nok' end) process3 
from t 
left join 
    (
     select t.user_code,remarks
     from t
     join
      (select t1.user_code ucid, max(t1.id) maxid from t t1 where processid = 2 group by t1.user_Code) s
      on s.ucid = t.user_code and s.maxid = t.id
    ) x on x.user_code = t.user_code

group by t.user_code;

note I have assumed that the remarks in your desired output is incorrect given that the latest remark for user_Code ccc is r002

Upvotes: 1

Akina
Akina

Reputation: 42834

SELECT DISTINCT
       MAX(DATE(DateCreated)) OVER (PARTITION BY User_Code) TransDate,
       FIRST_VALUE(CASE ProcessID WHEN 2 THEN Remarks END) OVER (PARTITION BY User_Code ORDER BY CASE ProcessID WHEN 2 THEN DateCreated END DESC) Remarks,
       User_Code,
       CASE WHEN MAX(CASE WHEN ProcessID = 1 THEN Remarks END) OVER (PARTITION BY User_Code) IS NULL THEN 'NOK' ELSE 'OK' END Process1,
       CASE WHEN MAX(CASE WHEN ProcessID = 2 THEN Remarks END) OVER (PARTITION BY User_Code) IS NULL THEN 'NOK' ELSE 'OK' END Process2,
       CASE WHEN MAX(CASE WHEN ProcessID = 3 THEN Remarks END) OVER (PARTITION BY User_Code) IS NULL THEN 'NOK' ELSE 'OK' END Process3
FROM test;

TransDate  Remarks User_Code Process1 Process2 Process3
2020-01-01 SUCCESS AAA       OK       OK       OK
2020-01-01 SUCCESS BBB       OK       OK       OK
2020-01-01 R002    CCC       OK       OK       NOK

fiddle

Upvotes: 0

Related Questions