How to write sub Query using IF conditions




 Hi All,  

  

I have one table and multiple columns my table data like this  

tblEmployeeTimeCard  

  

 Copy Code

```
RecID |EmployeeID|clientID |FromTime              |ToTime                 |AutoDetect 
1     |  1       |1        |2013-04-05 08:10:00AM |2013 -04-05 12:00:00PM |1
2     |  1       |1        |2013-04-05 13:00:00PM |2013-04-05 19:10:00PM  |1
3     |  1       |1        |2013-04-05 20:00:00PM |2013-04-05 23:45:00 pM |1
```
  

  

the above rows are single Employee work stats report. my question is   

1)how to get the total(SUM) working hours in particular day.  

2)Employee works above 6hrs in particular Day then Every 6hr detect the 30 mints form total hours  

(i.e- Subtract the detect mints form (sum)total working hrs when autoDetect= 1)  

  

how to write the query for get the total working hours with in the single row.  

  

please help me..  

  

Thanks and Regards
Hi,  

  

check the following Code  

SQLCopy Code

```
-- Question 1 Answer
SELECT E.EmployeeID,E.Day, (E.TotalTime/60) 'TotalHours', (E.TotalTime%60) 'Minites'
FROM (SELECT EmployeeID,CONVERT(VARCHAR(10),FromTime,102) 'Day',SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
      FROM tblEmployeeTimeCard
      GROUP BY EmployeeID,CONVERT(VARCHAR(10),FromTime,102))E

-- Question 1&2 Answer
SELECT E.EmployeeID,E.Day, (E.TotalTime/60) 'TotalHours', (E.TotalTime%60) 'Minites',
CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND((E.TotalTime/60*6),1))
     ELSE (E.TotalTime/60) END 'TotalWorkHours'
FROM (SELECT EmployeeID,CONVERT(VARCHAR(10),FromTime,102) 'Day',SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
      FROM tblEmployeeTimeCard
      GROUP BY EmployeeID,CONVERT(VARCHAR(10),FromTime,102))E
```
  

  

For ur Question 2, I am not getting. give some more details about that. if possible send some sample data. if access over lap like (From date is 2013 -04-05 11:55:00PM to 2013-04-05 19:10:00PM)  

then we can't able to fine the time difference. So check the code for Question No 1 and give the feedback.  

  

Regards,  

GVPrabu
You need to iterate over the resource...

 
```
while ($row = mysql_fetch_object($tariff_query)) {
    echo $row->somefield;
    echo $row->someotherfield;
}

```
 So you now get all the records not just the first one.

 BTW, if you want to use an object, why not use PDO or mysqli? mysql is dead - or as good as. Hi, Really hard to work this out from your query but I suspect the answer would be a correlated sub query to find the latest appointment date.



https://www.geeksforgeeks.org/sql-correlated-subqueries/ it would depend on a number of different factors if there is a claim here. firstly, it would depend on how many hours overtime you have worked and if this is every weekend or if there is a pattern. (you can only legally work 48 hours per week and you must have a signed agreement to say if you want to work more). then it will depend on what your contract states on overtime.


legally, guaranteed overtime would count as normal working hours. the issue on why you need to consider the above is to determine whether your overtime hours have become contractual.


in employment law where certain terms may become implied into an employment contract by ‘custom and practice’. this makes them contractually binding even if they are not written down anywhere. this area of law is rather complicated and there is always a degree of uncertainty as to whether a term has become implied in this way. it is usually only down to the tribunals to establish with certainty if that is the case. however, it does not prevent employees from raising this argument with their employers.


in general, your overtime would need to have been clearly communicated and consistently applied for a number of years before it is considered an implied contractual term. therefore, something that is uncertain, or is not applied consistently is unlikely to qualify.


i would advise that you raise this issue with your employer/ hr department to see what they say about it. Hey,



You could consider using this API ‘QUSRJOBI’ with format ‘*JOBI0300′.*

It Returns you Job Status, JOBQ details and few other details which you might need. [Here](https://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/apis/qusrjobi.htm) You go for full details.

Pradeep. as i had the time, thought i would tidy things up, just one custom query needed, no view required. just change the office names and copy/paste into a custom query 
have a great christmas party! 
select sum(office1) as office1,sum(office2) as office2,sum(office3) as office3,hours from 
((select 
cou… let table = base.gettable("table 1")

let record = await input.recordasync("record", table)

await table.updaterecordasync(record.id, {

"single select": null

}) 1) form controls use a list box for the names of the conference rooms;use datetimepicker for scheduling the reservation...set the format property to custom;set the customformat property to mm dd yyyy hh mm ssof course, will also need ok button2) dataset up the data file like this:room date time1st floor, 6/8/21, 14:00:002nd floor, 6/9/21, 10:00:003rd floor, 6/8/21, 13:30:00reads these records from the file to determine if there is a conflict.if the room is available at the requested time, writes the reservation tothe file (in append mode) and displays a message box saying the reservationis complete. otherwise display a message box saying the room is unavailableat that time. If your system is not queised (not very busy) you won’t get a report on all the objects. Run it in a semi restricted state or on the weekend when users aren’t typically on the system and batch jobs aren’t running. Then you should get very close to 100%. You could try using the in line view like this:


Select table1.col1, table1.col2, table1…., table2.col1, table2.col2, table2….  

 from  

(SELECT *  

FROM dbo.Shaft\_Scales  

WHERE dbo.Shaft\_Scales.ID = (SELECT max(ID) from dbo.Shaft\_Scales ID) as table1,  

(SELECT *  

FROM OtherTable  

WHERE dbo.OtherTable.ID = (SELECT max(ID) from dbo.OtherTable ID) as table2


—————- another approach from meandyou 


This works on [at least some] SQL processors


SELECT T1.* , T2.*  

FROM table1 T1, table2 T2  

WHERE …


Do we have to discuss why not to use “SELECT * ” in your SQL?<pre>


Try this


SELECT * FROM T1 as a, T2 as b WHERE a.ID = (SELECT max(ID) from T1)  

and b.ID = (SELECT max(ID) from T2);</pre> hi [@jitse\_b](/u/jitse_b)

welcome to the community !

yes you can, there are various ways to do so.

the simplest:

you may want to create a table with 3 columns,

key\_column, type text, value “k000” (or whatever you want, it doesn’t matter if you stick to only one comparison)

“material1”, type ref, source tabl…