bicortex

SQL Function to Determine Day Type (Work Day vs. Week Day)

January 25th, 2012 / No Comments » / by admin

Here is a little handy function which I use to either determine day type in some ad hoc query or populate date dimension in the data warehouse via SQL Server Integration Services (on that see the upcoming posts). All I do is pass the date parameter (in a “datetime” data type) to determine if the date is classed as a work day or weekend. It’s a really neat and simple solution as you don’t have to construct the code to determine the field value as part of the larger query; all it takes is just a simple function call. The code behind the solution as well as an execution sample is below:

--------------------------------------------------------------------------------------
--             CREATE A SCALAR-VALUED FUNCTION TO DETERMINE DAY TYPE CODE
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[Day_Type_Code]
(@Date datetime)

RETURNS varchar (10)
AS
    BEGIN
             DECLARE @dtfirst int
             DECLARE @dtweek int
             DECLARE @iswkday varchar(10)

             SET @dtfirst = @@datefirst - 1
             SET @dtweek = DATEPART(weekday,@date)-1

                   IF(@dtfirst + @dtweek)%7 not in (5,6)
                        SET @iswkday = 'WD'
                   ELSE
                        SET @iswkday = 'WE'
    RETURN @iswkday
END

GO

Posted in: SQL

Tags: Code, SQL

Boosting IT Career By Getting Involved In Data Projects

January 18th, 2012 / No Comments » / by admin

According to www.InfoWorld.com, one of the most effective way of boosting your ailing IT career (or simply becoming indispensable as per previous post) is to get involved in data-focused project work. This is, by the way, how most of BI professionals start out and progress to bigger endeavors, including myself. I completely subscribe to Dan’s opinion that BI projects can put you in the spotlight and make your skills the most desirable in the department, however, what I cannot second in this post is the suggestions that cutting corners and providing half-baked, temporary solutions to satisfy your CEO’s monthly report is the way to fast-track your BI career. Too many times have I seen a small, once-off and tactical only Excel report with some back-of-the-napkin calculations that eventually turned into a master data source for all of corporate data. Even worse, after a while (and believe me, it does not take long at all) executives refused to scrap this solution in favor of professional BI solution deployment, justifying spreadsheet ongoing maintenance with the low ownership cost and familiarity with technology. Once you loose the support of business stakeholders and executive strata in the company, it is remarkably hard to get the management sign-off for a multi-million dollar BI project implementation.
Continue Reading This Post

Posted in: This-and-That

Tags: Data, This-and-That