How to get all differences

Question

0.00/5 (No votes)

See more:

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

SQL

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]

Posted 12-Jan-15 0:34am

mikybrain1

Updated 13-Jan-15 20:56pm

v5

Add a Solution

Comments

dan!sh 12-Jan-15 6:36am

Can you update your question with SQL query/procedure you are using to get this data?

mikybrain1 12-Jan-15 9:12am

HiI've updated my question :)

Zoltán Zörgő 12-Jan-15 6:42am

I don't think you should do this on database/t-sql level. Such reports are tasks to perform on upper levels.

ZurdoDev 12-Jan-15 7:51am

Where are you stuck?

SELECT * FROM table WHERE field1 NOT IN (SELECT field2 FROM table)

BillWoodruff 23-Jan-15 7:22am

If you use Linq this is easy.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Praveen Kumar Upadhyay · Answer 1 · 2015-01-12T03:22:00

Solution 1

Hope below queries will help you.

SQL

[1]
select t1.soll_value
from table1 t1 left outer join table1 t2
on t1.soll_id = t2.lst_value
where t2.lst_value is null; 

[2]
select t1.lst_value
from table1 t1 left outer join table1 t2
on t1.lst_value = t2.soll_value
where t2.soll_value is null; 

[3]
select t1.soll_value
from table1 t1 left outer join table1 t2
on t1.soll_value = t2.lst_value
where t2.lst_value is not null; 

[4]
select t1.lst_value
from table1 t1 left outer join table1 t2
on t1.lst_value = t2.soll_value
where t2.soll_value is not null;

Posted 12-Jan-15 3:22am

Praveen Kumar Upadhyay

Comments

mikybrain1 13-Jan-15 3:24am

HiI just have one table not two tables. Is it possible?

Praveen Kumar Upadhyay 13-Jan-15 3:36am

I have not used two tables. Same table is been used as a self join. Try running the query and see if you get the output.

mikybrain1 13-Jan-15 4:23am

Thnx. I' m now tryin to count the Projectnr and also sum up the Ist or Soll value if the ProjectNr repeats.
My statement is counting but without an aggregation: Example if a Projectnr occurs 3 times and the Ist or Soll value also . it shd be like

D.0126438743 3 45500,00

Here my statement but it counting 1 by 1
D.0126438743 1 12000,00
D.0126438743 1 10000,00
D.0126438743 1 21500,00

SELECT t1.[Projectnr], count(t1.[Projectnr]) AS TotalOfProj, sum(t1.[Soll_value]) as Soll

FROM [dbo].[table1] t1
LEFT OUTER JOIN [dbo].[table1] t2
ON
t1.[Soll_value] = t2.[Ist_value]
WHERE t2.[Ist_value] IS NULL
GROUP BY t1.[Projectnr], t1.[Soll_value]
ORDER BY t1.[Projectnr] ASC

Praveen Kumar Upadhyay 13-Jan-15 4:44am

Accept this solution if it worked. Ask this question in another question.

Praveen Kumar Upadhyay 14-Jan-15 2:56am

The solution didn't work for you?

mikybrain1 14-Jan-15 3:02am

unfortunately no
Your statement suggestion do repeats it self and i' m not getting the results like in my experted solution. Can u please take a look again?