x

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

more ▼

asked Sep 23, 2015 at 10:42 PM in Default

avatar image

cjvj1098
0 1

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.

Sep 28, 2015 at 06:07 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Sep 24, 2015 at 07:28 AM

avatar image

erlokeshsharma08
2k 3 12 16

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Sep 24, 2015 at 12:30 PM

avatar image

Magnus Ahlkvist
21.8k 20 41 42

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.

Sep 24, 2015 at 02:18 PM cjvj1098

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.

Sep 24, 2015 at 02:29 PM erlokeshsharma08

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.

Sep 25, 2015 at 06:51 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

;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

more ▼

answered Sep 24, 2015 at 02:30 PM

avatar image

erlokeshsharma08
2k 3 12 16

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)

Sep 24, 2015 at 03:04 PM cjvj1098

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)

Sep 24, 2015 at 03:35 PM erlokeshsharma08

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)

Sep 24, 2015 at 07:16 PM cjvj1098

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

Sep 25, 2015 at 12:27 AM erlokeshsharma08

I think the new code is working, but I am only getting the Facility ID, Violation and rn columns.

Sep 25, 2015 at 08:04 PM cjvj1098

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....

Sep 25, 2015 at 08:21 PM erlokeshsharma08
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x49
x21

asked: Sep 23, 2015 at 10:42 PM

Seen: 77 times

Last Updated: Sep 28, 2015 at 06:07 PM

Copyright 2017 Redgate Software. Privacy Policy