Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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

0
tia_kamakshi
Asked:
tia_kamakshi
  • 4
  • 3
  • 2
  • +2
2 Solutions
 
raulggonzalezCommented:
Hi

I think you can do something like this, I couldn't test it, but I'm sure you can get the logic.

Instead of a variable with the max(date) put 2 into a temp table and join it with yours.

Hope it helps.

Cheers
DECLARE totalCount INT;

Declare tranDate  Date;

CREATE TEMPORARY TABLE dates(tranDate Date);

INSERT INTO dates
(SELECT Transactiondate 
   from dailycount 
  where countoftheday <>0 
ORDER BY Transactiondate DESC 
LIMIT 2);


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

from dailycount
INNER JOIN dates
ON dailycount.transactionDate = dates.tranDate;


RETURN totalCount;

END

Open in new window

0
 
raulggonzalezCommented:
Sorry, remove


Declare tranDate  Date;


because is not necessary for my solution

good luck
0
 
Mark WillsTopic AdvisorCommented:
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

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
raulggonzalezCommented:
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
0
 
Mark WillsTopic AdvisorCommented:
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...
0
 
awking00Commented:
See attached for alternate method.
procedure.txt
0
 
GRayLCommented:
How about:

SELECT Sum(EmailTransCount) + Sum(PostTransCount) FROM (
SELECT * FROM DailyCount ORDER BY TransactionDate DESC LIMIT 2)
0
 
Mark WillsTopic AdvisorCommented:
Hmmmm...

I was assuming there could be multiple instances of transactiondate within the DailyCount datasource. So, getting the top 2 rows doesnt necessarily return the top 2 distinct transactiondates. Then I realised there was an error in my previous query where we have to group by (or distinct) in order to ensure we get the top 2 distinct transactiondates.

ie:

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

Open in new window

0
 
GRayLCommented:
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.
0
 
Mark WillsTopic AdvisorCommented:
@grayl, I think that is where the SUM comes into it...

If possible, would be great to see some sample data and expected results
0
 
tia_kamakshiAuthor Commented:
Many Thanks for your replies.

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now