Loop through records using case statement

I have a table that I want to loop through each record and apply a case statement for each column to tell if the date in the table is earlier than a matching record in a second table.

Secondary table to see if the matching record is after the date in the first table.  Looking at the dates in the PA_Date column

[Material Number]        GA_Date          PA_Date         ES_Date
110936-B21                1/2/2006          2/1/2006          5/6/2007
110445-B21                12/1/2004        1/1/2005

ee2.txt
FairfieldAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Try this.

As mentioned in your attachment, T1's date column name is 'AU' . If not rename it to your actual field name

Raj
SELECT *,  
	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]

Open in new window

0
 
Brendt HessSenior DBACommented:
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.
0
 
the_billCommented:
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
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
FairfieldAuthor Commented:
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
0
 
FairfieldAuthor Commented:
Adding second table.
T1.txt
0
 
Rajkumar GsSoftware EngineerCommented:
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]

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
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]

Open in new window

0
 
FairfieldAuthor Commented:
Raj,

I am trying to loop through each column/record and do the check, not just one column.  Is this possible?
0
 
Rajkumar GsSoftware EngineerCommented:
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
0
 
FairfieldAuthor Commented:
Will this compare all columns?
0
 
Rajkumar GsSoftware EngineerCommented:
If you mean to compare all columns of T1, just declare variables for each column and use it in query inside loop

Raj
0
 
FairfieldAuthor Commented:
I am using a dynamic pivot to get the columns, so declaring variables I think will be difficult.
0
All Courses

From novice to tech pro — start learning today.