user data by unspecific date, according to param "last entry, last but one, last but two, etc.", MySQL

user data by unspecific date, according to param "last entry, last but one, last but two, etc."

##user data by unspecific date, according to param "last entry, last but one, last but two, etc."

##Task:
##  Query data by "human sense" date notation. We don't know when it happened, 
##  just want to see data from last day, it happened, last but one day, etc.
##  Solved by qerying nth occurence of distinct date,

##Benutzerdaten nach unspezifischem Datum, abhängig von Parameter "letzter Eintrag, vorletzter, vorvorletzter, etc."

##Aufgabe:
##  Datenabfrage gemäß menschlichem Empfinden eines Datums. Wir wissen nicht wann es war, 
##  wir wollen einfach die Daten vom letzten Mal, vom vorletzten, vorvorletzten Tag, etc.
##  Umgesetzt durch Abfrage des n-ten Auftretens der DISTINCT Dates

##challenge origin / Ursprung :
##  https://www.php.de/forum/webentwicklung/datenbanken/1504839-select-max-datum-bei-mehreren-ausgaben-zu-diesem-datum

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

select version() as 'mysql version';

## trainingstabelle
create table if not exists mytraining (
  id           mediumint not null auto_increment,
  datum        timestamp,                            ## Uhrzeit ist auch ok, Abfrage ignoriert sie / additional time data does no harm
                                                     ##   wird ein Abfrageparameter / will be one parameter
  benutzer     varchar(20),                          ## Benutzer / user
                             ##   der zweite Parameter      / 2nd query parameter
  uebung       varchar(20),
  satz         mediumint,
  wiederholung mediumint,
  gewicht      mediumint,
  anstrengung  mediumint,
  PRIMARY KEY  (id)
);

## trainingsdaten
insert into mytraining (datum, benutzer, uebung,  satz, wiederholung,  gewicht, anstrengung)
values (STR_TO_DATE('2017-05-05 02:20pm', '%Y-%m-%d %h:%i%p'), 'user1', 'ub1', 1,15,50,2),
       (STR_TO_DATE('2017-05-05 02:25pm', '%Y-%m-%d %h:%i%p'), 'user1', 'ub2', 1,15,50,3),
       (STR_TO_DATE('2017-05-05 02:35pm', '%Y-%m-%d %h:%i%p'), 'user1', 'ub1', 1,12,50,4),
       (STR_TO_DATE('2017-05-02 03:20pm', '%Y-%m-%d %h:%i%p'), 'user1', 'ub2', 1,17,50,2),
       (STR_TO_DATE('2017-05-02 04:20pm', '%Y-%m-%d %h:%i%p'), 'user1', 'ub1', 1,15,50,2),
       (STR_TO_DATE('2017-05-05 02:20pm', '%Y-%m-%d %h:%i%p'), 'user2', 'ub2', 1,15,50,2),
       (STR_TO_DATE('2017-05-01 02:40pm', '%Y-%m-%d %h:%i%p'), 'user2', 'ub3', 1,12,50,2),
       (STR_TO_DATE('2017-04-30 02:20pm', '%Y-%m-%d %h:%i%p'), 'user2', 'ub2', 1,15,50,2),
       (STR_TO_DATE('2017-04-19 02:40pm', '%Y-%m-%d %h:%i%p'), 'user2', 'ub3', 1,12,50,2);

## Kontrolle der Daten / list data
## select * from mytraining;
  
  ## Benutzer und Datum ohne Uhrzeit  / user and date, time stripped off
  select distinct benutzer, date(datum) from mytraining;

## "letzte" Nummer erzeugen Beispiel mit Einschränkung auf Benutzer / example generation of "n-th occurence" along with user filtering
## kann eingeschränkt werden auf N<Max Wert / could be locked down to max. desired occurences
SELECT @row := @row + 1 as row, t.*
FROM (select distinct benutzer, date(datum) from mytraining where benutzer='user1') t, (SELECT @row := 0) r;

## select (vor (vor))letzter Trainingstag usw. / select last (but one|two) training day
##   wichtig: funktioniert so nur mit Benutzereinschränkung und Einschränkung auf 1 Tag
##   import: this way it works only with specific user filtering and "occurence" mapping to one day
select t.*, lst.row  
  from mytraining t,
       (SELECT @row := @row + 1 as row, t.*
          FROM (select distinct benutzer, date(datum) letztesdatum from mytraining where benutzer='user2') t, (SELECT @row := 0) r) lst
                                                                                         ## benutzer is mandatory query parameter
 where t.datum between lst.letztesdatum and lst.letztesdatum + INTERVAL 1 DAY
   and lst.row      = 3  ## row is mandatory query parameter: 1= letzter Tag, 2=vorletzter, 3 = drittletzter usw.
   and lst.benutzer = t.benutzer;
   
drop table mytraining;


run \| edit \| history \| help	0