Why result set is different - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Why result set is different

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-01-04 : 07:31:49

I am trying to ascertain the number of customers that connected to a mobile network during the months of Nov or Dec 2003. However, I do not want to include those that have disconnnected before a year is up or if they did disconnect, the terminate date is either a year or more later than the connection date.

Here is the query:
select count(distinct AccountNo), count(distinct MobileNo)
from J36 J
inner join CycleInstance C on C.CycleInstanceID=J.CycleInstanceID
inner join Cycle on Cycle.CycleID = C.CycleID
inner join ControllerCodes CC
on CC.ControllerID = Cycle.ControllerCodeID
where datepart(mm,N000_Connection_date) in (11,12)
and datepart(yy,N000_Connection_date)='2003'
and (N000_Terminate_date is null or datediff(yy, N000_Connection_date, N000_Terminate_date) >= 1)
go

The result set is:
AccountNo MobileNo
----------- -----------
27732 27578

If I run the above query replacing the text in red with:
and N000_Terminate_date is null

I get the result set:
AccountNo MobileNo
----------- -----------
27362 27232

If I run the above query replacing the text in red with:
and datediff(yy, N000_Connection_date, N000_Terminate_date) >= 1

I get the result set:
AccountNo MobileNo
----------- -----------
1706 1710

What I do not understand is why the result of the latter two queries do not return the result of the first query? See below:

First query
AccountNo MobileNo
----------- -----------
27732 27578

Combined 2nd and 3rd query
AccountNo MobileNo
----------- -----------
28068 28942

The conditions are the same (where Terminate date is null or is a year greater than the connection date), but have been combined to a single result set, yet the result does not tally when the query conditions are performed separately?

Can someone please explain to me why this happens?

Hearty head pats

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-04 : 07:47:58

count(distinct AccountNo), count(distinct MobileNo)

you have the same AccountNo in both result sets. These are only counted once in the combined.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-01-04 : 07:58:43

Hello

Thanks for replying. Can you elaborate more please as I don't understand what you mean? (probably the result of New year excess)

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-01-04 : 09:47:21

I know that the result set must be including the same AccountNo, but I don't understand why as the conditions are different.

For example, the query asking to return the number of AccountNo's has to meet the condition 'terminate date is null'. Likewise for the other condition 'It has a terminate date a year or more greater than the connection date'. An AccountNo cannot meet both conditions, as it is either disconnected or still active.

So what are the possible explanations as to why would it be included twice? (eg. bad data in the first place)

Thanks in advance

Hearty head pats

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-04 : 13:18:01

Create the two resultsets then look for common values in them.

Are account numbers unique? if not then the same value can be in both resultsets, if not why are you using count distinct?
similarly for mobile nos.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-01-05 : 11:07:05

Account number is not unique, and neither is the mobile number. Are you suggesting that perhaps that if so, then one account number may have a termination date as well as remain connected?

This can be possible (although not supposed to be), as there can be multiple handsets to one account number (again, not supposed to be). In theory, one handset could have been disconnected whilst others are still active. Thanks for your advice, it is much appreciated.

In conclusion, I presume that the more reliable result set is the one that includes both conditions. I think I need to investigate the data a bit more, and like you say, compare the results. It does make life difficult when you discover supposed business rules not to be entirely true!!!

Hearty head pats

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-05 : 11:25:36

>> It does make life difficult when you discover supposed business rules not to be entirely true!!!
That's why you shouldn't believe anything unless it is enforced in the structure.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-01-05 : 12:27:54

Something I am beginning to learn more and more!!!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources