SQL – Shift and Break Tables

A Cynic is a man who knows the price of everything and the value of nothing

~ Oscar Wilde

Dealing with DATES and TIMES programmatically can be a chore at the best of times. Today’s task of setting up shift and break tables is finally completed.  Setting up the initial tables is a relatively simple task using Microsoft’s SQL Server Management Studio.

The real challenge is calculating the net hours worked over a given period of time.  The process is further complicated by periods of time that span across the midnight hour into the next day.

Parsing Time

Three parameters will be passed to our “ParseTime” SQL procedure:  Start Time, Finish Time, and a “Breaks ON or OFF” flag.  The procedure returns the net hours worked.

Internally, the function creates two tables, ShiftTimes and BreakTimes respectively, from which the shifts worked, break times, and net hours worked are determined.

We’ll break the process down in more detail in our next post.

Until Next Time – Crunch Data!



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.