SQL Query to fetch the records with success status

Question

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

You can use `RowNumber()` – Nguyễn Văn Phong Feb 27 '20 at 08:50 — Nguyễn Văn Phong, Feb 27 '20 at 08:50

score 2 · Answer 1 · answered Feb 27 '20 at 08:56

2

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

answered Feb 27 '20 at 08:56

Tyron78

4,117
2
17
32

So, what is the catch there? The data in your example looks same as before or am I missing something? – Tyron78 Feb 27 '20 at 09:47
Above Query works. Now i need to convert to LINQ. Can you help me. – Praveen Kumar Feb 27 '20 at 14:32
Unfortunately I only have a little experience with LINQ, but here might be another stack overflow thread which might help you: https://stackoverflow.com/questions/9980568/row-number-over-partition-by-xxx-in-linq – Tyron78 Feb 27 '20 at 14:43

Nguyễn Văn Phong · Accepted Answer · 2020-02-28T04:30:55.997

1

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

edited Feb 28 '20 at 04:30

answered Feb 27 '20 at 08:57

Nguyễn Văn Phong

13,506
17
39
56

The above Query works. Thanks for the details. now i added below datas into the exisiting. This is added as per my data available in my table. Now after running the query 2 records it gives. but i need the latest data date only. (10, 'Student_Dump', 'SUCCESS', '2020-02-19'), (11, 'Teacher_Dump', 'FAILED', '2020-02-19'), (12, 'Teacher_Dump', 'SUCCESS', '2020-02-19'), (13, 'Subjects_Dump', 'SUCCESS', '2020-02-19'), – Praveen Kumar Feb 27 '20 at 15:45
I've just updated my answer, please take a look at @Praveen Kumar – Nguyễn Văn Phong Feb 28 '20 at 04:31

score 0 · Answer 3 · answered Feb 27 '20 at 08:51

0

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

answered Feb 27 '20 at 08:51

Fahmi

37,315
5
22
31

1

This won't give the correct result - you will always get the first record per date and pipeline with Success, not the date with Success for all three pipelines... see fiddle for details: http://sqlfiddle.com/#!18/a25c2b/8/1 – Tyron78 Feb 27 '20 at 08:59

score 0 · Answer 4 · answered Feb 27 '20 at 08:53

0

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;

answered Feb 27 '20 at 08:53

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 5 · answered Feb 27 '20 at 08:53

0

Try this:

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

answered Feb 27 '20 at 08:53

Naveen Kumar

1,988
1
7
11

SQL Query to fetch the records with success status

5 Answers5