SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need help with comparing data for verification
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bosstone75
Starting Member

USA
11 Posts

Posted - 03/04/2014 :  10:31:37  Show Profile  Reply with Quote
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


Edited by - bosstone75 on 03/04/2014 10:36:39

nagino
Yak Posting Veteran

Japan
65 Posts

Posted - 03/04/2014 :  20:03:13  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000