Returning only oldest item from a list 1

[DrSmyth]

Hi, i've been racking my brain over this for a couple of days and can't see to find a definitive solution.

Basically, my database contains details relating to customer contact, i'm reporting on letters that have been sent to customers and the date they were sent. Most letters have only been sent once, but some have been sent 2, 3 or 4 times. I'm interested in only returning details relating to the first letter sent. The cases that only have i letter are not a problem, the problem lies with the multiple letters.

I thought i could get around it by using a min measure on the date of the letter, but this hasn't worked. Then i tried combining a count in (to find the instances where there are more than 1 letter) combined with an if statement (if count (letter date) in (customer) but alas, still no joy.

Anybody have a suggestion for a function to look into or alternatively a solution... As always any help will be greatly appreciated.

Andy

 

[BrianTyler]

You could use a complex join between the customer table and the letter table (in Designer):

letter.custno = customer.custno and
letter.letterdate = (select min(x.letterdate from letter x where x.custno = customer.custno)

To allow other queries to run as before, you may need to generate alias tables for this join.

 

[DrSmyth]

That's a good idea Brian, I've used complex joins for other stuff before but never really considered it for this. I'll give it a go and let you know how i get on.

Andy

 

[Sridharan]

What Brian outlined should work good for you??? Its basically a Correlated Subquery done at the Universe Level and use that Condition in the Report... One another way to do is at the Report Level... While making the condition letterdate Equal To choose Calculation Wizard which will start a Wizard kinda stuff that should walk you thru the steps... Thats one way of creating the required query... So you can choose between those two... But in one way doing it at the Universe Level is good for some users who are not comfortable with the Calculation Wizard...

Sri

 

[DrSmyth]

Cheers lads, got your methid to work Brian, cheers. Also took a look at the calculation wizard, not used it before and got a little confused, does anybody know where i can find a good guide?

 

[DrSmyth]

Just completed my first successful calculation wizard report..... What a fantastic little tool, this opens up all sorts of possibilities.

Still looking for some decent information on it though, the BO helpguide on it is only a couple of pages long and not very good, think i'll start another thread...

 

[Sridharan]

Yes it is a good option... I don't think there is some separate Doc kinda stuff for Calculation Wizard... But BO Doc should be good starting point and also one shud be little conversant with Correlated Subquery to understand the way it creates the query... Having said that once you spend some time with it you should be comfortable with all the values... Now if you are unsure or confused about some screens drop a note and maybe I can help you with it...

Sri