MAX function

[developer155]

I am having problem with this query, instead of max event date for each customer, it returns max date for all customers on each row. What is wrong?

select
max(el1.feventdate), max(el2.feventdate), max(el3.feventdate)
from tprop p
inner join tcustomer c on p.ftracknum=c.ftracknum and p.fpropnum=p.fpropnum
and c.fcustomerid in (select fcustomerid from tcustomer where ftracknum=132291)
left join teventlog el1 on c.fcustomerid=el1.fcustomerid and el1.feventid='LEBS'
left join teventlog el2 on c.fcustomerid=el2.fcustomerid and el2.feventid='LBOR'
left join teventlog el3 on c.fcustomerid=el3.fcustomerid and el3.feventid='PRRF'

 

[philhege]

dev155,

You need to GROUP your results by customer ID. SQL needs a field value to answer the MAX question by individual customer.

select
c.fcustomerid, max(el1.feventdate), max(el2.feventdate), max(el3.feventdate)
from tprop p
inner join tcustomer c on p.ftracknum=c.ftracknum and p.fpropnum=p.fpropnum
and c.fcustomerid in (select fcustomerid from tcustomer where ftracknum=132291)
left join teventlog el1 on c.fcustomerid=el1.fcustomerid and el1.feventid='LEBS'
left join teventlog el2 on c.fcustomerid=el2.fcustomerid and el2.feventid='LBOR'
left join teventlog el3 on c.fcustomerid=el3.fcustomerid and el3.feventid='PRRF'

GROUP BY fcustomerid

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[developer155]

But when I add a few fileds, like fpropcity, the results are all messed up again.

select C.fcustomerid,
max(el1.feventdate), max(el2.feventdate), max(el3.feventdate) , p.fpropcity
from tprop p
inner join tcustomer c on p.ftracknum=c.ftracknum and p.fpropnum=p.fpropnum --and p.ftracknum=132291
and c.fcustomerid in (select fcustomerid from tcustomer where ftracknum=132291)
left join teventlog el1 on c.fcustomerid=el1.fcustomerid and el1.feventid='LEBS'
left join teventlog el2 on c.fcustomerid=el2.fcustomerid and el2.feventid='LBOR'
left join teventlog el3 on c.fcustomerid=el3.fcustomerid and el3.feventid='PRRF'
group by c.fcustomerid,p.fpropcity

 

[philhege]

When you add a few fields, you have subdivided the aggregate function by city, so every record is the MAX for the customer/city pair. This is by design.



Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[roccorocks]

Try giving an example of the results you expect to see, that might help the community "see" what your your really trying to get. Just a thought.

Tim R

 

[developer155]

thanks a lot guys. I think i figured it out so everything is ok now. thanks again

 

[philhege]

Please post your solution and/or experiences with the problem. If someone else has the same problem, they'll find it here, and you'll have spared them the trouble you went through.

tks,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.