<div>Hi Muhannad,</div><div> </div><div>Did you try using MIN and MAX function? I guess that following query solves your problem.</div><div> </div><div><div>select employee_id, day, sum(total_hours) as total_hours, (select name from employee where id = employee_id) as emp_name, <em><strong>min(sign_in) as first_sign_in, max(sign_in) as last_sign_in</strong></em></div><div>from bioemployee where sign_in BETWEEN X and Y GROUP BY employee_id, day ORDER BY day;</div><div> </div><div>Best regards.</div></div><div> </div><div> </div><div><br /></div><div><br /></div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div><br /></div><div><br /></div><div><br /></div><div>20.04.2017, 19:56, "Muhannad Shubita" <muhannadshubita(at)gmail(dot)com>:</div><blockquote type="cite"><div dir="ltr"><div>Good day,</div><div><br /></div><div>I have a table with biometric info about employees (let's call it bioemployee):</div><div><br /></div><div>id serial not null -- primary key</div><div>employee_id -- foreign key references employee table</div><div>day char(8) -- YY-MM-DD</div><div>sign_in TIMESTAMP</div><div>total_hours INTEGER</div><div><br /></div><div>I want to display details grouped by day & employee Id, for example:</div><div><br /></div><div><br /></div><div>Day <span style="white-space:pre;"> </span>Employee<span style="white-space:pre;"> </span>Total-Hours<span style="white-space:pre;"> </span> First-Sign-In <span style="white-space:pre;"> </span>Last-Sign-In</div><div><br /></div><div>20/4<span style="white-space:pre;"> </span>emp1<span style="white-space:pre;"> </span>4<span style="white-space:pre;"> </span>8:22<span style="white-space:pre;"> </span>3:25</div><div>21/4<span style="white-space:pre;"> </span>emp1<span style="white-space:pre;"> </span>7<span style="white-space:pre;"> </span>9:00<span style="white-space:pre;"> </span>4:11</div><div>21/4<span style="white-space:pre;"> </span>emp2<span style="white-space:pre;"> </span>2<span style="white-space:pre;"> </span>11:00<span style="white-space:pre;"> </span>01:11</div><div><br /></div><div><br /></div><div>I created a pgsql function to get the details through the below query:</div><div><br /></div><div>select employee_id, day, sum(total_hours) as total_hours, (select name from employee where id = employee_id) as emp_name</div><div>from bioemployee where sign_in BETWEEN X and Y GROUP BY employee_id, day ORDER BY day;</div><div><br /></div><div>now the problem is with getting First-Sign-In & Last-Sign-In per group (employee & day), I have currently implemented it in a FOR loop:</div><div><br /></div><div>for RECORD in query LOOP</div><div><span style="white-space:pre;"> </span>--First-Sign-In</div><div><span style="white-space:pre;"> </span>select sign_in from bioemployee where employee_id = -- and day = -- and sign_in BETWEEN X and Y ORDER BY sign_in LIMIT 1;</div><div><span style="white-space:pre;"> </span>--Last-Sign-In</div><div><span style="white-space:pre;"> </span>select sign_in from bioemployee where employee_id = -- and day = -- and sign_in BETWEEN X and Y ORDER BY sign_in DESC LIMIT 1;</div><div><span style="white-space:pre;"> </span></div><div><span style="white-space:pre;"> </span>return next json_build_object(first_sign_in, last_sign_in, ..rest of details);</div><div>END LOOP</div><div><br /></div><div>but If I had 100 employees over a span of 30 days, this would be 6000 queries inside the loop! which I am sure you would agree is an overkill</div><div><br /></div><div><br /></div><div>is there a better way to do this? </div><div><br /></div><div><br /></div><div>Thanks.</div>
</div>
</blockquote>