Can anyone help me how to write SPC

Question

0.00/5 (No votes)

See more:

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:

C#

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

SQL

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

Posted 29-Dec-13 19:49pm

Karthik Achari

Updated 29-Dec-13 20:13pm

v5

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2013-12-29T20:59:00

SQL

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'

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.

SQL

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.