Query to Determine Differnces - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query to Determine Differnces

Author

Topic

Otacustes
Starting Member

15 Posts

Posted - 2008-08-29 : 07:18:31

Hello all

I have two tables set out like this:

TableA

Desc Qty
ABC 1
DEF 2
GHI 3
ABC 10

TableB

Desc
RST 1
UVW 2
XYZ 3
ABC 5

I need to know what the difference is between the tables. The qty of ABC in tableA is 11 and in tableB is 5; as tableB is the most upto date I need to know the difference is -6

There is every possibility that tableA and tableB could have duplicate desc entries for which the qty values need to be added and then compared to find the difference from tableB

What is the easiest way to achieve this?

Thanks

Any help or guidance offered is greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 07:35:50

[code]DECLARE @TableA TABLE ([Desc] CHAR(3), Qty INT)

INSERT @TableA
SELECT 'ABC', 1 UNION ALL
SELECT 'DEF', 2 UNION ALL
SELECT 'GHI', 3 UNION ALL
SELECT 'ABC', 10

DECLARE @TableB TABLE ([Desc] CHAR(3), Qty INT)

INSERT @TableB
SELECT 'RST', 1 UNION ALL
SELECT 'UVW', 2 UNION ALL
SELECT 'XYZ', 3 UNION ALL
SELECT 'ABC', 5

SELECT [Desc],
SUM(aQty) AS aQty,
SUM(bQty) AS bQty,
SUM(bQty - aQty) AS [Diff]
FROM (
SELECT [Desc],
SUM(Qty) AS aQty,
0 AS bQty
FROM @TableA
GROUP BY [Desc]

UNION ALL

SELECT [Desc],
0 AS aQty,
SUM(Qty) AS bQty
FROM @TableB
GROUP BY [Desc]
) AS d
GROUP BY [Desc]
ORDER BY [Desc][/code]

E 12°55'05.25"
N 56°04'39.16"

Otacustes
Starting Member

15 Posts

Posted - 2008-08-29 : 08:57:24

Hi Peso

I should have said that I was developing this for Access so I am trying to convert it over

Anyway, I have converted it to this:

SELECT [Desc], Sum(aQty) AS aQty, Sum(bQty) AS bQty, Sum(bQty-aQty) AS Diff
FROM (SELECT[Desc],SUM(Qty) AS aQty,0 AS bQty FROM A GROUP BY[Desc]
            UNION ALL
            SELECT[Desc],0 AS aQty,SUM(Qty) AS bQty FROM B GROUP BY[Desc]) AS d
GROUP BY [Desc]
ORDER BY [Desc];

...but when I run it I get an error saying
"Circular reference caused by alias 'aQty' in query definitions SELECT list"

I suspect the same is true of bQty as well? I suspect I have made a cock-up somewhere but cannot quite put my finger on it

Any help or guidance offered is greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 08:59:59

[code]SELECT [Desc],
Sum(mQty) AS aQty,
Sum(nQty) AS bQty,
Sum(nQty - mQty) AS Diff
FROM (
SELECT [Desc],
SUM(Qty) AS mQty,
0 AS nQty
FROM A
GROUP BY [Desc]

UNION ALL

SELECT [Desc],
0 AS mQty,
SUM(Qty) AS nQty
FROM B
GROUP BY [Desc]
) AS d
GROUP BY [Desc]
ORDER BY [Desc][/code]

E 12°55'05.63"
N 56°04'39.26"

Otacustes
Starting Member

15 Posts

Posted - 2008-08-29 : 09:18:35

Ah-ha!! I see now

Thanks very much for your help, Peso

Otacustes
Starting Member

15 Posts

Posted - 2008-08-29 : 11:46:35

I now need to get two different versions of this so that one shows all of the additions in tableB and the other to show all the deletions with the correct qtys

The code supplied by Peso works great but I also need these two in separate queries.

I have been playing around with this to try and adapt it but cannot seem to get the right combination.

Is it as simple as a where clause?

Thanks

Any help or guidance offered is greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 12:51:19

quote:
Originally posted by Otacustes

I now need to get two different versions of this so that one shows all of the additions in tableB and the other to show all the deletions with the correct qtys

The code supplied by Peso works great but I also need these two in separate queries.

I have been playing around with this to try and adapt it but cannot seem to get the right combination.

Is it as simple as a where clause?

Thanks

Any help or guidance offered is greatly appreciated.

by additions do you meant ones with diff >0 and deletions as diff<0

then what you want is simply an extra HAVING clause

for additions:-

SELECT		[Desc],
		Sum(mQty) AS aQty,
		Sum(nQty) AS bQty,
		Sum(nQty - mQty) AS Diff
FROM		(
			SELECT		[Desc],
					SUM(Qty) AS mQty,
					0 AS nQty
			FROM		A
			GROUP BY	[Desc]

			UNION ALL

			SELECT		[Desc],
					0 AS mQty,
					SUM(Qty) AS nQty
			FROM		B
			GROUP BY	[Desc]
		) AS d
GROUP BY	[Desc]
HAVING Sum(nQty - mQty)>0
ORDER BY	[Desc]

and for deletions:-

SELECT		[Desc],
		Sum(mQty) AS aQty,
		Sum(nQty) AS bQty,
		Sum(nQty - mQty) AS Diff
FROM		(
			SELECT		[Desc],
					SUM(Qty) AS mQty,
					0 AS nQty
			FROM		A
			GROUP BY	[Desc]

			UNION ALL

			SELECT		[Desc],
					0 AS mQty,
					SUM(Qty) AS nQty
			FROM		B
			GROUP BY	[Desc]
		) AS d
GROUP BY	[Desc]
HAVING Sum(nQty - mQty)<0
ORDER BY	[Desc]

Otacustes
Starting Member

15 Posts

Posted - 2008-08-30 : 02:16:43

Oh!!...as simple as that

no wonder I couldn't get it, I was messing around in completely the wrong area.

Thanks for your help all who have posted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources