Link to home
Start Free TrialLog in
Avatar of GreatLakes07
GreatLakes07Flag for United States of America

asked on

How to find missed data from a table by comparing two columns?

#Temp table

Yearmody     Presentdata   PreviousData
20130317        236575
20130310        205379         205379
20130303        286207         286207
20130224        249361         249361
20130217        224401         224401
20130210        171052         171052
20130203        null               204978
20130127       199835          199835
20130120       null               190591
20130113       171052           171052


I got the above data from left joining two tables but i want to find in which yearmoday the data was missing by comparing the presentdata column with previousdata column.

Guys can you please help me how can i do this please
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Sorry, not sure I understand clearly. Could you identify the 'expected result' from that sample? Then we can be sure.
Avatar of GreatLakes07

ASKER

I want to find in which column the data is null
example from the aboove temp table
20130203
20130120
If you are only interested in missing PresentData, the query will be:
SELECT Yearmody  from #Temp where PresentData IS NULL

Open in new window

If you are interested in both Present and Previous data, the query will be:
SELECT Yearmody  from #Temp where PresentData IS NULL OR PreviousData IS NULL

Open in new window

If you are interested records for which Present and Previous data are different, the query will be:
SELECT Yearmody  from #Temp where PresentData <> PreviousData

Open in new window

Add a line in your "Where" clause:

Where yourtable.PresentData IS NULL

Open in new window

Hahaha, beaten by 11 seconds!
Please award points to Chaau only, his answer is way more comprehensive than mine.
Awesome buddies you are Brilliants but
if there are nulls i want to print them are also i want to print temp table
How to scan the column whenther nulls are present or not??
What do you mean by "print"?
Sorry not print select * from #Temp table (if null are not there in temp table)
if nulls are there i need to select where null are there in temp table
There, not there....
This will select where NULL is there:
SELECT Yearmody, PresentData, PreviousData  from #Temp where PresentData IS NULL

Open in new window

This will select where NULL is not there:
SELECT Yearmody, PresentData, PreviousData  from #Temp where PresentData IS NOT NULL

Open in new window

This will select all:
SELECT Yearmody, PresentData, PreviousData  from #Temp

Open in new window

Yes all the statements must be in stored Proc edure
can we set all three statements using any IF condidtion if possible???
If I may observe, in your sample data there are 2 entries of NULL, but another row shows something else (actually nothing is shown), could that indicate an "empty string"?

The columns appear to be |date|int|int| is that true? or are any of these (n)varchar?

Yearmody     Presentdata   PreviousData
20130317        236575                                 --<< "empty string"????
..
20130203        null               204978            --<< null is identified
..
20130120       null               190591            --<< null is identified
..
Yes, you can have three of them in a stored procedure. You would have to pass a parameter, called e.g. @Option, that will take three values: 1 - Show only NULLs, 2 - Show NOT NULLs, 3 - Show All. Then you have multiple choice: you can either use IF ... THEN, or you can write everything within a single SQL statement:
SELECT Yearmody, PresentData, PreviousData  
from #Temp 
where @Option = 2 AND PresentData IS NOT NULL
OR @Option = 1 AND PresentData IS NULL
OR @Option = 3

Open in new window

I've requested that this question be closed as follows:

Accepted answer: 0 points for GreatLakes07's comment #a39520050

for the following reason:

impressive and good
ASKER CERTIFIED SOLUTION
Avatar of Koen Van Wielink
Koen Van Wielink
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
good