CODEWITHSUNDEEP
mysql
dodo
dodo

Reputation: 67

Looking for direction to Build MySQL date query

reference Date: 20.10.2015

step 1: need the max date from PayTO table > max(PayTO)

step 2: check if this max(PayTO) < reference Date (20.10.2015)

MenTBL
======
TZ   |   Fname
===========
11    |  ZZZ
22    |  XXX
33    |  CCC
44    |  VVV


PayTbl
======
TZ   |  PayTO
===========
11    |  01/01/2015
11    |  03/05/2015
11    |  06/01/2016
22    |  01/01/2015
22    |  01/10/2015
33    |  01/01/2015
33    |  01/12/2015
44    |  01/01/2015
44    |  04/05/2015
44    |  18/10/2015

The result should be that way:

22    |  01/10/2015  |  XXX
44    |  18/10/2015  |  VVV

Important note: PayTO is a string Field

Upvotes: 1

Views: 32

Answers (1)

amdixon
amdixon

Reputation: 3833

query

select p.TZ, 
date_format(max(str_to_date(p.PayTO, '%d/%m/%Y')), '%d/%m/%Y') as dt,
m.FName
from PayTbl p
inner join MenTbl m
on p.TZ = m.TZ
group by p.TZ, m.FName
having max(str_to_date(p.PayTO, '%d/%m/%Y'))
<      DATE('2015-10-20')
;

output

+----+------------+-------+
| TZ |     dt     | FName |
+----+------------+-------+
| 22 | 01/10/2015 | XXX   |
| 44 | 18/10/2015 | VVV   |
+----+------------+-------+

sqlfiddle

notes

due to the PayTO field type, have to use str_to_date. this will perform slower than the analogous query with date typed field

Upvotes: 2

Related Questions