CODEWITHSUNDEEP
c#sqlsql-server
VAAA
VAAA

Reputation: 15049

SQL Best way to store the day and week or weekend day (time and attendance)

I have a sql table where Im going to store every employee time record (IN and OUT).

The table schema is:

enter image description here

So, in order to make easier the calculation for working hours, vacations, holidays, etc.. I would like also to include into the same table a new field:

But also want to know the day (monday, tuesday, etc.) I was thinking on a tinyint datatype from 1 to 7 where 1 is Monday and 7 is Sunday.

Any advice or recomendation? In C# or SQL does Monday equals to first day of week?

Upvotes: 0

Views: 836

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Adding the isholiday flag is quite useful. The alternative is to look up holidays in a table, but having the data directly in the record is easy. And, because you already have bit flags, one more flag will probably not make the field any larger.

Identifying the day of week or weekends is less useful. SQL Server already provides functions to do this, notably datepart() and datename().

There are some circumstances where these can be difficult to use. For instance, if you have to write code that runs on any system, regardless of language and any local preferences for when the week starts, then storing the information in the record could be useful. That is, datepart() and datename() are not deterministic, because they depend on system parameters. However, for the majority of applications that are written for one culture, this is not much of an issue.

Also, under some circumstances that I could imagine, the overhead to call a system function might be larger than the time to read a value from a record. In a heavily CPU constrained environment, you might want to store the flags. However, I suspect that there would be other more important optimizations.

Upvotes: 1

Related Questions