question

pagee33 avatar image
pagee33 asked

Help with query

ops.jpg

Where there is a Sub Ops Y I would like to get the Ops previous to that so in case of Job A I want Ops 1 and 2 and in case of B it would only be Ops 1

I can easily get the Sub Ops Y and all sub ops N but not sure how I iterate through to check the Sub Ops value with a Y Ops number and select previous ones within that Job

Any suggestions? Sorry probably quite straight forward.

sql query
ops.jpg (20.0 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Can you please post data as a script rather than an image / file, most people will not download a file from an unknown person due to security.


Based on your sample data and expected outcome, the below will work.


declare @jobs table (job char(1), ops tinyint, subops char(1))
insert into @jobs values
('A',1,'N'),
('A',2,'N'),
('A',3,'Y'),
('A',4,'N'),
('B',1,'N'),
('B',2,'Y'),
('B',3,'N')

SELECT 
n.*
FROM
@Jobs n
INNER JOIN
(
SELECT job, ops FROM @Jobs WHERE subops = 'Y'
) as y
on n.job = y.job
and n.ops < y.ops
and n.subops = 'N'


The question from me would be what if the job had 2 subops = 'Y', e.g 'A',5,'Y'

Do you want 1,2,4 to be returned, 1,2,3,4 or just 4.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pagee33 avatar image
pagee33 answered

Thanks for your response, apologies for the image.

So at the moment I would only want the Ops prior to the first sub op Y but it maybe that your example is valid somewhere in the data set and I think I would want 1 2 4 as that would be prior to the second sub op yes but not include a sub op yes

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

So then you want to experiment with MIN & MAX on the inner join select on the Ops column to pull the first or last subop Y.


So if you want the first sub op its MIN (eg 1,2), if you want all from the latest sub op its MAX (eg 1,2,4)

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.