1

Trying to get the distinct row from multiple tables, depending on Job_Status. i want to only job_status=60 and not in job_status>=90. Sample

 STORENAME JOBNO SEQ JOB_STATUS ABC 743 1 20 ABC 743 2 30 ABC 743 3 60 ABC 743 4 60 ABC 743 5 90 ABC 771 1 20 ABC 771 2 20 ABC 771 3 60 ABC 771 4 60 ABC 895 1 10 ABC 895 2 20 ABC 895 3 30 ABC 895 4 30 ABC 895 5 30 ABC 895 6 30 ABC 895 7 30 ABC 895 8 20 ABC 895 9 30 ABC 895 10 30 ABC 895 11 30 ABC 895 12 30 ABC 895 13 60 ABC 895 14 90 ABC 895 15 90

Return expected

 STORENAME JOBNO SEQ JOB_STATUS ABC 771 4 60

what i am tried is below

select Distinct * from ( Select UL.StoreName, GD.Job_No, GD.STOREID, GD.Warranty from dbo.SERVICEJOB GD Inner Join dbo.JOBTRACKING AS JT ON JT.JOB_NO = GD.JOB_NO INNER JOIN dbo.DataDetails AS UL ON GD.STOREID = UL.STOREID WHERE (JT.JOB_STATUS=60) AND JT.JOB_STATUS!>90 Group By UL.StoreName,GD.Job_No,GD.STOREID,GD.Warranty ) As U

For me returning all means status 90 and more also.

;WITH T AS ( SELECT *, CASE WHEN Job_Status>=90 THEN 0 ELSE 1 END AS IsConsider FROM SERVICEJOB GD Inner Join dbo.JOBTRACKING AS JT ON JT.JOB_NO= GD.JOB_NO INNER JOIN dbo.DataDetails AS UL ON GD.StoreID = UL.StoreID ) SELECT StoreName, JOB_NO, SEQ, Job_Status FROM ( SELECT StoreName, JOB_NO,	TRACKING_SEQ Job_Status, ROW_NUMBER() OVER (PARTITION BY JOB_NO ORDER BY SEQ DESC) JobPartNo FROM T WHERE Job_Status=60 AND JOB_NO NOT IN (SELECT JOB_NO FROM T WHERE IsConsider=0) ) AS X WHERE JobPartNo=1

Improve this question
5
  • tried already same result. Commented Apr 13, 2017 at 12:13
  • use JT.JOB_STATUS=60 in where clause Commented Apr 13, 2017 at 12:18
  • 2
    From where comes 4190753771 in your expected result? Commented Apr 13, 2017 at 12:21
  • sorry thats copied from real result Commented Apr 13, 2017 at 12:21
  • Can you please check my answer I think it works for you Commented Apr 13, 2017 at 12:42

3 Answers 3

Reset to default
1 
create table jobs (STORENAME varchar(10), JOBNO int, SEQ int, JOB_STATUS int); insert into jobs values ('ABC', 743, 1, 20), ('ABC', 743, 2, 30), ('ABC', 743, 3, 60), ('ABC', 743, 4, 60), ('ABC', 743, 5, 90), ('ABC', 771, 1, 20), ('ABC', 771, 2, 20), ('ABC', 771, 3, 60), ('ABC', 771, 4, 60), ('ABC', 895, 1, 10), ('ABC', 895, 2, 20), ('ABC', 895, 3, 30), ('ABC', 895, 4, 30), ('ABC', 895, 5, 30), ('ABC', 895, 6, 30), ('ABC', 895, 7, 30), ('ABC', 895, 8, 20), ('ABC', 895, 9, 30), ('ABC', 895, 10, 30), ('ABC', 895, 11, 30), ('ABC', 895, 12, 30), ('ABC', 895, 13, 60), ('ABC', 895, 14, 90), ('ABC', 895, 15, 90); GO 
 24 rows affected 
SELECT DISTINCT STORENAME, JOBNO, JOB_STATUS FROM jobs WHERE JOB_STATUS = 60 AND JOBNO NOT IN (SELECT JOBNO FROM jobs WHERE JOB_STATUS >= 90) GO 
 STORENAME | JOBNO | JOB_STATUS :-------- | ----: | ---------: ABC | 771 | 60

dbfiddle here

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

It will not work for Seq column as OP required in final result
I know, but it returns two different SEQ for the same STORENAME, JOBNO and JOB_STATUS, a DISTINCT is not possible if OP doesn't change premises on his question.
0

What you want is to find jobs that have a status 60 and no status > 90:

select job_no from dbo.jobtracking group by job_no having count(case when job_status = 60 then 1 end) > 0 and count(case when job_status > 90 then 1 end) = 0;

The complete query:

select ul.storename, gd.job_no, gd.storeid, gd.warranty from dbo.servicejob gd join dbo.datadetails ul on gd.storeid = ul.storeid where gd.job_no in ( select jt.job_no from dbo.jobtracking jt group by jt.job_no having count(case when jt.job_status = 60 then 1 end) > 0 and count(case when jt.job_status > 90 then 1 end) = 0 );
Improve this answer

2 Comments

Each GROUP BY expression must contain at least one column that is not an outer reference. getting error like this.
Well, the query looks fine to me. Both job_number and job_status are columns of table jobtracking. The first query works and the second doeesn't, I assume? Maybe SQL Server is buggy as to name scope and you need qualifiers. I have edited the second query accordingly.
0

Use ROW_NUMBER() With Partition and after eliminate rows which has JobStatus>=90

;WITH T AS ( SELECT *, CASE WHEN JobStatus>=90 THEN 0 ELSE 1 END AS IsConsider FROM @tblTest ) SELECT StoreName, JobNo, Seq, JobStatus FROM ( SELECT StoreName, JobNo, Seq, JobStatus, ROW_NUMBER() OVER (PARTITION BY JobNo ORDER BY Seq DESC) JobPartNo FROM T WHERE JobStatus=60 AND JobNo NOT IN (SELECT JobNo FROM T WHERE IsConsider=0) ) AS X WHERE JobPartNo=1

Output:

enter image description here

Improve this answer

6 Comments

really i dont understand your query, how i can try this.
first check with your data if it works let me know i will explain logic
sorry to ask like this what is @tblTest ?
You can replace with your table, i have test with your data and created temp table with name @tblTest
check my question. i add your answer thats what you mean?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.