different values for similar queries

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 2005 Forums

Transact-SQL (2005)

different values for similar queries

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-28 : 11:58:11

hi, can someone explain to me why these 2 queries produce different counts ?

select distinct loan.loanid,
person.personid, depositdate, datedue,
datediff(dd, depositdate, datedue) as datedifference,
issettled, isdefault, isblacklist
from loan
inner join person on person.personid=loan.personid
inner join payment on loan.loanid=payment.loanid
where depositdate is not null
AND isblacklist=1
AND loan.loanid IN (select max(loan.loanid) from loan group by personid)
AND payment.paymentid IN(Select Max(paymentid) from payment group by loanid)
AND issettled=1
ORDER BY isdefault, issettled

SELECT DISTINCT
person.personId, MAX(loan.loanId) AS loanid, MAX(payment.paymentId) AS paymentid, MAX(loan.depositDate) AS depositdate, MAX(payment.dateDue)
AS datedue, DATEDIFF(dd, MAX(loan.depositDate), MAX(payment.dateDue)) AS datedifference,
max(convert(int,issettled)), max(convert(int,isdefault)),max(convert(int,isblacklist))
FROM loan INNER JOIN
person ON person.personId = loan.personId INNER JOIN
payment ON loan.loanId = payment.loanId
WHERE (loan.depositDate IS NOT NULL) AND (person.isBlacklist = 1) AND (loan.isSettled = 1)
GROUP BY person.personId

shouldn't they basically be the same ?
the first query produces alot less..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-28 : 12:06:30

The queries look completely different to me.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-28 : 12:09:42

hi Tara,
IF I say : AND loan.loanid IN (select max(loan.loanid) from loan group by personid)
isn't that saying the biggest loanid for each personid ?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 12:12:16

the first returns records only when the last loan is a settled loan

where AND loan.loanid IN (select max(loan.loanid) from loan group by personid)
where AND issettled=1

the second returns the last loan record of all the settled loans

select MAX(loan.loanId) AS loanid,
where AND (loan.isSettled = 1)

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-28 : 12:15:07

cheers anon !

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-11-28 : 12:20:13

query 1 should be :
AND loan.loanid IN (select max(loan.loanid) from loan where issettled=1 group by personid)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources