任何人都可以帮我写SPC [英] Can anyone help me how to write SPC

问题描述

如何从'2013-11-18 14：15：39.000'获得时间部分



我正在使用MS SQL SERVER2008R2

我想更新状态AS'P'，'A'，'HL'



SPC：

How to get time part from '2013-11-18 14:15:39.000'

I am using MS SQL SERVER2008R2
I want to update status AS 'P','A','HL'

SPC:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE  SP_DIFF(@FromDate datetime,@ToDate datetime)
AS
BEGIN	
SET NOCOUNT ON;

DECLARE 
@In_Punch datetime,
@Out_Punch datetime
--@PDate datetime
 
SELECT CASE WHEN  CAST(@In_Punch AS TIME,@Out_Punch AS TIME ) BETWEEN CAST ('08:00' AS TIME) 
             AND CAST('16:00'  AS TIME) THEN 'P'
             WHEN  CAST(@Out_Punch AS TIME ) BETWEEN CAST ('16:00' AS TIME) 
             AND CAST('17:30'  AS TIME) THEN 'P'
--WHEN  CAST(@In_Punch AS TIME ) BETWEEN CAST ('08:00' AS TIME) 
             --AND CAST('09:30'  AS TIME) THEN 'P'
WHEN  CAST(@In_Punch AS TIME,@Out_Punch AS TIME ) BETWEEN CAST ('09:31' AS TIME) 
             AND CAST('15:59'  AS TIME) THEN 'HL'
             ELSE 'A'
--SELECT *FROM MASTERPROCESSDAILYDATA where PDate > '02-1-13' AND PDate <= '02-28-13'
      
END             
END 
GO

任何人都可以帮我写SPC

Can anyone help me how to write SPC

OUTPUT:

@In_Punch                           @Out_Punch                Status
08:00 AND 09:30     between         16:00 AND 17:30            'P'
09:30               between         15:59                      'HL'


ELSE 'A'

提前致谢

Thanks in advance

推荐答案

SELECT CASE WHEN  CAST(@In_Punch AS TIME,@Out_Punch AS TIME ) BETWEEN CAST ('08:00' AS TIME)
             AND CAST('16:00'  AS TIME) THEN 'P'

这是你发明的代码。这不是SQL。这不是它的工作方式，你需要阅读正确的SQL是什么，而不仅仅是做些什么。您需要分几步完成此操作。首先，是两次之间的时间，然后是两者之间的时间。



另外，如果你必须把日期时间和他们把它转到一个时间，那就去做一次。在你用来检查的时候做一次，而不是一遍又一遍。这使您的代码更高效，更易读。

This is code you invented. It's not SQL. That's not how it works, you need to read up on what the right SQL is, not just make something up. You'd need to do this in several steps. First, is the in time between the two times, and then, is the out time between the two.

Also, if you must take a datetime and them turn it in to a time, do it once. Do it once for the times you use to check as well, not over and over again. This makes your code more efficient and more readable.

declare @eightAM as time = cast('08:00' as time)
declare @fourPM as time = cast('16:00' as time)
declare @timein as time = cast(@In_punch as time)
declare @timeout as time = cast(@Out_punch as time)

select case when @timein >= @eightAM and @timeOut <= @timeout and @timein < @timeOut then 'P'

等等。注意我的支票比你少。我的代码检查相同的内容，但更具可读性。

and so on. Note I am doing less checks than you. My code checks the same thing, but is more readable.

这篇关于任何人都可以帮我写SPC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！