Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to caluculate half day Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 5:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 12:05 AM
Points: 99, Visits: 304
hi

i want to caluclate half day

my actual data is like this

date-----------------------------eid---------------timein---------------------------timeout-----------spend-----excessshort------excess
2013-03-21 00:00:00.000---26446---2013-06-13 09:13:00.000--2013-06-13 3:46:00.000----06:33:00---- 02:27:00-------Short




i want this data


date-----------------------------eid---------------timein---------------------timeout-----------spend-----excessshort-excess----Remarks
2013-03-21 00:00:00.000--26446--2013-06-13 09:13:00.000--2013-06-13 3:46:00.000-06:33:00--02:27:00---Short------HALFDAY




employee timing is 9am to 6pm if he leaves from factory at 4pm or before 4 pm then remarks say halfday

becouse we have 2 hours half day mean if he go for some work and didnt come back for 2 hours our more then 2 hours then half day implemented

please implement on this query

drop table #temp1
select
[date],
min([Timein]) as First_Record,
sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
into #temp1 from attend_LOG
where eid = 17090
group by [date]
GO
select
t.[date],
t.eid,
t.[Timein] as timein,
t.[Timeout] as timeout,
CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,

case when (540 - Time_Minutes) > 0 Then '- ' else '+ ' end
+CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime

FROM attend_LOG t
left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
where eid = 17090
order by t.[date], t.[Timein]



please help me out
thanks for the help
Post #1462994
Posted Thursday, June 13, 2013 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1463079
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse