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
Can anyone help me how to write SPC
I want output as follows
OUTPUT:
@PDate @In_Punch @Out_Punch Status
Consider for any month Between 08:00 AM to 09:30AM Between 04:00 PM to 05:30 PM P
@In_Punch > 09:30 AM @Out_Punch <=03:59 PM HL
@In_Punch = NULL @ Out _Punch = NULL A