Requête SQL pour une ligne distincte

Essayer d'get la ligne distincte de plusieurs tables, en fonction de Job_Status. je veux seulement job_status = 60 et pas dans job_status> = 90. Échantillon

 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

Retour attendu

 STORENAME JOBNO SEQ JOB_STATUS
 ABC 771 4 60

ce que je suis essayé est ci-dessous

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

Pour moi, tout return signifie le statut 90 et plus aussi.

 ;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

 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 lignes affectées

 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 ici

Ce que vous voulez, c'est find des emplois qui ont un statut de 60 et aucun statut> 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;

La requête complète:

 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 );

Utilisez ROW_NUMBER () avec partition et après les lignes éliminées avec 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

Sortie:

entrez la description de l'image ici