sql server - SQL First Time In Last Time Out (Transact-SQL..preferrably) -

-

i have following table contains time in , time out of people: 

 create table test (        timecardid int      , trandate date      , employeeid int      , trantime time      , trantype varchar(1)      , projcode varchar(3) )

the task earliest trantime trantype (perhaps using min) , latest trantime trantype z (using max), of in trandate (ie. trantype july 17 8:00 , trantype z july 17 7:00pm).

the problem is, output should in same format table it's coming from, meaning have leave data , filter out rest (that aren't earliest , latest in/out date, per employee)

my current solution use 2 different select commands earliest, latest. combine them both.

i wondering though, there simpler, single string solution?

thank much.

edit (i apologize, here sample. server sql server 2008):

 timecardid | trandate | employeeid | trantime | trantype | projcode        1      2013-04-01      1        8:00:00              sample1       2      2013-04-01      1        9:00:00              sample1       3      2013-04-01      2        7:00:00              sample1       4      2013-04-01      2        6:59:59              sample1       5      2013-04-01      1       17:00:00        z       sample1       6      2013-04-01      1       17:19:00        z       sample1       7      2013-04-01      2        17:00:00       z       sample1        8      2013-04-02      1        8:00:00              sample1       9      2013-04-02      1        9:00:00              sample1      10      2013-04-02      2        7:00:58              sample1      11      2013-04-02      2       18:00:00        z       sample1      12      2013-04-02      2       18:00:01        z       sample1      13      2013-04-02      1       20:00:00        z       sample1

expected results (the earliest in , latest out per day, per employee, in select command):

  timecardid | trandate | employeeid | trantime | trantype | projcode        1      2013-04-01      1        8:00:00              sample1        4      2013-04-01      2        6:59:59              sample1          6      2013-04-01      1       17:19:00        z       sample1       7      2013-04-01      2        17:00:00       z       sample1       8      2013-04-02      1        8:00:00              sample1      10      2013-04-02      2        7:00:58              sample1      12      2013-04-02      2       18:00:01        z       sample1      13      2013-04-02      1       20:00:00        z       sample1

thank much

perhaps you're looking for:

select      t.*       test t      trantime in (         (select min(trantime) test t1 t1.trandate = t.trandate , trantype = 'a'),         (select max(trantime) test t2 t2.trandate = t.trandate , trantype = 'z')     )

changing answer account "per employee" requirement:

;with earliestin (     select trandate, employeeid, min(trantime) earliesttimein     test      trantype = 'a'     group trandate, employeeid  ),  latestout (     select trandate, employeeid, max(trantime) latesttimeout     test      trantype = 'z'     group trandate, employeeid  ) select *  test t      exists (select * earliestin t.trandate = earliestin.trandate , t.employeeid = earliestin.employeeid , t.trantime = earliestin.earliesttimein)      or exists (select * latestout t.trandate = latestout.trandate , t.employeeid = latestout.employeeid , t.trantime = latestout.latesttimeout)

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