Hi i have this one table and i wanna get all the differenves. I have tried
Excepts, Not in, Exists etc but i'ant getting the right figures like with excel.
ProjectNr Ist Value Soll Value
D.01025 0 19010
D.01048 0 0
D.01620 0 409196
G.01013 20766,31575 20406,21832
G.10002 3409,73749 3707,07765
G.10004 8052,97646 8057,03358
G.01006 1,50104 1,50104
G.00960 585385,8582 543435,6963
G.01961 11000 0
G.01003 2210,34804 2184,59583
G.01004 637,73126 637,38993
G.01005 1557,47348 1424,94381
G.01113 745,29877 745,93531
G.01043 29285,59879 28600,87531
G.01162 23226 0
G.01069 72765,80058 73450,86162
I wanna do something like this:
1.What soll-values are not in Ist value (Soll not in Ist)
2.What Ist values are not in Soll values (Ist not in Soll)
3.What soll values are in Ist (Soll in Ist)
4.What Ist values are in Soll (Ist in Soll)
Experted Solution:
Soll not in Ist Ist not in Soll Soll in Ist Ist in Soll
19.010,00 - - -
- - -
409.196,00 - - -
- 20.406,22 20.766,32
- 3.707,08 3.409,74
- 8.057,03 8.052,98
- 1,50 1,50
- 543.435,70 585.385,86
11.000,00 - -
- 2.184,60 2.210,35
- 637,39 637,73
- 1.424,94 1.557,47
- 745,94 745,30
- 28.600,88 29.285,60
23.226,00 - -
- 73.450,86 72.765,80
1.
SELECT [Soll_value] AS Soll
FROM [dbo].[Table1]
WHERE NOT IN (SELECT [Ist_value] AS Ist FROM [dbo].[Table1]
2.
SELECT [Ist_value] AS Ist
FROM [dbo].[Table1]
WHERE NOT IN (SELECT [Soll_value] AS Ist FROM [dbo].[Table1]
2(alternative).
SELECT [Soll_value] AS Soll
FROM [dbo].[Table1]
EXCEPT
SELECT [Ist value] AS Ist
FROM [dbo].[Table1]
3.
SELECT [Soll_value] AS Soll
FROM [dbo].[Table1]
WHERE EXISTS IN (SELECT [Ist_value] AS Ist FROM [dbo].[Table1]
4.
SELECT [Ist_value] AS Ist
FROM [dbo].[Table1]
WHERE EXISTS IN (SELECT [Soll_value] AS Ist FROM [dbo].[Table1]