Hi,
I have two table to compare a locate the differences. I want to know which records are in tblExample2 and not in tblExample1 based on the column ProjectNr. And if there are differences, show me the remaining columns relating to detected ProjectNr. Is that possible?
tblExample1
Year BookingNr ProjectNr ProjectName Area Location Finance Price A Price B Plast Pnow Plan 1 Plan 2 Plan 3 Plan 4
01.2011 16 TT.0161110 Acc-Gha E Ost B 2.516,00 2.516,00 2.516,00
01.2011 16 TT.0161110 Acc-Gha E Ost LU 1.199.337,78 931.337,78 78.337,80 55.000,00 150.000,00 134.000,00 206.000,00 150.000,00
01.2011 16 TT.0161110 Acc-Gha E Ost A 513.000,00 513.000,00 blank blank 50.000,00 70.000,00 100.000,00 120.000,00
01.2011 16 TT.0161110 Acc-Gha E Ost LU 5.135.000,00 4.976.000,00 blank blank 250.000,00 750.000,00 1.100.000,00 1.400.000,00
01.2011 16 T.016064 Nrg-Lag E Südost LU blank 735.000,00 blank blank blank blank blank
01.2011 16 T.016064 Nrg-Lag E Südost LU blank 4.161.000,00 blank blank blank blank blank
01.2011 31 PP.031001 Bln-Port E Süd A 2.967,00 2.967,00 2.967,00 blank blank blank blank
01.2011 31 PP.031001 Bln-Port E Süd LA 33.889,68 31.630,18 12.630,20 2.740,50 4.000,00 8.000,00 1.000,00
01.2011 31 PP.031001 Bln-Port E Süd LA 70.000,00 70.000,00 blank blank 35.000,00 35.000,00 blank
01.2011 11 G.011210 Alt-Nyc E Südost LS blank 4.000,00 blank blank blank
01.2011 11 G.011210 Alt-Nyc E Südost LS blank 80.000,00 blank blank blank
01.2011 16 X.016270181506 Fraport-Schil D Ost FE 46.702,71 50.335,23 50.335,20 3.632,52 0
01.2011 16 X.016270181506 Fraport-Schil D Ost V3 430.122,03 430.122,03 430.122,00
01.2011 16 X.016270181506 Fraport-Schil D Ost W 341.374,00 341.374,00 341.374,00
01.2011 16 X.016270181506 Fraport-Schil D Ost FE 173.415,55 173.415,55 169.415,40 0
01.2011 16 X.016270181506 Fraport-Schil D Ost V3 3.969.787,11 3.969.787,11 3.969.787,10
01.2011 16 XX.01681514600 Pot-Ess D Ost V3 175.649,82 175.649,82 175.649,80
01.2011 16 XX.01681514600 Pot-Ess D Ost V4 20.627,25 20.627,25 20.627,30
tblExample 2
Year BookingNr ProjectNr ProjectName Area Location Finance Price A Price B Plast Pnow Plan 1 Plan 2 Plan 3 Plan 4
02.2011 16 TT.0161110 Acc-Gha D Ost B 2.516,00 2.516,00 2.516,00
02.2011 16 TT.0161110 Acc-Gha D Ost LU 1.199.337,78 960.337,78 78.337,80 55.000,00 150.000,00 134.000,00 206.000,00 150.000,00
02.2011 16 TT.0161110 Acc-Gha D Ost A 513.000,00 513.000,00 blank blank 50.000,00 70.000,00 100.000,00 120.000,00
02.2011 16 TT.0161110 Acc-Gha D Ost LU 5.135.000,00 4.976.000,00 blank blank 250.000,00 750.000,00 1.100.000,00 1.400.000,00
02.2011 16 T.016064 Nrg-Lag D Südost LU blank 735.000,00 blank blank blank blank blank
02.2011 16 T.016064 Nrg-Lag D Südost LU blank 4.161.000,00 blank blank blank blank blank
02.2011 31 PP.031001 Bln-Port D Süd A 2.967,00 2.967,00 2.967,00 blank blank blank blank
02.2011 31 PP.031001 Bln-Port D Süd LA 33.889,68 33.630,18 12.630,20 2.740,50 4.000,00 8.000,00 1.000,00
02.2011 31 PP.031001 Bln-Port D Süd LA 70.000,00 70.000,00 blank blank 35.000,00 35.000,00
02.2011 11 G.011210 Alt-Nyc D Südost LS blank 4.000,00 blank blank
02.2011 11 G.011210 Alt-Nyc D Südost LS blank 80.000,00 blank blank
02.2011 16 X.016270181506 Fraport-Schil D Ost AZ blank 57.000,00
02.2011 16 X.016270181506 Fraport-Schil D Ost FE 46.702,71 50.335,23 50.335,20 3.632,52 0
02.2011 16 X.016270181506 Fraport-Schil D Ost V3 430.122,03 430.122,03 430.122,00
02.2011 16 X.016270181506 Fraport-Schil D Ost W 341.374,00 341.374,00 341.374,00
02.2011 16 X.016270181506 Fraport-Schil D Ost AZ 590.000,00
02.2011 16 X.016270181506 Fraport-Schil D Ost FE 173.415,55 173.415,55 169.415,40 0
02.2011 16 XX.01681514600 Pot-Ess D Ost V blank blank blank 0 0
02.2011 16 XX.01681514600 Pot-Ess D Ost V3 175.649,82 175.649,82 175.649,80
02.2011 16 XX.01681514600 Pot-Ess D Ost V4 20.627,25 20.627,25 20.627,30
The first possible answer has the a sp like this:
SET @Dynamictbl = N'SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM ' + @Table_Name + ' WHERE [Area] = ''D''' +
' EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM ' + @Table_Name2 + ' WHERE [Area] = ''D''' +
' ORDER BY [ProjectNr] DESC '
Possible Answer 1
ProjectNr ProjectName BookingNr
TT.0161110 Acc-Gha 16
T.016064 Nrg-Lag 16
PP.031001 Bln-Port 31
G.011210 Alt-Nyc 11
And the answer is based on this statement:
SET @Dynamictbl = N'SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM (
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM ' + @Table_Name + ' WHERE [Area] = ''D''' +
' EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM ' + @Table_Name2 + ' WHERE [Area] = ''D''' +
') AS T ' +
' GROUP BY [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
Possible Answer 2
BookingNr ProjectNr ProjectName Finance Price A
16 TT.0161110 Acc-Gha B 2.516,00
16 TT.0161110 Acc-Gha LU 1.199.337,78
16 TT.0161110 Acc-Gha A 513.000,00
16 TT.0161110 Acc-Gha LU 5.135.000,00
16 T.016064 Nrg-Lag LU blank
16 T.016064 Nrg-Lag LU blank
31 PP.031001 Bln-Port A 2.967,00
31 PP.031001 Bln-Port LA 33.889,68
31 PP.031001 Bln-Port LA 70.000,00
11 G.011210 Alt-Nyc LS blank
11 G.011210 Alt-Nyc LS blank
16 X.016270181506 Fraport-Schil AZ blank
16 XX.01681514600 Pot-Ess V blank
Exported records / solution should look like this:
Possible Answer 3
BookingNr ProjectNr ProjectName Finance Price A
16 TT.0161110 Acc-Gha B 2.516,00
16 TT.0161110 Acc-Gha LU 1.199.337,78
16 TT.0161110 Acc-Gha A 513.000,00
16 TT.0161110 Acc-Gha LU 5.135.000,00
16 T.016064 Nrg-Lag LU blank
16 T.016064 Nrg-Lag LU blank
31 PP.031001 Bln-Port A 2.967,00
31 PP.031001 Bln-Port LA 33.889,68
31 PP.031001 Bln-Port LA 70.000,00
11 G.011210 Alt-Nyc LS blank
11 G.011210 Alt-Nyc LS blank
My Main Question is or the records which am really interested in should look like the first possible answer without the X’s But with the other columns relating to it (like possible answer 3). Is that possible although the finance column to the X's are new in table Example2 .I have updated my main from yesterday