tia_kamakshi

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
```

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Yep, kinda have to agree...

but would change it slightly...

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)
```

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

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...

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

procedure.txt

How about:

SELECT Sum(EmailTransCount) + Sum(PostTransCount) FROM (

SELECT * FROM DailyCount ORDER BY TransactionDate DESC LIMIT 2)

SELECT Sum(EmailTransCount) + Sum(PostTransCount) FROM (

SELECT * FROM DailyCount ORDER BY TransactionDate DESC LIMIT 2)

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

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

ASKER

Many Thanks for your replies.

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

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

Declare tranDate Date;

because is not necessary for my solution

good luck