Elimate incorrect records - 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)

Elimate incorrect records

Author

Topic

ggardei
Starting Member

6 Posts

Posted - 2010-04-05 : 14:00:46

Greetings all.

I have the table that contains attendance status for students in our elementary grades. Due to an error in my coding the attendance entry page runs the add record script instead of an update script; resulting in multiple attendance entries for the date.

ACA_ElmAttendanceData
StudentID
Status
Date
Comment

1234, Late, 01/01/2010
1234, Late, 01/02/2010
1234, Present, 01/03/2010
1234, Left Early, 01/03/2010

I need to generate a report showing the dates that a student has multiple entries for the same day.

Thanks

George

George W. Gardei
God's Bible School & College
(513) 763-6516
1810 Young Street
Cincinnati, OH. 45202

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-05 : 14:25:48

[code]
-- List a count of records for each StudentID/Date, Where the count > 1.
SELECT StudentID, Date, COUNT(*) AS RecordCount
FROM ACA_ElmAttendanceData
GROUP BY StudentID, Date
HAVING COUNT(*) > 1

-- List all records counted in the above query
SELECT a.StudentID, a.Status, a.Date, a.Comment
FROM ACA_ElmAttendanceData a
INNER JOIN (
SELECT StudentID, Date
FROM ACA_ElmAttendanceData
GROUP BY StudentID, Date
HAVING COUNT(*) > 1 ) e
ON a.StudentID = e.StudentID
AND a.Date = e.Date

There are 10 types of people in the world, those that understand binary, and those that don't.[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 23:59:22

[code]SELECT StudentID,Status,Date,Comment
FROM
(
SELECT StudentID,Status,Date,Comment,COUNT(StudentID) OVER (PARTITION BY Date) AS Cnt
FROM Table
)t
WHERE Cnt > 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources