Hi,

You can make use of the MAX function

Below is examples using the demo ski team data.
In the first Report the Max function is not apply so duplicates exist




In the second Report the Max function is applied so no duplicates exist, and last date for each camp is displayed.




Please let me know if this is what you were after

Regards,
Stephen

Hi Stephen,

Thanks for the prompted reply!

This is almost what I need to see, because in case I have different amounts it will sum them and I don't want that. I want to keep the rest of the records as they are (for the last date/transaction). Perhaps I haven't explain enough, sorry for that:


Prod__Date________Amt
A____ 20/01/2014 50
A____ 21/01/2014 60
B____ 19/01/2014 30
B____ 20/01/2014 50
C____ 20/01/2014 10
C____ 22/01/2014 5

Desired Result:

Prod_ Date_______Amt
A____ 21/01/2014 60
B____ 20/01/2014 50
C____ 22/01/2014 5

Any suggestions?

Regards,
Konstantin

Hi Konstantin,

Based on the previous example, I've just added an appended sub-query to include the additional column(s).



I joined CampName = CampName and MAX End Date = End Date, this will only return the values where the date matches the maximum date.
I also removed any aggregation from the appended fields.




This should give you the results you want.

Regards,
Stephen

Thanks Stephen!

That was exactly what I needed. I suspected that it is something with sub-queries, but I was missing the idea of combining MAX of date and sub-queries.

I have two additional problems/questions:

1. My initial though was to create virtual table where I will filter only the data that I need from the last transaction with query like this:

SELECT t1.*
FROM transactions t1
LEFT OUTER JOIN transactions t2
ON (t1.acct_id = t2.acct_id AND t1.trans_date < t2.trans_date)
WHERE t2.acct_id IS NULL;

but haven't done it yet. Not sure if it will work. Is this also possible and from performance point of view, which will be better (faster)?

2. I am not able to make MAX to my timestamp field, because it is defined as varchar dimension and I have no idea why does it define it like that and how to convert it. I read a lot, but couldn't find solution. In my SQL DB is formatted e.g.: "11/09/2013 19:38:00", but it comes as varchar dimension. Any suggestions?


Thanks once again for the support!

Hi Konstantin,


If you opt to go the route of creating a Virtual Table , your SQL statement should looks similar to the statement below
[code]
SELECT t1.acct_id, MAX(t1.trans_date), t2.amt
FROM transactions t1
LEFT OUTER JOIN transactions t2
ON (t1.acct_id = t2.acct_id AND MAX(t1.trans_date) = t2.trans_date)
GROUP BY t1.acct_id, t1.trans_date
[/code]
The only way to know which statement is better (faster), would be to run the statement against your database and check the execution times.

If your date field is saved as a VARCHAR in your database you can convert it to a date field using a Freehand SQL field and applying the TO_DATE or TO_TIMESTAMP function.
[code]TO_DATE( trans_date, 'DD/MM/YYYY')[/code]
or
[code]TO_TIMESTAMP ( trans_date, 'DD/MM/YYYY HH:MI:SS' )[/code]
(NB! The SQL might be different depending on the Database you're connecting to)

Hope that answers your questions/problems

Regards,
Stephen