Loop through records using case statement

Insufficiently explained for us to assist.  Can you give a sample of the data in both tables, and what you want the CASE statement to do - that is, are you just looking for Earlier Date in TableA than TableB?  If so, then a simple IF would do.

Is this what you're after?

Assuming you have two table T1 and T2. T1 and T2 are joined by a common field, say T1column and T2Column

select T1.DateColumn, case when T1.DateColumn<T2.DateColumn then 'true' else 'false' end
from T1
inner join T2 on T1.T1column  = T2.T2column

Here is what I am trying to do:

In table T1, I want to use the PA_Date for each [Material Number]

In table T2 I want to compare the matching record from T1 with each column in the matching record and if the PA_Date from T1 is greater than the date from T2 I want it to show a Y, otherwise N.

I am attaching the table T2 in this comment and will post T1 in the next comment.
T2.txt

Adding second table.
T1.txt

Slight modification - not the logic

Removed * from SELECT and showed only Material Number

Raj

SELECT T1.[Material Number],  
	CASE WHEN T1.AU > T2.PA_Date THEN 'Y' ELSE 'N' END RESULT
FROM T1
	LEFT OUTER JOIN T2 ON A.[Material Number] = B.[Material Number]

Select allOpen in new window

Oops! Wrong alias used in join condition

Here is the corrected query.

Is this you mean ?

Raj

SELECT T1.[Material Number],  
	CASE WHEN T1.AU > T2.PA_Date THEN 'Y' ELSE 'N' END RESULT
FROM T1
	LEFT OUTER JOIN T2 ON T1.[Material Number] = T2.[Material Number]

Select allOpen in new window

Raj,

I am trying to loop through each column/record and do the check, not just one column.  Is this possible?

Sure. You can do that using CURSOR.

See this script

-- CURSOR
DECLARE @MATERIAL_NUMBER      VARCHAR(20)
DECLARE @AU      DATETIME, @CHECK BIT

DECLARE CCURSOR CURSOR   FOR
      SELECT [Material Number], AU FROM T1

OPEN CCURSOR
-- FETCH FIRST RECORD
FETCH NEXT FROM CCURSOR INTO @MATERIAL_NUMBER, @AU

WHILE @@FETCH_STATUS=0 -- EXECUTE TILL END OF THE RECORD
BEGIN
      -- DO YOU CHECK HERE
      -- DATA FROM T1 ARE AVAILABLE IN VARIABLES @MATERIAL_NUMBER & @AU



      -- STEP TO NEXT RECORD
      FETCH NEXT FROM CCURSOR INTO @MATERIAL_NUMBER, @AU
END
-- CLOSE & DESTROY CURSOR
CLOSE CCURSOR
DEALLOCATE CCURSOR


Raj

Will this compare all columns?

If you mean to compare all columns of T1, just declare variables for each column and use it in query inside loop

Raj

I am using a dynamic pivot to get the columns, so declaring variables I think will be difficult.