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!

versalytics-logo

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s