query/procedure help - 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 2012 Forums

Transact-SQL (2012)

query/procedure help

Author

Topic

bigjay2100
Starting Member

4 Posts

Posted - 2014-09-29 : 18:30:29

OK, so here is my problem. I have two tables that I can tie together and get the info I need but the problem is that one of the tables has unique data for each record in a row and not multiple rows. The data is expiration dates and while 4 exist per record, if even one is not expired, the record is good. I need to query on the two table results and get only those results where all 4 express dates are truly expired or null. How can I do that with a query or procedure to get me the records?

The reason for the query is I need to put together a procedure one I have the info to change the status of every record that has all 4 expire dates as expired or null but leave any record that has even one good date alone.

My results right now look like:

ID Name expire1 expire2 expire3 expire4

Thoughts?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-29 : 19:38:33

Please show us some sample data and expected output to make your post clear.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bigjay2100
Starting Member

4 Posts

Posted - 2014-09-29 : 22:45:52

Ok, sorry about any missing information as i was typing the original post through my phone. So here is what I have.

Table 1 is just a simple contact table. One line for each person who resides in the database.
Table 2 is a test score table. One row for each contact I have in the database and all testing data resides in that one record for that one individual.

There are 4 tests and each have their own score and expiration date of validity of the test. So the query is very simple for the code:

select Contact.ID, Contact.FULL_NAME, Education.ExamDate1, Education.ExamDate2, Education.ExamDate3, Education.ExamDate4
from Contact inner join Education on Contact.id = Education.id

When I run that query, I get this for my results:

ID FULL_NAME ExamDate1 ExamDate2 ExamDate3 ExamDate4
100001 John Smith 2011-01-30
100002 Jane Doe 2013-05-24
100003 Jon Doe 2013-05-24
100004 Test User1 2013-05-24
100005 Test User2 2012-08-09

This query actually provides about 58k records. What I need to do is look at each record and determine if any of the exam dates are expired based upon the companies policy. For each individual, if all exams are either empty or expired, the record gets flagged for deletion but in the same manor, since the records are combined to each individual, if even one of the exam dates is not expired, the entire record needs to be kept. My problem is I have been trying to figure out how to run this to get me a list of all records that are to be flagged to be deleted. Once I get a query to give me that, I can write a procedure to flag them appropriately.

Now, I tried to do this using a case statement earlier in a procedure where I set a dummy variable to blank and ran through a case statement for each record for each date and built a small string to the declared variable but I couldn't get it to run, kept giving me a bunch of errors so I gave up on this.

Any ideas on what my best course of action is to be able to cycle through each record and validate the ones to be kept and the ones to be removed?

Thanks for any and all help and I hope this is enough to go by and if not, please let me know what else is needed.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-30 : 00:42:12

So, the exam dates in each row represents expiry date or exam taken date? What is the criteria for calculating expiration date?

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

bigjay2100
Starting Member

4 Posts

Posted - 2014-09-30 : 07:48:43

They are the e actual expires date of the exam.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-30 : 08:18:26

Maybe something like this:


Select c.id, c.FULL_NAME, e.ExamDate1, e.ExamDate2, e.ExamDate3, e.ExamDate4
from contact c join 
(select id from education
where ExamDate1 is not null and 
ExamDate2 is not null and 
ExamDate3 is not null and 
ExamDate4 is not null
union all
select id from education
where ExamDate1 is null and
ExamDate2 is null and 
ExamDate3 is null and 
ExamDate4 is null
) t
on c.id = t.id
join education e on c.id = e.id

This should give you list of records which are to be flagged.

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

bigjay2100
Starting Member

4 Posts

Posted - 2014-09-30 : 08:55:28

Thank you for the code but looking at it, it looks like the code will ignore any record with a null in it. While that would normally be fine, I am working with records that most likely will have a null as we have no records where a person has taken all 4 exams so any that they haven't taken will have a null value to them.

I need to be able to look at the results for each column for each record and flag the column results as null/expired - bad or any one of the 4 record to be not null - good and if I get 4 bad results for the record, the record is then a bad record where I can flag it to be deleted. If any of the 4 results are good, then the entire record is good no matter what the other results say.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-01 : 01:13:43

If you look carefully, there is a UNION ALL between two queries. So, I am combining records where either all exam dates are null(empty) or all exam dates populated(expired). As per your description, you are trying to find all such records where all exams are expired or empty. Am I missing something?

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources