Link to home
Start Free TrialLog in
Avatar of tia_kamakshi
tia_kamakshiFlag for United Arab Emirates

asked on

Getting last max 2 dates in the table


I am writting a function in mysql.

I need to have sum of transactions from last 2 dates entered in the system

sum(EmailTransCount)+ sum(posttranscount)


My below function is considering last max date but I wanted to consider last max 2 dates in my below query

Please help me fixing my query.
BEGIN

DECLARE totalCount INT;

Declare tranDate  Date;

select max(Transactiondate) into tranDate from dailycount where countoftheday <>0;


select (sum(EmailTransCount)+ sum(posttranscount))  INTO totalCount

from dailycount

where countoftheday <>0

and transactiondate = tranDate;

RETURN totalCount;

END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of raulggonzalez
raulggonzalez

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of raulggonzalez
raulggonzalez

Sorry, remove


Declare tranDate  Date;


because is not necessary for my solution

good luck
Avatar of Mark Wills
Yep, kinda have to agree...

but would change it slightly...

select (sum(EmailTransCount)+ sum(posttranscount))  INTO totalCount
from dailycount
where countoftheday <>0
and transactiondate IN (select Transactiondate from dailycount where countoftheday <>0 order by transactiondate desc limit 2)

Open in new window

Hi mark_wills,

I'm not sure about repeating

where countoftheday <>0

If we retrieve the dates in my temp table or your subquery, would it be necessary doing it again in the main query?
 I've seen the original code has it, but I've got my doubts.

Cheers
Yep reckon it is... But like you I did have my doubts.

We dont know what may be included or excluded as a result of countoftheday, or, the dependancy on that condition.

Certainly if the goal is simply the two most recent dates (regardless) then it is not needed in the subquery (as it currently is).

But, if we take our clue from the original max(Transactiondate) then it was part of that condition, so, simply carried it through...
See attached for alternate method.
procedure.txt
How about:

SELECT Sum(EmailTransCount) + Sum(PostTransCount) FROM (
SELECT * FROM DailyCount ORDER BY TransactionDate DESC LIMIT 2)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Do we need the top two 'different' dates?  What do we do when there are five (to choose a number) records having the same date which are max() dates.
@grayl, I think that is where the SUM comes into it...

If possible, would be great to see some sample data and expected results
Avatar of tia_kamakshi

ASKER

Many Thanks for your replies.

I am sorry for late response. I was out of station last week.