Need help with comparing data for verification

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)

Need help with comparing data for verification

Author

Topic

bosstone75
Starting Member

11 Posts

Posted - 2014-03-04 : 10:31:37

We run a database that keeps track of student's services (activity) in our school district. When a student receives services from 2 people at the same time, same date, the first person is expected to enter minutes for the student. The second person is expected to enter zero for the minutes. Sometimes, the first person forgets to enter minutes and so the only record we have is the second person's record with 0 minutes. We want to find all those instances so we can have that corrected by the staff.

Here's a simple example of the data.

This is what we'd expect to be OK
StuActID___StudentID___StudentName___ActivityDesc____ActivityDate____MinutesMiles___StaffName
5___________869823_______John_________Personal Care__3/1/2013 0:00______60___________Cindy Loo
8___________869823_______John_________Personal Care__3/1/2013 0:00______0____________Larry Lee
10__________993321_______Gary_________Personal Care__3/4/2013 0:00______30___________Bobby Joe

This would NOT be OK
StuActID___StudentID___StudentName___ActivityDesc____ActivityDate____MinutesMiles___StaffName
12__________843223_______Bobby_______Personal Care____3/1/2013 0:00______0__________Dougie Fresh
17__________832113_______John________Personal Care____3/1/2013 0:00______0__________Happy Gilmore

We'd want the query to return all of the "NOT OK" records because they have zero minutes and don't have a matching activity with minutes on the same date. One thing working in our favor is the ActivityDate field does NOT have time stamps. The staff member types in the date so it ends up just the date stamp with zeros for the hours and minutes, so it should be easier to compare the dates.

Thanks,
Bill

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-03-04 : 20:03:13

get alone zero data.

SELECT *
FROM TableOfStuAct BASE
WHERE NOT EXISTS(
	SELECT *
	FROM TableOfStuAct FILTER
	WHERE FILTER.StudentID = BASE.StudentID
	AND  FILTER.ActibityDate = BASE.ActibityDate
	AND FILTER.StuActID != BASE.StuActID
	AND FILTER.MinutesMiles != BASE.MinutesMiles)
AND BASE.MinutesMiles = 0

or Other way, get having no pair data.

SELECT *
FROM TableOfStuAct BASE
WHERE NOT EXISTS(
	SELECT *
	FROM TableOfStuAct FILTER
	WHERE FILTER.StudentID = BASE.StudentID
	AND  FILTER.ActibityDate = BASE.ActibityDate
	AND FILTER.StuActID != BASE.StuActID
	AND FILTER.MinutesMiles != BASE.MinutesMiles
	--If application ensure following condition, remove
	AND 0 IN (FILTER.MinutesMiles, BASE.MinutesMiles)
	)
--Exclude todays progress data
AND NOT (
	BASE.ActibityDate = CONVERT(DATE, GETDATE())
	AND BASE.MinutesMiles != 0
	)

-------------------------------------
From Japan
Sorry, my English ability is limited.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources