Adding data to column

Question

i have written this query to show total working time and overtime of an employee on particular date (if he has came). i want if for a particular date person's INtime and OutTime are empty then put 00:00 in his intime, outtime, totalworkingtime, overtime e.g. it's sunday so obviously no InTime and OutTime then it should show 00:00 in time columns. Note: dates are only entered if person InTIme is mentioned otherwise no DateVisited.

e.g.

EmplID  EmplName ShiftID intime Outtime totalworking overtime  dateVisited
0000001 John     S001    00:00  00:00   00:00:       00:00     2013-12-01

Query:

with times as (

SELECT    t1.EmplID

        , t3.EmplName

        , min(t1.RecTime) AS InTime

        , max(t2.RecTime) AS [TimeOut]

        , t4.ShiftId as ShiftID

        , t4.StAtdTime as ShStartTime

        , t4.EndAtdTime as ShEndTime

        , cast(min(t1.RecTime) as datetime) AS InTimeSub

        , cast(max(t2.RecTime) as datetime) AS TimeOutSub

        , t1.RecDate AS [DateVisited]

FROM  AtdRecord t1 

INNER JOIN 

      AtdRecord t2 

ON    t1.EmplID = t2.EmplID 

AND   t1.RecDate = t2.RecDate

AND   t1.RecTime < t2.RecTime

inner join 

      HrEmployee t3 

ON    t3.EmplID = t1.EmplID 

inner join AtdShiftSect t4

ON t3.ShiftId = t4.ShiftId

group by 

          t1.EmplID

        , t3.EmplName

        , t1.RecDate

        , t4.ShiftId 

        , t4.StAtdTime 

        , t4.EndAtdTime

)
SELECT 

 EmplID

,EmplName
,ShiftId As ShiftID

,InTime
,[TimeOut]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) TotalWorkingTime

,[DateVisited]
,CASE WHEN [InTime] IS NOT NULL AND [TimeOut] IS NOT NULL THEN

     CONVERT(char(5),CASE WHEN  CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S002' Then  LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME),CAST([TimeOutSub] AS DATETIME)),0), 108),5) 

                          WHEN  CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S001' Then  LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME),  CAST([TimeOutSub] AS DATETIME)),0), 108),5) 

      ELSE '00:00' END, 108) 

 ELSE 'ABSENT' END AS OverTime

FROM times  order by EmplID, ShiftID, DateVisited

You've got a CASE statement already, what's the problem here? — Hart CO
– Hart CO, Commented Feb 5, 2014 at 16:57
Problem is that for holidays i should mention 00:00 in time sections — PreciseTech
– PreciseTech, Commented Feb 5, 2014 at 16:58
Sorry, maybe I'm just needing more coffee. How do we know that it's a holiday? — tommy_o
– tommy_o, Commented Feb 5, 2014 at 18:39
sir there must be any way to get around it ? i have wasted several hours trying it but couldn't please help — PreciseTech
– PreciseTech, Commented Feb 5, 2014 at 19:12

gh9 · Accepted Answer · 2014-02-06 14:35:20Z

1

If your problem solely relies on knowing when a date is a workday/holiday/weekend I would recommend using a DataWarehouse solution. Create A table that contains all the holidays/weekends and check against it.

Depending on what Calendar and what other dates you are using there are many scripts out there to create it for you, but the simplest way would be for the next 10 years or so calculate when all your holidays land on. for example Christmas is always on December 23 and thanksgiving is always 3 week in November I would create a table like this

CREATE TABLE Holiday
(
HolidayID INT NOT NULL, --surrogate key, but you can just as easily make HolidayDate the Natural key
HolidayDate  DATE NOT NULL
HolidayName NVARCHAR(30),--encase you need non English holiday names saved
...
...
..


INSERT INTO Holiday (HolidayDate,HolidayName) VALUES (2014-12-23, 'Christmas')
INSERT INTO Holiday (HolidayDate,HolidayName) VALUES (2015-12-23, 'Christmas')
INSERT INTO Holiday (HolidayDate,HolidayName) VALUES (2016-12-23, 'Christmas')
INSERT INTO Holiday (HolidayDate,HolidayName) VALUES (2017-12-23, 'Christmas')
.....
.....
....

Or if you want to get even more indepth you can map out ever day for the next 10 years in a table (Date Dimension table). Then mark those days as holidays, work cancelled, sales quarters and what not. (google Date Dimension Table)

edited Feb 6, 2014 at 14:35

answered Feb 5, 2014 at 21:28

gh9

10.8k12 gold badges65 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PreciseTech Over a year ago

how sir i couldn't understand this

gh9 Over a year ago

What is confusing you, when can go over it step by step if you would like

Collectives™ on Stack Overflow

Adding data to column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related