?
Solved

Computed Column if the column datetime is more than 0:00

Posted on 2011-04-25
14
Medium Priority
?
505 Views
Last Modified: 2012-05-11
having the computed column specification code below,

would request for
CASE WHEN lateduration IS NOT NULL OR ??? THEN 'late' END

??? = DateTime in lateduration is more than 0:00.
ALTER TABLE [TableName]
ADD ['attendancestatus']  AS 
(
CASE WHEN 
actualtime IS NOT NULL  AND arrivedtime IS NULL 
THEN 'absense' 
ELSE  
CASE WHEN lateduration IS NOT NULL THEN 'late' END
END)

Open in new window

0
Comment
Question by:doramail05
  • 7
  • 6
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35464320
>>if the column datetime is more than 0:00<<
Please define this.  Do you mean that the datetime column is not set to midnight?  If so then something like this perhaps:
ALTER TABLE [TableName]
ADD ['attendancestatus']  AS 
(
CASE 
	WHEN actualtime IS NOT NULL AND arrivedtime IS NULL THEN 'absense' 
	ELSE  
		CASE 
			WHEN lateduration IS NOT NULL OR DATEPART(second, lateduration) > 0 THEN 'late'
		END
END)

Open in new window

0
 
LVL 9

Expert Comment

by:kaminda
ID: 35464400
I think you should check hour and  minutes part as I would assume getting late by seconds wont be practical in a real world scenario. So it would look like this, If you want you can add the sconds as well
ALTER TABLE [TableName]
ADD ['attendancestatus']  AS 
(
CASE WHEN 
actualtime IS NOT NULL  AND arrivedtime IS NULL 
THEN 'absense' 
ELSE  
CASE WHEN lateduration IS NOT NULL OR DATEPART(hour,lateduration) > 0 OR DATEPART(minute,lateduration) > 0 THEN 'late' END
END)

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35466362
>>as I would assume getting late by seconds wont be practical in a real world scenario. <<
Actually more than impractical my solution is wrong as they could clock in on the hour, an hour late and DATEPART(second, lateduration) = 0
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:doramail05
ID: 35472056
i added the formula as :

ALTER TABLE StudentAttendance
ADD ['attendancestatus']  AS
(
CASE WHEN
actualtime IS NOT NULL  AND arrivedtime IS NULL
THEN 'absense'
ELSE  
CASE WHEN DATEPART(hour,lateduration) > 0 OR DATEPART(minute,lateduration) > 0 OR DATEPART(second, lateduration) > 0 THEN 'late' END
END)

but the attendancestatus should show 'late' because the hour is > 0.
absensestatusnotshown.jpg
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35472087
How about you post some sample data, so that we can really see what is happening.   Otherwise all we can do is hazard wild guesses ad nauseum.
0
 
LVL 1

Author Comment

by:doramail05
ID: 35472305
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35472455
Something else is going as it does show 'late'.  See for yourself with your own data:
DECLARE	@actualtime datetime,
	@arrivedtime datetime,
	@lateduration datetime

SELECT	@actualtime = '20111212 01:00',
	@arrivedtime = '20111212 02:00',
	@lateduration = '19000101 01:00'

SELECT	CASE 
		WHEN @actualtime IS NOT NULL AND @arrivedtime IS NULL THEN 'absense' 
		ELSE  
			CASE 
				WHEN DATEPART(hour, @lateduration) > 0 OR DATEPART(minute, @lateduration) > 0 OR DATEPART(second, @lateduration) > 0 THEN 'late' 
			END
	END attendancestatus

Open in new window

0
 
LVL 1

Author Comment

by:doramail05
ID: 35472481
k, from that it recognise

but when input the data into the sql column manually and execute,

it does not automatically appear 'late' on the attendance status and showing only NULL
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35474796
As I stated then there must be something else going on.  As it stands the code posted is the correct solution to the question posted.  If you would lile to post a complete SQL script that duplicates the problem I will be happy to take a look at it.  By "complete SQL script", I mean:
1. The CREATE statement for the table.
2. The ALTER statement to add the computed column.
3. The INSERT statement(s) to add some data that will show the problem.
0
 
LVL 1

Author Comment

by:doramail05
ID: 35481602
the script is the one you requested,

and i added the table by manual typing in into the sql column and press exclaimation mark.


CREATE TABLE
------------
USE [iDash]
GO

/****** Object:  Table [dbo].[StudentAttendance]    Script Date: 04/28/2011 16:06:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[StudentAttendance](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[attendancestatus] [varchar](10) NULL,
	[lateduration]  AS ([arrivedtime]-[actualtime]),
	[actualtime] [datetime] NULL,
	[arrivedtime] [datetime] NULL,
	[markby] [bigint] NULL,
	[datecreated] [datetime] NULL,
	[datemodified] [datetime] NULL,
 CONSTRAINT [PK_StudentAttendance] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE
-----------
ALTER TABLE StudentAttendance
ADD ['attendancestatus']  AS 
(
CASE WHEN 
actualtime IS NOT NULL  AND arrivedtime IS NULL 
THEN 'absense' 
ELSE  
CASE WHEN DATEPART(hour,lateduration) > 0 OR DATEPART(minute,lateduration) > 0 OR DATEPART(second, lateduration) > 0 THEN 'late' END
END)

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35483283
Unfortunately, what you are proposing is not possible: you cannot create a computed column using a computed column.  If you execute the script you provided you will get the following error message:
Computed column 'lateduration' in table 'StudentAttendance' is not allowed to be used in another computed-column definition.

That is because you are using the computed column "lateduration" in your expression for your new computed column "attendancestatus"

Here is an alternative approach that at least is syntactically correct (fix the obvious typo in xALTER):
xALTER TABLE StudentAttendance
	ADD ['attendancestatus']  AS (
CASE 
    WHEN actualtime IS NOT NULL AND arrivedtime IS NULL THEN 'absense' 
    ELSE  
	CASE 
	    WHEN DATEDIFF(second, arrivedtime, actualtime) > 0 THEN 'late' 
	END
END)

Open in new window

0
 
LVL 1

Accepted Solution

by:
doramail05 earned 0 total points
ID: 35502746
because of the reason that whenever data is enter, the computed column (attendancestatus) hope to change automatically once it detect the conditions if other columns.

else could be using the workaround way by setting attendancestatus in application code via update,,
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35876036
Please post your ALTER TABLE solution.  What you state make no sense.
0
 
LVL 1

Author Closing Comment

by:doramail05
ID: 35906728
use the coding way
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question