GreatLakes07
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
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
Sorry, not sure I understand clearly. Could you identify the 'expected result' from that sample? Then we can be sure.
ASKER
I want to find in which column the data is null
example from the aboove temp table
20130203
20130120
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
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
If you are interested records for which Present and Previous data are different, the query will be:SELECT Yearmody from #Temp where PresentData <> PreviousData
Add a line in your "Where" clause:
Where yourtable.PresentData IS NULL
Hahaha, beaten by 11 seconds!
Please award points to Chaau only, his answer is way more comprehensive than mine.
Please award points to Chaau only, his answer is way more comprehensive than mine.
ASKER
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??
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"?
ASKER
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
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:
This will select where NULL is there:
SELECT Yearmody, PresentData, PreviousData from #Temp where PresentData IS NULL
This will select where NULL is not there:SELECT Yearmody, PresentData, PreviousData from #Temp where PresentData IS NOT NULL
This will select all:SELECT Yearmody, PresentData, PreviousData from #Temp
ASKER
Yes all the statements must be in stored Proc edure
can we set all three statements using any IF condidtion if possible???
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
..
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
ASKER
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
Accepted answer: 0 points for GreatLakes07's comment #a39520050
for the following reason:
impressive and good
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
good