0

How to get the last step of a certain job and identify if the status of the last process of every job is equal to 1. If the status of the last process is equal to one then display the value.

JOB TABLE (prepressjobs)

+-----------+--------------+------------+
|    ID     | SchedNum     |  Item Name | 
+-----------+--------------+------------+
| 1         | JITE7ERUK    | Item 1     | 
| 2         | JV7FSW26Y    | Item 2     | 
| 3         | JQFXV4H3X    | Item 3     | 
+-----------+--------------+------------+

OPERATION TABLE (operation)

+-----------+--------------+------------+-----------+----------+
|    ID     | SchedNum     |  Job Name  |   Step    |  Status  |
+-----------+--------------+------------+-----------+----------+
| 1         | JITE7ERUK    | Designing  | 0         | 1        |
| 2         | JITE7ERUK    | Sample     | 1         | 1        |
| 3         | JITE7ERUK    | Printing   | 2         | 0        |
| 4         | JV7FSW26Y    | Designing  | 0         | 1        |
| 5         | JV7FSW26Y    | Sample     | 1         | 0        |
| 6         | JQFXV4H3X    | Designing  | 0         | 1        |
| 7         | JQFXV4H3X    | Sample     | 1         | 1        |
+-----------+--------------+------------+-----------+----------+

MY QUERY

SELECT *
FROM operation p1
INNER JOIN prepressjobs 
ON prepressjobs.pj_schednum = p1.operation_schednum 
WHERE EXISTS(SELECT MAX(operation_step) FROM (SELECT DISTINCT(operation_schednum) FROM operation )p2)
AND p1.operation_status = 0 ;

EXPECTED RESULT

+-----------+--------------+------------+-----------+----------+
| 7         | JQFXV4H3X    | Sample     | 1         | 1        |
+-----------+--------------+------------+-----------+----------+
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Ailyn
  • 265
  • 1
  • 6
  • 17
  • Is it mysql or MS SQL ? – Aparna Aug 07 '17 at 05:00
  • @Aparna, mySQL. – Ailyn Aug 07 '17 at 05:03
  • @Aparna . It it most likely getting the last row like using this code `SELECT m1.* FROM operation m1 LEFT JOIN operation m2 ON (m1.operation_schednum = m2.operation_schednum AND m1.operation_id < m2.operation_id) WHERE m2.operation_id IS NULL ;` but I need also to get if the `operation_status` is equal to 1. – Ailyn Aug 07 '17 at 05:13
  • Just posted the code in SQL Server ...Try converting that to @mysql – Aparna Aug 07 '17 at 05:20
  • Igoes to an error. sql esyntax – Ailyn Aug 07 '17 at 05:25
  • @Aparna . I'm still on query and I'm on test now. I'm using this `SELECT m1.* FROM operation m1 LEFT JOIN operation m2 ON (m1.operation_schednum = m2.operation_schednum AND m1.operation_id < m2.operation_id) LEFT JOIN prepressjobs ON (prepressjobs.pj_schednum = m2.operation_schednum ) WHERE m1.operation_status=1 AND m2.operation_id IS NULL ;` and I just need now to join the prerpressjobs table. Can you help me? – Ailyn Aug 07 '17 at 05:28
  • Looks like mysql does not have the row_number() functions as in other dbs please read the link https://stackoverflow.com/questions/5347565/how-to-get-a-row-rank which might throw some light .Sorry for not being to help here – Aparna Aug 07 '17 at 05:30
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/151231/discussion-between-ailyn-and-aparna). – Ailyn Aug 07 '17 at 05:31
  • The query you posted works fine for me.Cant join in chat...access restricted – Aparna Aug 07 '17 at 05:34
  • Sorry @Ailyn,chat.stackoverflow is blocked at my workplace . – Aparna Aug 07 '17 at 05:52
  • @Aparna.Finally I got it. `SELECT * FROM operation m1 LEFT JOIN operation m2 ON (m1.operation_schednum = m2.operation_schednum AND m1.operation_id < m2.operation_id) LEFT JOIN prepressjobs m3 ON m1.operation_schednum = m3.pj_schednum WHERE m1.operation_status=1 AND m2.operation_id IS NULL ; ` – Ailyn Aug 07 '17 at 05:59
  • 1
    ,thank you for letting me know this .Great to see you could uncover this yourself.... – Aparna Aug 07 '17 at 07:34

2 Answers2

0

Try the below.This works in SQL Server 

 select * from 
 (
 select *,row_number() over(partition by schednum order by step desc ) as rn 
 from operations 
 ) a 
 where a.rn=1 and a.status=1;
Aparna
  • 286
  • 1
  • 11
0

Finally I got it on my own. But still thanks to @Aparna for respons. In addition I've got an idea at Retrieving the last record in each group and JOIN three tables.

QUERY:

SELECT * FROM operation t1
INNER JOIN prepressjobs 
ON prepressjobs.pj_schednum = t1.operation_schednum 
WHERE EXISTS(SELECT * FROM operation p2 WHERE p2.operation_step =   IF((t1.operation_step - 1)=-1,0,(t1.operation_step - 1) ) AND p2.operation_status = 1)
AND t1.operation_processname ='SINGLE DIELINE'
AND t1.operation_status = 0
AND prepressjobs.pj_status ='APPROVED'
Ailyn
  • 265
  • 1
  • 6
  • 17