sql - How to find MIN and MAX of two columns from TWO different tables? -


table1 contains time of first call made agent , time of last call made agent every day grouped agent name.

agent      firstcall           lastcall         8/5/2013 10:59    8/5/2013 19:50         8/6/2013 11:06    8/6/2013 19:50 b         8/5/2013 10:33    8/5/2013 10:35 c         8/5/2013 10:04    8/5/2013 9:56 c         8/6/2013 10:02    8/6/2013 9:47 d         8/5/2013 10:37    8/5/2013 18:47 d         8/6/2013 14:58    8/6/2013 18:19 d         8/6/2013 10:01    8/6/2013 9:59 e         8/5/2013 12:29    8/5/2013 18:51 e         8/6/2013 12:05    8/6/2013 18:48 f         8/6/2013 11:15    8/6/2013 19:49 g         8/5/2013 10:04    8/5/2013 10:09 g         8/6/2013 10:39    8/6/2013 9:54

table2 consists of agent name , daily login , logout time agent.

 agent            login               logout        2013-08-05 11:02:52    2013-08-05 20:05:45        2013-08-06 11:00:30    2013-08-06 20:06:47 b        2013-08-05 08:59:07    2013-08-05 18:01:58 b        2013-08-06 09:11:43    2013-08-06 18:08:49 c        2013-08-05 08:58:21    2013-08-05 17:59:29 c        2013-08-06 08:59:13    2013-08-06 18:03:53 d        2013-08-05 10:37:55    2013-08-05 19:56:20 d        2013-08-06 10:37:04    2013-08-06 20:00:43 e        2013-08-06 09:20:50    2013-08-06 18:00:35 f        2013-08-05 10:58:06    2013-08-05 20:00:24 f        2013-08-06 10:49:19    2013-08-06 20:01:37 g        2013-08-06 11:00:17    2013-08-06 19:58:31 h        2013-08-05 09:00:38    2013-08-05 18:16:16 h        2013-08-06 08:56:38    2013-08-06 17:57:00

i need join these tables in such manner agent, firstcall, lastcall, login, logout each day. in example above, i've listed data 2 days data exists on 2 years (>700 days).

previously, i've tried connecting these tables via 'agent' results in multiple records. way out?

something work. may there'is better way convert timestamp date.

select t1.agent, t1.firstcall, t1.lastcall, t2.login, t2.logout table1 t1     inner join table2 t2 on         t2.agent = t1.agent ,         to_char(t1.firstcall, 'yyyymmdd') = to_char(t2.login, 'yyyymmdd')

see sql fiddle example


Comments

Post a Comment

Popular posts from this blog

matlab - Deleting rows with specific rules -

i got 20*3 cell array , need delete rows contains "137", "2" , "n:t" origin data: 't' '' '' 'np(*)' '' '' [ 137] '' '' [ 2] '' '' 'are' 'and' 'np(fcc_a1#1)' '' '' '1:t' [ 1200] [0.7052] '' [1.2051e+03] [0.7076] '' 'are' 'and' 'np(fcc_a1#3)' '' '' '2:t' [ 1200] [0.0673] '' [1.2051e+03] [0.0671] '' 'are' 'and' 'np(m23c6)' '' '' '3:t' [ 1200] [0.227...
Read more

php - MySQLi multi_query results for later use -

i using mysqli multi_query work several select statemets @ time. know how handle results, able use them later in code. example: <?php //connection stuff $query = "select name, surname database1;"; $query.= "select car, year, type database2 carid='1';"; $query.= "select product, price database3;"; if ($mysqli->multi_query($query)) { if($result = $mysqli->store_result()) { while($row = $result->fetch_row()) { --> here? } } } ?> <html> <div id='persona'> <?php foreach() { --> print name + surname } ?> </div> <div id='cars'> <?php foreach() { --> print car + year + type } ?> </div> <div id='product'> <?php foreach() { --> print product + price } ?> </div> </html> one more thing, prepared statements not possible when using multiple_query, right? there no benefit in putting unrelated queries in 1 multi ...
Read more