Adding Data To Column

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 ar

Solution 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

CREATETABLE Holiday
(
HolidayID INTNOTNULL, --surrogate key, but you can just as easily make HolidayDate the Natural key
HolidayDate  DATENOTNULL
HolidayName NVARCHAR(30),--encase you need non English holiday names saved
...
...
..


INSERTINTO Holiday (HolidayDate,HolidayName) VALUES (2014-12-23, 'Christmas')
INSERTINTO Holiday (HolidayDate,HolidayName) VALUES (2015-12-23, 'Christmas')
INSERTINTO Holiday (HolidayDate,HolidayName) VALUES (2016-12-23, 'Christmas')
INSERTINTO 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)