asked
Find records with same violation on different date
My goal is to find facilities that have had the same violation more than once. I have the query showing me a list of facilities, in which some are what I need and some are not. I can't seem to get to the next step of only showing the records that meet the criteria. **Query so far:** select VIOLATION_CODE as "Violation", iv.FACILITY_ID as "Facility ID", cf.FACILITY_NAME as "Facility", cf.SITE_ADDRESS as "Address", cf.city as "City", cf.STATE as "State", cf.ZIP as "Zip", iv.ENTERED_DATE as "Entered On", iv.RELATED_RECORD_ID as "Program ID"--, COUNT(iv.FACILITY_ID) as "Count" from dba.TB_CORE_INSPECTION_VIOL iv inner join dba.TB_CORE_FACILITY cf on iv.FACILITY_ID = cf.FACILITY_ID where POINTS = '4' group by iv.FACILITY_ID, VIOLATION_CODE, cf.FACILITY_NAME, cf.SITE_ADDRESS, cf.city, cf.STATE, cf.ZIP, iv.ENTERED_DATE, iv.RELATED_RECORD_ID order by iv.FACILITY_ID, iv.VIOLATION_CODE asc **Query Results:** Violation - Facility 0023 - FA2121 0007 - FA2124 0007 - FA2124 0014 - FA2131 0021 - FA2131 0021 - FA2135 0007 - FA2141 0009 - FA2141 0009 -FA2141 0008- FA2141 0007 - FA2141 What I would like to see is only Facility FA2124 and FA2141 because that have the same violation more than once. Thanks in advance
You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
This is a pure case of finding duplicate rows. So you could do it by using various methods. I am demonstrating here by using CTE and Ranking Function. To make it simpler dump your initial result into a temp table and then apply cte and ranking function on top of that. Here is the query:- select VIOLATION_CODE as "Violation", iv.FACILITY_ID as "Facility ID", cf.FACILITY_NAME as "Facility", cf.SITE_ADDRESS as "Address", cf.city as "City", cf.STATE as "State", cf.ZIP as "Zip", iv.ENTERED_DATE as "Entered On", iv.RELATED_RECORD_ID as "Program ID" --, COUNT(iv.FACILITY_ID) as "Count" into #test_table from dba.TB_CORE_INSPECTION_VIOL iv inner join dba.TB_CORE_FACILITY cf on iv.FACILITY_ID = cf.FACILITY_ID where POINTS = '4' group by iv.FACILITY_ID, VIOLATION_CODE, cf.FACILITY_NAME, cf.SITE_ADDRESS, cf.city, cf.STATE, cf.ZIP, iv.ENTERED_DATE, iv.RELATED_RECORD_ID order by iv.FACILITY_ID, iv.VIOLATION_CODE asc ;WITH CTE AS ( SELECT Facility ID,ROW_NUMBER() OVER (PARTITION BY VIOLATION,[FACILITY ID] ORDER BY VIOLATION) RN FROM #test_table ) SELECT * FROM CTE WHERE RN>1
I would, like @erlokeshsharma08 use a CTE and the ROW_NUMBER-function. EDIT No, I would use GROUP BY and HAVING inside the CTE, based on later comments... /EDIT But I'm guessing you want all the details about the rows, and most of all all information about them. Then the CTE couldbe something like this: ;WITH CTE AS( SELECT COUNT(*) as cnt,[Facility ID],Violation FROM #test_table GROUP BY [Facility ID], Violation HAVING COUNT(*)>1 )SELECT t.* FROM #test_table t INNER JOIN CTE ON t.[facility id] = CTE.[facility id] AND t.violation = CTE.violation
Wow I don't think I would have ever figured that out. However, I am seeing rows that shouldn't be there. **Example:** **Violation - Facility** 0005 - FA2142 0007 - FA2142 0013 - FA2142 0013 - FA2142 0013 - FA2142 0015 - FA2142 With the new query I am seeing the row with violation 0005, 0007 and 0015. My goal is to only see the 0013 rows. My apologies. I just read my first post and I was not clear. Yes I want see those facilities that have the same violation more than once, but then only show me those violations.
Well I think @magnus's query will not fetch you the desired result because it is only being considering to faculty_id:- again 2 options 1. add ---> and violation in (select violation FROM CTE WHERE rownum>1) 2. see my edited answer below.
I've updated my answer. Now it uses a COUNT(*) instead of ROW_NUMBER, filtering out those facility/violation combinations which have more than one row.
;WITH CTE AS ( SELECT Facility ID,ROW_NUMBER() OVER (PARTITION BY VIOLATION,[FACILITY ID] ORDER BY VIOLATION) RN FROM #test_table ) SELECT (**all column names except for rn)** FROM CTE WHERE RN>1
Not sure what I am missting. Still getting the additional rows. Here is what I have after my initial query. ;WITH CTE AS( SELECT [Facility ID], ROW_NUMBER() OVER(PARTITION BY VIOLATION, [Facility ID] ORDER BY Violation) rn FROM #test_table ) SELECT * FROM #test_table WHERE [facility id] in (select [facility id] FROM CTE WHERE rn>1) and VIOLATION in (select violation FROM cte WHERE rn>1)
Try this:- ;WITH CTE AS( SELECT [Facility ID],**violation**, ROW_NUMBER() OVER(PARTITION BY VIOLATION, [Facility ID] ORDER BY Violation) rn FROM #test_table ) SELECT * FROM #test_table WHERE [facility id] in (select [facility id] FROM CTE WHERE rn>1) and VIOLATION in (select violation FROM cte WHERE rn>1)
Getting the same results. Query thus far - select VIOLATION_CODE as "Violation", iv.FACILITY_ID as "Facility ID", cf.FACILITY_NAME as "Facility", cf.SITE_ADDRESS as "Address", cf.city as "City", cf.STATE as "State", cf.ZIP as "Zip", iv.ENTERED_DATE as "Entered On", iv.RELATED_RECORD_ID as "Program ID" into #test_table from dba.TB_CORE_INSPECTION_VIOL iv inner join dba.TB_CORE_FACILITY cf on iv.FACILITY_ID = cf.FACILITY_ID where POINTS = '4' group by iv.FACILITY_ID, VIOLATION_CODE, cf.FACILITY_NAME, cf.SITE_ADDRESS, cf.city, cf.STATE, cf.ZIP, iv.ENTERED_DATE, iv.RELATED_RECORD_ID order by iv.FACILITY_ID, iv.VIOLATION_CODE asc ;WITH CTE AS ( SELECT [Facility ID], violation, ROW_NUMBER() OVER(PARTITION BY VIOLATION, [Facility ID] ORDER BY Violation) rn FROM #test_table ) SELECT * FROM #test_table WHERE [facility id] in (select [facility id] FROM CTE WHERE rn>1) and VIOLATION in (select violation FROM cte WHERE rn>1)
Try my original answer then. I think that should work for sure ;WITH CTE AS ( SELECT [Facility ID], violation, ROW_NUMBER() OVER(PARTITION BY VIOLATION, [Facility ID] ORDER BY Violation) rn FROM #test_table ) SELECT * FROM cte WHERE rn ^ 1 If not working please provide the results of select * from #testtable
I think the new code is working, but I am only getting the Facility ID, Violation and rn columns.
you would have to include all column names inside the cte definition and like this:- ;with.....(select [Facility ID], violation, other column names , ....continue .....)... select ....all column names except for rn .....continue....
18 People are following this question.
