CODEWITHSUNDEEP
sqlsql-server
Praveen Kumar
Praveen Kumar

Reputation: 37

SQL Query to fetch the records with success status

I have a table below. Daily 3 different files will run and status will be inserted into the table as Success/Failure. I need all the 3 files with status success and fetch that date. Here as per below table SQL query has to return Date 2020-02-25 (as all 3 files on that are a success)

Please help me with the Query, I tried many queries but not able to get the desired result.

ID  Pipeline_name   Status      UpdatedDate
1   Student_Dump    SUCCESS     2020-02-27
2   Teacher_Dump    SUCCESS     2020-02-27
3   Subjects_Dump   Failed      2020-02-27  
4   Student_Dump    SUCCESS     2020-02-26
5   Teacher_Dump    Failed      2020-02-26
6   Subjects_Dump   SUCCESS     2020-02-26  
7   Student_Dump    SUCCESS     2020-02-25
8   Teacher_Dump    SUCCESS     2020-02-25
9   Subjects_Dump   SUCCESS     2020-02-25

Upvotes: 2

Views: 2614

Answers (5)

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

Demo on db<>fiddle

You can use Rownumber() to achieve it

Select ID,Pipeline_name,Status,UpdatedDate
from
(
  select *,row_number() over(partition by Status,UpdatedDate order by id) as rn
  from #table1
)A where rn=3 and Status = 'SUCCESS'

Updated

If you need the latest data date only, you can select TOP 1 combines with ORDER BY ID DESC

Select TOP 1 ID,Pipeline_name,Status,UpdatedDate
from
(
  select *,row_number() over(partition by Status,UpdatedDate order by id) as rn
  from #table1
)A where rn=3 and Status = 'SUCCESS'
ORDER BY ID DESC

Output here

Upvotes: 1

Tyron78
Tyron78

Reputation: 4187

Try this one:

WITH cteStatus AS(
  SELECT UpdatedDate, Status, ROW_NUMBER() OVER (PARTITION BY UpdatedDate, Status ORDER BY UpdatedDate, Status) rn
    FROM T1
 )
SELECT c.UpdatedDate
  FROM cteStatus c
  WHERE Status = N'SUCCESS'
    AND rn = 3

The idea is to get all dates, where you have three records per Date with Status Success.

Here a link to Fiddle: http://sqlfiddle.com/#!18/a25c2b/5/1

Upvotes: 2

Naveen Kumar
Naveen Kumar

Reputation: 2006

Try this:

select UpdatedDate from test where UpdatedDate not in 
(select UpdatedDate from test where status='Failed');

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can do aggregation :

select UpdatedDate
from table t
group by UpdatedDate
having sum(case when statue = 'failed' then 1 else 0 end) = 0 and
       count(distinct Pipeline_name) = 3;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

You can use row_number()

Select * from
(
  select *,row_number() over(parition by updatedate,pipeline_name order by id) as rn
  from tablename
  where status='SUCCESS'
)A where rn=1

Upvotes: 0

Related Questions