sql - Inner join Logic in mysql -

-

i using inner join want combine 2 result display in singel table.
have created query check latecoming , earlyleaving want both output displayed 1 singel table.
query latecoming:

    select k.empid,min(k.dateofcheckin) mindate,     case      when min(time(dateofcheckin))<=@i 'ontime'     when min(time(dateofcheckin)) between @i , @j 'late'     when min(time(dateofcheckin)) between @j , @n 'halfday'     when dayofweek(date(dateofcheckin)) =7 ,  min(time(dateofcheckin)) >@j  'halfday'     else 'absent' end loginstatus     checkinlogs k join     (select      @i:=time(r.latecomer) latecoming,     @j:=time(r.latecomingendtime) latecomingend,     @m:=time(lunchstarttime),     @n:=time(lunchendtime),     @o:=time(earlyleavingstarttime),     @p:=time(earlyleavingendtime),      e.empid     employees e join profiles r on e.leaveprofile=r.profilename) h     on k.empid=h.empid     k.dateofcheckin='in'  ,       dayofweek(date(k.dateofcheckin)) != 1 ,     k.branchid=1 , date(dateofcheckin) between '2013-05-1' , '2013-05-29'     group date(k.dateofcheckin),k.empid

query earlyleaving:

select      empid,date(dateofcheckin) logoutdate,dateofcheckin,max(time(dateofcheckin)) logouttime,     case      when max(time(dateofcheckin))>=@p 'ontime'     when dayofweek(date(dateofcheckin)) =7 ,  max(time(dateofcheckin)) < @m     'halfday'     when dayofweek(date(dateofcheckin)) =7 ,  max(time(dateofcheckin)) > @m  'ontime'     when max(time(dateofcheckin)) between @o , @p 'earlyleaving'     when max(time(dateofcheckin)) between @m  , @o 'halfday'     when max(time(dateofcheckin))<=@m 'absent'     else 'absent' end logoutstatus     checkinlogs k1 join     (select      @i:=time(r.latecomingstarttime) latecoming,     @j:=time(r.latecomingendtime) latecomingend,     @m:=time(lunchstarttime),     @n:=time(lunchendtime),     @o:=time(earlyleavingstarttime),     @p:=time(earlyleavingendtime),      e.empid     employees e join profiles r on e.leaveprofile=r.profilename) h1     on k1.empid=h1.empid     k1.dateofcheckin='out'      , k1.branchid=1     , dayofweek(date(k1.dateofcheckin)) != 1 ,       date(k1.dateofcheckin) between '2013-06-1' , '2013-06-29'     group date(k1.dateofcheckin),k1.empid

below example of how i'd approach query, this quick copy , past job you'd have fix syntax errors etc. main difference need employee details once @ start of query , add common field between 2 current queries (other employee) join on. in case have used logoutdate

select * (select      @i:=time(r.latecomer) latecoming,     @j:=time(r.latecomingendtime) latecomingend,     @m:=time(lunchstarttime),     @n:=time(lunchendtime),     @o:=time(earlyleavingstarttime),     @p:=time(earlyleavingendtime),      e.empid employees e join profiles r on e.leaveprofile=r.profilename) h join (select          empid, date(dateofcheckin) logoutdate,         dateofcheckin,max(time(dateofcheckin)) logouttime,         case              when max(time(dateofcheckin))>=@p 'ontime'             when dayofweek(date(dateofcheckin)) =7 ,  max(time(dateofcheckin)) < @m                 'halfday'             when dayofweek(date(dateofcheckin)) =7 ,  max(time(dateofcheckin)) > @m                  'ontime'             when max(time(dateofcheckin)) between @o , @p 'earlyleaving'             when max(time(dateofcheckin)) between @m  , @o 'halfday'             when max(time(dateofcheckin))<=@m 'absent'         else 'absent' end logoutstatus       checkinlogs k1           k1.dateofcheckin='out'        , k1.branchid=1       , dayofweek(date(k1.dateofcheckin)) != 1       group date(k1.dateofcheckin), k1.empid) out on out.empid = h.empid join (select k.empid, date(dateofcheckin) logoutdate,                 min(k.dateofcheckin) mindate,                 case                      when min(time(dateofcheckin))<=@i 'ontime'                     when min(time(dateofcheckin)) between @i , @j 'late'                     when min(time(dateofcheckin)) between @j , @n 'halfday'                     when dayofweek(date(dateofcheckin)) =7                          , min(time(dateofcheckin)) >@j  'halfday'                     else 'absent' end loginstatus       checkinlogs k join         k.dateofcheckin='in'  ,           dayofweek(date(k.dateofcheckin)) != 1 ,         group date(k.dateofcheckin),k.empid) in on in.empid = out.empid , in.logoutdate = out.logoutdate

i consider moving case , group bys top level query.


Comments

Post a Comment

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

-
i trying fetch records database , having above error this classs appropriate getters , setters public class user { private int id; private string username; private string password; private string email; i have create database , inserted data below configuration <bean id="datasource" class="org.apache.commons.dbcp2.basicdatasource" destroy-method="close"> <property name="driverclassname" value="com.mysql.jdbc.driver"/> <property name="url" value="jdbc:mysql://localhost:3306/usersdb"/> <property name="username" value="root"/> <property name="password" value=""/> </bean> this complete stacktrace severe: servlet.service() servlet [appservlet] in context path [/spring] threw exception [request processing failed; nested exception org.hibernate.exception.sqlgrammarexception: not ...
Read more

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

-
Image
i have postgresql db number of tables. if query: select column_name information_schema.columns table_name="my_table"; i list of columns returned properly. however, when query: select * "my_table"; i error: (programmingerror) relation "my_table" not exist 'select *\n "my_table"\n' {} any thoughts on why can columns, can't query table? goal able query table. you have include schema if isnt public one select * <schema>."my_table" or can change default schema show search_path; set search_path my_schema; check table schema here select * information_schema.columns for example if table on default schema public both works ok select * parroquias_region select * public.parroquias_region but sectors need specify schema select * map_update.sectores_point
Read more

asp.net mvc - breakpoint on javascript in CSHTML? -

-
i have .cshtml file has javascript tag. within script tag have few lines of javascript. first line of javascript refers property in @viewbag. other javascript lines plain script without mvc related references. observation: can place breakpoints (visual studio 2015) on each of these javascript lines. however, breakpoints has white dot in center of breakpoint symbol (red circle) except line @viewbag reference (this particular line not have white dot in center). it seems breakpoint without white dot hit during run-time. question: can explain going on here? white dot mean? why plain red breakpoint line line hitting breakpoint? the visual studio debugger expecting debug actual server-side code within .cshtml file opposed client-side javascript within it. iirc, visual studio allow debug javascript code expected in internet explorer (and possibly edge), other browsers want use either third-party tool or developer tools (f12) within browser. an easy approach take advant...
Read more