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

MySQL ServerSQL

Avatar of undefined
Last Comment
tia_kamakshi

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
raulggonzalez

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
raulggonzalez

Sorry, remove


Declare tranDate  Date;


because is not necessary for my solution

good luck
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

raulggonzalez

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Mark Wills

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

See attached for alternate method.
procedure.txt
GRayL

How about:

SELECT Sum(EmailTransCount) + Sum(PostTransCount) FROM (
SELECT * FROM DailyCount ORDER BY TransactionDate DESC LIMIT 2)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
GRayL

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

@grayl, I think that is where the SUM comes into it...

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

Many Thanks for your replies.

I am sorry for late response. I was out of station last week.
Your help has saved me hundreds of hours of internet surfing.
fblack61