Two Conditions for one Field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Two Conditions for one Field

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2011-06-08 : 01:03:34

basically i Am Working On Attendance System
My Dataa Is Like This

(Date) (TIME In) (Time Out) (Spend) (Late Time) (HalfDay Time)

(Oct 19 2010) (8:46AM) (6:35PM) (09:48:48) (1899-12-30 09:10:00.000) (1899-12-30 11:30:00.000)
(Oct 19 2010) (9:12AM) (6:01PM) (09:08:59) (1899-12-30 09:10:00.000) (1899-12-30 11:30:00.000)
(Oct 19 2010) (11:32AM) (7:24PM) (10:29:29) (1899-12-30 09:10:00.000) (1899-12-30 11:30:00.000)

i want To Add Remarks Column for Remarks

I want Data Like This

(Date) (TIME In) (Time Out) (Spend) (Late Time) (HalfDay Time) (Remarks)

(Oct 19 2010) (8:46AM) (6:35PM) (09:48:48) (1899-12-30 09:10:00.000) (1899-12-30 11:30:00.000) (NUll)
(Oct 19 2010) (9:12AM) (6:01PM) (09:08:59) (1899-12-30 09:10:00.000) (1899-12-30 11:30:00.000)(LATE)
(Oct 19 2010) (11:32AM) (7:24PM) (10:29:29) (1899-12-30 09:10:00.000) (1899-12-30 11:30:00.000) (HALFDAY)

i have Tried This But Not Showing Correct Data

SELECT [Date], TimeIn, TimeOut, Spend,HalfDayTime,LateTime, (CASE WHEN ltrim(RIGHT(TimeIn, 8)) > ltrim(RIGHT(LateTime, 8)) THEN 'LATE' WHEN ltrim(RIGHT(TimeIn, 8)) > ltrim(RIGHT(HalfDayTime, 8)) THEN 'HalfDay' END)
AS Remarks
FROM TableName

Kindly Help
Thanks In Advance

lappin
Posting Yak Master

182 Posts

Posted - 2011-06-10 : 07:25:36

Select HalfDay as HD,LateTime as L,Timein as TI,
CASE

WHEN (datePart(HH,convert (datetime,Timein)) > datePart(HH, Late)
AND datePart(HH,convert (datetime,Timein))< datePart(HH, HalfDay)) THEN 'LATE'

WHEN (datePart(HH,convert (datetime,Timein)) > datePart(HH, Late)
AND datePart(HH,convert (datetime,Timein))= datePart(HH, HalfDay))
AND datePart(minute,convert (datetime,Timein))< datePart(minute, HalfDay) THEN 'LATE'

WHEN (datePart(HH,convert (datetime,Timein)) = datePart(HH, Late)
AND datePart(Minute,convert (datetime,Timein)) > datePart(Minute, Late)
AND datePart(HH,convert (datetime,Timein))< datePart(HH, HalfDay)) THEN 'LATE'

WHEN (datePart(HH,convert (datetime,Timein)) = datePart(HH, Late)
AND datePart(Minute,convert (datetime,Timein)) > datePart(Minute, Late)
AND datePart(HH,convert (datetime,Timein))= datePart(HH, HalfDay))
AND datePart(Minute,convert (datetime,Timein))< datePart(Minute, HalfDay) THEN 'LATE'

WHEN (datePart(HH,convert (datetime,Timein)) > datePart(HH,HalfDay)) THEN 'HalfDay'

WHEN (datePart(HH,convert (datetime,Timein)) = datePart(HH,HalfDay))
AND datePart(Minute,convert (datetime,Timein))> datePart(Minute, HalfDay)
THEN 'HalfDay'

else 'OnTime'
END

AS Remarks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-12 : 04:27:19

see similar logic here

http://visakhm.blogspot.com/2010/03/calculating-business-hours.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources