Count mismatch in validation

Question

Count mismatch in validation

saravanatn

SSCarpal Tunnel

Points: 4533
More actions
January 12, 2019 at 2:19 am

#409429

My colleagues was working on count validation between source and target tables. There were many joins conditions and in where condition
where (claim_date<>'01-01-2006' and eff_date<>'31-12-9999') /* is giving 112 records */
when the where (claim_date<>'01-01-2006' and eff_date<>'31-12-9999') is replaced by
where not (claim_date='01-01-2006' and eff_date='31-12-9999') /* is giving 124 records */
My question is both where conditions is same or not? Kindly let me know if you requires any additional details.
Saravanan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 1

No - these are not the same...the first one excludes all claims on 2006-01-01 where the eff_date is not 9999-12-31. The second one includes only those claims where the claim date is 2006-01-01 and the eff_date is 9999-12-31. The second one is actually identifying the claims that are being excluded from the first one...

Note: you really should use the ISO standard date format of YYYYMMDD or YYYY-MM-DD (if claim_date is date data type - if datetime then use YYYYMMDD only). Any other format can be misinterpreted depending on the language for that system.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 2

This is an example where De morgan's law can be applied.
(not A) AND (not B) = not (A OR B)
So the expression
where (claim_date<>'01-01-2006' and eff_date<>'31-12-9999')
can be transformed into
where not (claim_date='01-01-2006' OR eff_date='31-12-9999')

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 3

Jonathan AC Roberts - Saturday, January 12, 2019 11:49 AM
This is an example where De morgan's law can be applied.
(not A) AND (not B) = not (A OR B)
So the expression
where (claim_date<>'01-01-2006' and eff_date<>'31-12-9999')
can be transformed into
where not (claim_date='01-01-2006' OR eff_date='31-12-9999')

Thanks Jeffery and Jonathan. I got both of your points but Jeffery is saying second one includes only those claims where the claim date is 2006-01-01 and the eff_date is 9999-12-31 and Jonathan is saying where not (claim_date='01-01-2006' OR eff_date='31-12-9999'). Is both statement looks contradictory or kindly let me know if my understanding is wrong?

Saravanan

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 4

saravanatn - Sunday, January 13, 2019 1:25 AM
Jonathan AC Roberts - Saturday, January 12, 2019 11:49 AM
This is an example where De morgan's law can be applied.
(not A) AND (not B) = not (A OR B)
So the expression
where (claim_date<>'01-01-2006' and eff_date<>'31-12-9999')
can be transformed into
where not (claim_date='01-01-2006' OR eff_date='31-12-9999')
Thanks Jeffery and Jonathan. I got both of your points but Jeffery is saying second one includes only those claims where the claim date is 2006-01-01 and the eff_date is 9999-12-31 and Jonathan is saying where not (claim_date='01-01-2006' OR eff_date='31-12-9999'). Is both statement looks contradictory or kindly let me know if my understanding is wrong?

They are both logically the same statement. You can verify it with a truth table.

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 5

Jonathan AC Roberts - Sunday, January 13, 2019 2:50 AM
saravanatn - Sunday, January 13, 2019 1:25 AM
Jonathan AC Roberts - Saturday, January 12, 2019 11:49 AM
This is an example where De morgan's law can be applied.
(not A) AND (not B) = not (A OR B)
So the expression
where (claim_date<>'01-01-2006' and eff_date<>'31-12-9999')
can be transformed into
where not (claim_date='01-01-2006' OR eff_date='31-12-9999')
Thanks Jeffery and Jonathan. I got both of your points but Jeffery is saying second one includes only those claims where the claim date is 2006-01-01 and the eff_date is 9999-12-31 and Jonathan is saying where not (claim_date='01-01-2006' OR eff_date='31-12-9999'). Is both statement looks contradictory or kindly let me know if my understanding is wrong?
They are both logically the same statement. You can verify it with a truth table.

Thanks Jonathan. I will check and let you know.

Saravanan