如何使用IF条件编写子查询 [英] How to write sub Query using IF conditions

问题描述

大家好，



我有一张桌子和多列我的桌子数据是这样的

tblEmployeeTimeCard

Hi All,

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

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

以上行是单个Employee工作统计报告。我的问题是

1）如何获得特定日期的总工时（SUM）。

2）员工工作时间超过6小时特别是每天6小时检测到30小时mints形成总小时数

（即 - 当autoDetect = 1时减去检测mints形式（总和）总工作小时数）



怎么写查询获得单行总工时。



请帮助我..



谢谢和问候

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

-- 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

对于你的问题2，我没有得到。提供更多细节。如果可能，发送一些样本数据。如果访问超过一圈（从日期是2013年04月04日下午11:55:00到2013-04-05 19:10:00 PM）

然后我们不能罚款时间区别。所以检查问题1的代码并给出反馈。



问候，

GVPrabu

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

这篇关于如何使用IF条件编写子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！