List of duplicate id - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

List of duplicate id

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2008-09-30 : 07:31:43

Hello,

I want to get a list of duplicate id repeated for each tax year. For example: taxpayer id: 111222333 appeared twice (one for taxyear 2000, and one for taxyear2001). I used function having count(*)>1, but I still see the single record there...Below is my query, if anyone can help. thanks a bunch!!!

SELECT A.ID, A.TAXYEAR , A.ITEM_DATE_ACTION, BB.ACTIVITY_DTTM,
sum(OP_ITM_BALANCE) as Assessment_Amount, a.item_assmnt_no
FROM DBA.ANOUTPUT A
LEFT JOIN (SELECT B.OWNER_ID, B.TAX_YEAR,
MIN(C.ACTIVITY_DTTM) AS ACTIVITY_DTTM
FROM DBA.TCASINF B
INNER JOIN DBA.TSTAHIS C ON B.CASE_ID=C.CASE_ID
WHERE B.STATUS_ID <>10
GROUP BY B.OWNER_ID, B.TAX_YEAR
)BB ON BB.OWNER_ID=A.ID AND BB.TAX_YEAR=A.TAXYEAR
WHERE A.ITEM_STATUS='AAA'
AND A.ITEM_DATE_ACTION >bb.activity_dttm
and bb.owner_id in(select owner_id
from dba.tcasinf
where status_id not in (1, 2, 3, 4, 5, 6, 7, 8, 10) group by owner_id
having count(*)>1)

group by a.id, a.taxyear, a.item_date_action, bb.activity_dttm, a.item_assmnt_no, a.item_status
order by a.id

And here is some output example for clarify my saying. The bold data that should not appear on the list...but it does..so how can we just get only repeated id (like the last 2 lines)
001327011 2001
001347863 2003
001386206 2001
001387967 2001
001405497 2001
001484482 2001
001546415 2003
001546415 2001
001546415 2002

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 07:37:44

Can you post your table structures?

ntn104
Posting Yak Master

175 Posts

Posted - 2008-09-30 : 07:44:54

quote:
Originally posted by visakh16

Can you post your table structures?

Do you mean the output table?
- id (anoutput.id=tcasinf.owner_id)
- taxyear (anoutput.taxyear=tcasinf.tax_year)
- item action date
- activity date
- assmessment no.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 07:56:24

Something similar to this?

SELECT		t.*
FROM		Table1 AS t
LEFT JOIN	(
			SELECT		ID,
					MIN(TaxYear) AS y
			FROM		Table1

			UNION ALL

			SELECT		ID,
					MAX(TaxYear)
			FROM		Table1
		) AS d ON d.ID = t.ID
			AND d.y <> t.TaxYear

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 07:58:33

quote:
Originally posted by ntn104

quote:
Originally posted by visakh16

Can you post your table structures?

Do you mean the output table?
- id (anoutput.id=tcasinf.owner_id)
- taxyear (anoutput.taxyear=tcasinf.tax_year)
- item action date
- activity date
- assmessment no.

why you're grouping by all other fields in main select? does anoutput table have duplicate entries?

ntn104
Posting Yak Master

175 Posts

Posted - 2008-09-30 : 08:07:42

quote:
Originally posted by visakh16

quote:
Originally posted by ntn104

quote:
Originally posted by visakh16

Can you post your table structures?

Do you mean the output table?
- id (anoutput.id=tcasinf.owner_id)
- taxyear (anoutput.taxyear=tcasinf.tax_year)
- item action date
- activity date
- assmessment no.

why you're grouping by all other fields in main select? does anoutput table have duplicate entries?

Thank you all. I figured out what need to be done. I will need to eliminate some status id code... I added new where statement in the subquery (see red line above)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources