如何编写sql查询 [英] how to write sql query

问题描述

DECLARE @PDate date,
@In_Punch datetime
SELECT *From MASTERPROCESSDAILYDATA where PDate>='02-1-13' AND PDate<= '02-28-13'
IF(@In_Punch>='08:00:00' AND @In_Punch <= '09:30:00')
BEGIN
    UPDATE MASTERPROCESSDAILYDATA set Status = 'P'  SELECT  *from MASTERPROCESSDAILYDATA;
END

ELSE

ELSE

BEGIN
    UPDATE MASTERPROCESSDAILYDATA set Status = 'HL'  SELECT  *from MASTERPROCESSDAILYDATA;
END</pre>

它不起作用，否则部分正在执行&状态更新给所有学生。



任何人都可以帮助我如何编写SQL查询，其中In_punch介于'08：00：00 AM'和'10：30之间： 00 AM'



在'04：00：00 PM'和'05：30 PM PM之间Out_punch'





考虑一组5名学生，如std1，std2，std3，std4，std5（来自table1）



例如：

std1，std2超越了时间，即在'08：00：00 AM'和'10：30：00AM'之间的In_punch，
$ 04 $ b在04之间的Out_punch： 00:00'和'05：30：00'

更新状态='P'

否则

UPDATE状态='HL'



如何使用条件或循环更新学生的状态





提前谢谢

It is not working only else part is executing & status is updated to all the students.

Can anyone help me how to write sql query where In_punch between '08:00:00 AM' AND '10:30:00AM'

Out_punch between '04:00:00 PM' AND '05:30:00PM'


Consider for a set of 5 students like std1,std2,std3,std4,std5(from table1)

eg:
std1,std2 made their punches beyond the timings i.e., In_punch between '08:00:00 AM' AND '10:30:00AM',
Out_punch between '04:00:00 PM' AND '05:30:00PM'
UPDATE Status ='P'
otherwise
UPDATE Status ='HL'

How can i update the status of students using if condition or loops


Thanks in advance

推荐答案

问题可能是您只需要查看时间部分：DateTime既有日期又有时间，所以如果您的时间戳有效是升然后他们可能包含一个日期元素。但是那个代码看起来很奇怪...

你可能想做的是

The problem is probably that you need to look at the time parts only: a DateTime has both date and time, so if your timestamps are literally that then they probably include a date element. But that code looks rather odd anyway...
What you probably want to do is along the lines of

UPDATE MASTERPROCESSDAILYDATA SET Status =  
   CASE WHEN CAST(PDate AS TIME) 
             BETWEEN CAST ('08:00' AS TIME) 
             AND CAST('09:30' AS TIME)
   THEN 'P'
   ELSE 'HL'
   END

错误的列名[/ edit]

[edit]Wrong column name[/edit]

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