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

Comments

Post a Comment

Popular posts from this blog

ruby on rails - Devise Logout Error in RoR -

ruby on rails - Devise Logout Error in RoR - while using devise in ror, encountering unusual error when trying log out url different root url. displays me : actioncontroller::invalidauthenticitytoken in devise::sessionscontroller#destroy" for illustration if rooting path after log in '/structure', , seek log out '/general' error, while logging out '/structure' fine. any help highly appreciated. ruby-on-rails ruby devise
Read more

c# - Create a Notification Object (Email or Page) At Run Time -- Dependency Injection or Factory -

c# - Create a Notification Object (Email or Page) At Run Time -- Dependency Injection or Factory - i have code emails me notification when happens. trying add together page , also remove dependency on email. for reference; have name of notification want send stored string in database. @ runtime have list of people , string representation of want receive. i thought dependency injection ninject, kernel bindings got big , seemed hacking together. then thought simple factory, activator.createinstance(email/page) inotifcation, don't know how handle differing constructor parameters (unless utilize common, complex property, employee, has info need). so far have this: public interface inotification { void sendmessage(); } public class email: inotification { private readonly string _toemailaddress; private string body { { homecoming "email"; } } private static string smtphost { ...
Read more

model view controller - MVC Rails Planning -

model view controller - MVC Rails Planning - i'm starting rails , i'm building app stores films info, user can watch film , give grade on web application. i have coded cinema model, controller , views. now, have 2 questions: should need create model , controller grading? in case, should create or should wait , create first users model , controller? yes, on model , controller grades. that'd feasible way maintain track of who's graded what. it's create first, i'd start users, since grades depend on both films , users. ruby-on-rails model-view-controller
Read more