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
 How to compare two integer results?

Author  Topic 

dmilam
Posting Yak Master

185 Posts
Posted - 2010-03-22 : 18:00:00
Hello,

What I want to do is compare two integers, if the results are equal in number, then proceed with the rest of the script, otherwise halt.

Something like this, but I don't know the right syntax (SQL Server 2005)

WHILE
(SELECT COUNT(DISTINCT(ID)) FROM #temptable1)
=
(SELECT COUNT(DISTINCT(ID)) FROM #temptable2)
THEN ...

dmilam
Posting Yak Master

185 Posts
Posted - 2010-03-22 : 18:40:35
For performance reasons, setting each expression as variables @n1 and @n2 and then comparing them like so,
WHILE @n1 = @n2
BEGIN ...

doesn't seem very efficient (query runs and runs and runs with no resolution)

Other ideas welcome!
Go to Top of Page

dmilam
Posting Yak Master

185 Posts
Posted - 2010-03-22 : 20:29:04
My comment #2 reflects my ignorance that COUNT is iterative, and so the query goes through one by one to compare, rather than comparing at the sum level, if that makes sense.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2010-03-23 : 03:32:45
I'm confused...what is it you want to achieve here? From what I can understand the syntax in your initial post is valid but it will of course run forever if no updates to either #temptable1 or #temptable2 are done within the loop. Same thing with your second post...you need to have some updates to either @n1, @n2 or both within the loop or it will run forever.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein
Go to Top of Page

dmilam
Posting Yak Master

185 Posts
Posted - 2010-03-23 : 12:44:28
Thanks, Lumbago. I'm not familiar with updating within the loop. How to?

I want the query to continue to run if the number returned from #temptable1 equals the number returned from #temptable2 -- the total number of course (result set) for each.

In other words, again botching the syntax

IF TOTAL FROM #temptable1 = TOTAL FROM #temptable2, PROCEED
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-03-23 : 12:57:12
What do want to do inside the loop?
Maybe we have a solution if we know what you are trying to do.

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-23 : 12:57:17
but they wont change during execution of loop so not sure whats the purpose of this condition at all. As Lumbago told, unless you do some dml operation inside like insert/delete the count wont vary and loop wont terminate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

dmilam
Posting Yak Master

185 Posts
Posted - 2010-03-23 : 13:26:15
I want to the loop to terminate; maybe I'm not understanding the role of COUNT. I want to compare the end result and then proceed, not compare the result of each iteration within the loop. Maybe I could illustrate with a common table expression.

WITH chk(nmbr) AS
(
SELECT COUNT(DISTINCT(ID)) FROM #temptable1

UNION ALL

SELECT COUNT(DISTINCT(ID)) FROM #temptable2
)

SELECT nmbr AS 'Numbers should match' FROM chk

--I'm just learning to use WITH, so I realize that the result here is considered one result set.

Essentially, I want some conditional logic that compares two lump sums, so to speak (I'm not actually using SUM). If the numbers/sums match up, then proceed. If not, then halt.

It might also help to think of it as:

Let a+b = total1
Let c+d = total2

Now, does total1 = total2? If so, then proceed. I'm not interested in a+b or c+d per se, but the results.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-03-23 : 13:31:31
You are still not clear.
Now, does total1 = total2? If so, then proceed
proceed with what?
why and when schouldn't total1 and total2 differ?

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-23 : 13:34:10
you may better off giving us bigger picture. what are significance of those counts? what do they suggest?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

dmilam
Posting Yak Master

185 Posts
Posted - 2010-03-23 : 13:35:10
Proceed with rest of the query, whatever that happens to be.

If total1 does differ from total2, if they are not equal, then don't proceed with the rest of the query, whatever the rest of it is.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-23 : 13:36:32
what does rest of query do? what are contents in two tables? when do their count differ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

dmilam
Posting Yak Master

185 Posts
Posted - 2010-03-23 : 13:48:44
The rest of the query would, for example, proceed to dropping and creating a table, then inserting data into that table, and so on.

The contents of the two tables are: list of distinct customer IDs, drawn from different data sources, which hopefully match. One source may be a temp table run by a user, and so in the T database; the other may be a table in another database (not T).

The counts might differ if the T table were updated after the not-T table.

1. Query is run which inserts data into T table.
2. User then enters data from that T table into another table in another database (not T).
3. User accidentally runs the T table query again, days later, which renews the T table (because dropped and created again, for example).
4. Therefore, the not-T table has old data, and the two tables likely don't match.

Thanks; I hope that scenario helps.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-23 : 13:54:29
quote:
Originally posted by dmilam

The rest of the query would, for example, proceed to dropping and creating a table, then inserting data into that table, and so on.

The contents of the two tables are: list of distinct customer IDs, drawn from different data sources, which hopefully match. One source may be a temp table run by a user, and so in the T database; the other may be a table in another database (not T).

The counts might differ if the T table were updated after the not-T table.

1. Query is run which inserts data into T table.
2. User then enters data from that T table into another table in another database (not T).
3. User accidentally runs the T table query again, days later, which renews the T table (because dropped and created again, for example).
4. Therefore, the not-T table has old data, and the two tables likely don't match.

Thanks; I hope that scenario helps.

ok even in that case whats purpose of loop? wont a conditional statement like if..else suffice?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

dmilam
Posting Yak Master

185 Posts
Posted - 2010-03-23 : 14:03:21
If IF...ELSE suffices, great, I'd like to know! I'm not proficient with conditional logic. It seemed to me that it would be as I stated at the beginning. I thus should have said:

IF
(SELECT COUNT(DISTINCT(ID)) FROM #temptable1)
=
(SELECT COUNT(DISTINCT(ID)) FROM #temptable2)
THEN ...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-23 : 14:12:08
ok now it makes more sense. now cant you do the reqd processing inside?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

dmilam
Posting Yak Master

185 Posts
Posted - 2010-03-23 : 14:40:29
I think I could, if I knew how to write IF...ELSE query blocks properly. Any tips?
Go to Top of Page

rohitkumar
Constraint Violating Yak Guru

472 Posts
Posted - 2010-03-23 : 15:03:58
http://msdn.microsoft.com/en-us/library/ms182717.aspx
Go to Top of Page

dmilam
Posting Yak Master

185 Posts
Posted - 2010-03-23 : 17:58:28
Thanks; I missed how to use variables (having tried them before in a different context, and failed)

Something like this works:

DECLARE @count1 INT, @count2 INT
SET @count1 = (SELECT COUNT(DISTINCT(ID)) FROM #temptable1)
SET @count2 = (SELECT COUNT(DISTINCT(ID)) FROM #temptable2)
IF @count1 = @count2
BEGIN
PRINT 'Yay'
END
ELSE
PRINT 'Bummer'
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site