September 15, 2010 at 11:43 pm
Hi Experts,
I have a two table Table 1: Table 2_log,
Table 1:(THis is Master Table will have 1 million record)
Column names are :C1 C2 c3 C4 c5 c6
Values are :01 sssss 94 43 34 2010-09-16
Table 2_log:(THis audit table will have 2 million record)
Column names are :C1 C2 c3 C4 c5 c6
Values are :01 ss 34 43 34 2010-01-01
:01 sss 34 43 34 2010-02-01
:01 ssss 35 43 34 2010-03-01
Here I want to generate the Report which columns are updated :
c1 Field_name before_Value current_values updated_on
01 c2 ss sssss 2010-01-01,
01 c2 sss sssss 2010-02-01
01 c2 ssss sssss 2010-03-01
01 c3 35 94 2010-03-01
thanks in advance ,
September 15, 2010 at 11:50 pm
Saravanan, we have told a gazillion times to post the tables structures, sample data et all (i myself have told u and pointed u to the article by Jeff Moden, 4 or 5 times), but still u continue to ask vague question 🙁
Go through this following article and helping us help you..
September 16, 2010 at 12:32 am
Try something like this:
SELECT
c1,
CASE
WHEN T1.C2 <> T2.C2 THEN 'C2'
WHEN T1.C3 <> T2.C3 THEN 'C3'
WHEN T1.C4 <> T2.C4 THEN 'C4'
WHEN T1.C5 <> T2.C5 THEN 'C5'
End Field_Name,
CASE
WHEN T1.C2 <> T2.C2 THEN T1.C2
WHEN T1.C3 <> T2.C3 THEN T1.C3
WHEN T1.C4 <> T2.C4 THEN T1.C4
WHEN T1.C5 <> T2.C5 THEN T1.C5
End before_value,
CASE
WHEN T1.C2 <> T2.C2 THEN T2.C2
WHEN T1.C3 <> T2.C3 THEN T2.C3
WHEN T1.C4 <> T2.C4 THEN T2.C4
WHEN T1.C5 <> T2.C5 THEN T2.C5
End current_value,
CASE
WHEN T1.C2 <> T2.C2 THEN T2.C6
WHEN T1.C3 <> T2.C3 THEN T2.C6
WHEN T1.C4 <> T2.C4 THEN T2.C6
WHEN T1.C5 <> T2.C5 THEN T2.C6
End updated_on
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.c1=T1.c1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply