sql - get number of occurrences taking 1 for every 3 days group -

sql - get number of occurrences taking 1 for every 3 days group -

i've got table employees late work. need send study human resources showing every user got late, taking business relationship can count warning per user if user got late in period of 3 days @ to the lowest degree 1 time within period.

the first info need total number of warnings sent hr manager evaluate global "lateness".

users got late 1 day receive 1 warning, if got late twice or more warnings they'll receive depend if received warning within 3 days period counting day one.

let's see example:

joe mon 9th mark mon 9th tim mon 9th joe tuesday 10th joe quarta-feira 11th joe th 12th tim fri 13th

taking info table above example.

joe receive 2 warnings: first monday, , sec thursday. tuesday , quarta-feira discarded because belonged same 3 day period.

mark receive 1 warning monday.

tim receive 2 warnings. first mon , sec friday.

maybe number not possible using standard sql query , cursors need done.

thanks in advance

ok... info missing here, correctly stated in both comments (quassnoi , mr. llama).

rdbms in utilize can impact on solution has date functions , date algebra , not rdbms share same extended function set. i have presumed mysql 5.5, quite mutual , can tested on sqlfiddle.

your 3 day period bit vague. same employees, or on depend? 3 days, or half week (mon-wed, thu-sat)? happens after that? utilize sun-tue, wed-fri , on? i have presumed half-weeks (sun-wed, thu-sat) same employees.`so period identified year, week-of-year, half-week.

the lastly point should clear expected result set. want list of warning days or count or what? i have presumed list of warnings date of each (i've taken first date each combination of employee & period).

creating sample dataset these statements:

create table lateentrances ( employee varchar(20), datelate date ); insert lateentrances values('joe' ,'2014.06.09'); insert lateentrances values('mark','2014.06.09'); insert lateentrances values('tim' ,'2014.06.09'); insert lateentrances values('joe' ,'2014.06.10'); insert lateentrances values('joe' ,'2014.06.11'); insert lateentrances values('joe' ,'2014.06.12'); insert lateentrances values('tim' ,'2014.06.13');

the next query solves problem:

select i.employee, i.yearlate, i.weeklate, i.periodlate, min(i.datelate) ( select employee, datelate, year(datelate) yearlate, weekofyear(datelate) weeklate, floor(dayofweek(datelate)/4) periodlate lateentrances ) grouping i.employee, i.yearlate, i.weeklate, i.periodlate;

(sqlfiddle here)

the 3 columns yearlate, weeklate , periodlate identify warning period. concatenate them in single period identification column:

select i.employee, i.periodlate, min(i.datelate) ( select employee, datelate, concat_ws('*', year(datelate) , weekofyear(datelate) , floor(dayofweek(datelate)/4) ) periodlate lateentrances ) grouping i.employee, i.periodlate;

... or hide them alltogether (in select), though must still utilize them grouping by:

select i.employee, min(i.datelate) ( select employee, datelate, concat_ws('*', year(datelate) , weekofyear(datelate) , floor(dayofweek(datelate)/4) ) periodlate lateentrances ) grouping i.employee, i.periodlate;

you can alter period calculation logic else, strict 3 days of year, or 3 days of month periods. there many possibilities.

...as per assumptions made. clear open points , i'll seek create reply better. in meantime should plenty started.

sql