Business Intelligence

What is business Intelligence

I am sure a lot of people can define business intelligence but what we need to know is how it applies to us our business, our life and things we do. Over the last few decades, we have collected data and we probably have overwhelming data about a person, from financial records to personal email, phone records etc. While this information is useful for your own records it provides a vast scope for someone in the field of offering services or goods a great opportunity to find out what they can launch or produce. 

While this is a short example and a survey could do that but have you ever thought of magic. While magical tricks (are tricks) and some are really sophisticated, business intelligence is the magic for this data. Data is any piece entered into a system but when we interpret it, it becomes information. When this goes beyond a regular interpretation, business intelligence comes into play. 

Have you ever thought about this if the person entering next in the car dealership will buy the car or not? Well, if you are into sales there, you know the other brain tricks (that I will talk about in another topic) but you wish you knew so you could spend your time appropriately. Well based on the vast data that we have, a simple survey may be a tool that could give you the answer with pretty high confidence. For example, how many car this person already own, is he / she a house owner? how many children? etc.

Business intelligence is not about what we can see but what we cannot see. With that said, here is my introduction to BI. 

Would love to get some feedback and comments...

Add / Subtracting days from a date to get the next date without counting weekend

Recently had a situation where I had to calculate the next date based on the duration provided to the function. The catch was not to count the weekend (Saturday and Sunday) in the calculation.

I did a lot of google search and landed with  a way to get the next date if the duration was positive (that is the next date will be later than the provided date). I needed a way to go back as well.

After a lot of learning, I came up with this T SQL function for SQL Server that will allow you pass a positive or negative duration parameter and a date. Based on the parameter, it will add or subtract numbers.

Here is the T SQL function. Have fun and enjoy. Feel free to use this, I would really appreciate acknowledgement and link to my blog (http://dmknol.blogspot.com/)

create function [dbo].[GetEndDate_ByWeekdays]
(
 @pdtStartDate datetime,
 @piDuration int
)
returns datetime
as
begin
 declare @rdt datetime
 -- calculating forward
 if (@piDuration > 0)
 begin
   select @rdt = dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end, @pdtStartDate)
   + (datepart (dw, dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end,
    @pdtStartDate)) - 2 + @piDuration) % 5
   + ((datepart (dw, dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end,
    @pdtStartDate)) - 2 + @piDuration) / 5) * 7
   - (datepart (dw, dateadd (d, case datepart (dw, @pdtStartDate) when 7 then 2 when 1 then 1 else 0 end,
   @pdtStartDate)) - 2)
 end

 -- calculating backward
 else
 begin
  -- local variable
  declare @liDuration int,
    @ldt_orgStart datetime,
    @li_orgDuration int
   
  -- save original value
  set @ldt_orgStart = @pdtStartDate
  set @li_orgDuration = @piDuration
  -- divide the duration into set of fives
  while abs(@piDuration)/5 > 0
   begin
    set @piDuration = @piDuration + 5
    --select [@piduration] = @piDuration
    set @liDuration = 7 - 2*(datepart (dw, @pdtStartDate) /7) + (datepart (dw, @pdtStartDate) -2)/7
    set @liduration = @liDuration *(-1)
    --select [@liduration] = @liDuration
    set @pdtStartDate = dateadd(d, @liDuration, @pdtStartDate)
    --select [@pdtStartDate_inside] = @pdtStartDate
   end
  -- add days that were not covered within the five sets
  if (abs(@li_orgDuration) % 5 > 0)
  begin
   --select @piDuration, @pdtStartDate, datepart (dw, @pdtStartDate)
   set @liDuration = case when datepart (dw, @pdtStartDate) <= abs(@piDuration) + 1 then abs(@piDuration) + 2
         when datepart (dw, @pdtStartDate) = 1 then abs(@piDuration) + 1
         else abs(@piDuration) end
   set @liDuration = @liDuration * (-1)
   --select [@liDuration_last] = @liDuration
   set @rdt = dateadd(d, @liDuration, @pdtStartDate)
  end
  else
   set @rdt = @pdtStartDate
 end

 -- return the date
 return @rdt
end
GO

TSQL Averages with Rollup

Here is tsql way of getting averages and also rolling up. Below example is using adventureworks database that is available with SQL server 2008. It is getting average pay rate by each department and also overall by the company.

use [AdventureWorks]

select[Department] = case when grouping(d.GroupName) = 1 then 'Company Average'

                              else d.GroupName end,

            [Average Pay Rate] = avg(p.Rate)

from (select [EmployeeID], [Rate] = max([Rate])

            from [HumanResources].[EmployeePayHistory]

            group by [EmployeeID]) p

      inner join [HumanResources].[EmployeeDepartmentHistory] h

                  on h.[EmployeeID] = p.[EmployeeID] and h.[EndDate] is null

      inner join [HumanResources].[Department] d on d.[DepartmentID] = h.[DepartmentID]

group by d.[GroupName]

with rollup

order by 1

SQL server split received string using XML

Several time, we have a need to split the string based on a delimiter, for instance to handle multiple input filter parameters etc.

 

Most of the time we end up using a user defined function that accepts a string and a delimiter and returns a table of split values. Function may be in-efficient at times.

 

Here is an XML way to split the input string and get a result within a table

 

declare @xml as xml,@str as varchar(100),@delimiter as varchar(10)set @str='A,B,C,D,E'set @delimiter =','set @xml = cast(('<X>' + replace(@str, @delimiter, '</X><X>') + '</X>') as xml)select N.value('.', 'varchar(10)') as value from @xml.nodes('X') as T(N)

SQL Server - Insert in identity column for a table

Sometimes there is a need to insert data into a table that has identity column. Example when you would like to restore a table from a backup including the same IDs so integrity is maintained. Here is simple script

--set identity insert on
set identity_insert <table_name> on

--set identity insert off
set identity_insert <table_name> off