December 17, 2016

SQL pivot


/* Dynamically pivot rows into columns  */

DECLARE @cols varchar(max)
,@query varchar(max)

SELECT @cols = COALESCE(@cols + ',[' , '') +convert(varchar(30),[loan date],101) + ']' FROM #EOMdates ORDER BY [SortID]
SET @cols = '[' + @cols 

SET @query = 'SELECT [status], ' + @cols + ' INTO ##pivoted FROM 
(
SELECT [Loan Date]
, [count] = isnull(loans,0)
, [status]
FROM #buckets
) data
PIVOT
(
Sum([count])
FOR [Loan Date] IN (' + @cols + ' )
) AS PivotTable'
EXECUTE(@query)


May 22, 2016

T-SQL: get the previous business day

Use the T-SQL DateName function to get the previous business day in the week:

SELECT  DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE()) 
                        WHEN 'Sunday' THEN -2 
                        WHEN 'Monday' THEN -3 
                        ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))

April 16, 2016

Linear Count

/* Increment and dynamically generate a list of integers for use with the Predicate */

PRINT '(' + '''' + convert(varchar(10),@StartNumeral) + ',' + ''''

WHILE @StartNumeral < @EndNumeral

BEGIN
SELECT @StartNumeral = @StartNumeral + 1
PRINT '''' + cast(@StartNumeral AS varchar(6)) + '''' + ',' 
END

PRINT '''' + convert(varchar(10),@StartNumeral + '''' + ')' 

March 2, 2016

Find column in database

This script finds all tables with a specific column in the database:

SELECT t.name AS table_name,
SCHEMA_NAME(schema_idAS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c 
ON t.OBJECT_ID c.OBJECT_ID
WHERE c.name LIKE '%ClientID%'
ORDER BY schema_nametable_name;

March 1, 2016

CTE and Pivot

/* Pivot to display queues horizontally */

WITH Validation AS
(
select top 100 percent
UserName, QueueName, EndDate, Count(DocumentID) AS 'QueueCount'
From ValidationGroup
Group by UserName, QueueName, EndDate
Order by UserName, QueueName
)
select * from (
select top 100 percent
UserName, QueueName, EndDate, isnull(QueueCount,0) AS 'QueueCount'
From ValidationGroup
Group by UserName, QueueName, EndDate, QueueCount
Order by UserName
) src
pivot
(
Sum(QueueCount)
for QueueName in ([Incoming,Validation,Appeal,Pending)
) p;


February 27, 2016

dynamic selection

/* select the first 10 rows from each table in a database */

EXEC sp_MSforeachtable 'SELECT ''?'' as tableName, * from ?'

February 14, 2016

Recommendation systems


Web recommendation systems attempt to predict rating behavior using various classes of algorithms:

Content-based algorithms use product characteristics to recommend other items with similar characteristics.

Collaborative filtering algorithms evaluate past behavior and similar product choices made by other users.

Other algorithms build a model from a user's past behavior such as items previously purchased or selected and/or numerical ratings given to those items as well as similar decisions made by other users. 

Popular Apps like Amazon, Netflix and Apple Music seem to incorporate a blend of both systems which translates to a better user experience. 

A purely collaborative method seems frequently off the mark for me. These algorithms seem to incorporate demographic profiling, ageism, gender, and ethnicity factors that skew the product choice analytics resulting in stereotypical recommendations. 

January 10, 2016

Timezones

-- Timezone wrapper

Create function dbo.fn_TimeZones
(@StartDateIn datetime
,@EndDateIn  datetime
,@State            varchar(10)
)
returns numeric(17,2) as
/*
Timezone tables are updated with data for many decades so they will have to be undated once a century and are maintained by the Human Resources Department:
dbo.TimeZoneStates
dbo.TimeZoneOffset

(1) The day of the act, event, or default from which the designated time period begins to run is not included.
(2) If the last day of the designated time period is a Saturday, Sunday, Federal legal holiday (as enumerated in Rule 6(1) of the Federal Rules of Civil Procedure), or a day on which the reviewing entity is unable to conduct business in the usual manner, the deadline becomes the next day that is not one of the aforementioned days.

1) The start date is always the received date.
2) The start date is a weekday. Start counting on the immediate next week day.
3) When start counting, count 10 hours for each contiguous full business day.

--QA  (copy and paste into query window)
declare
@StartDate datetime
,@EndDate  datetime
)
-- define the business day parameters
set @StartDate = '2015-1-18  14:00:00.000'
set @EndDate = '2015-1-20   23:00:00.000'
*/
BEGIN
Declare
@StartDate datetime
,@EndDate datetime
-- Timezone offset parameters
,@DTbegin  datetime
,@DTend     datetime
,@STbegin   datetime
,@STend      datetime
,@ESToffset float
,@EDToffset float
,@TimeZoneST varchar(100)  --- make it big in case we expand to other timezones like Hawaiin Aleusian time
,@TimeZoneDT varchar(100)
,@BusDays     numeric(17,2)

SELECT @DTbegin
,@DTend  
,@STbegin
,@STend    
,@ESToffset
,@EDToffset
@TimeZoneST
@TimeZoneDT
FROM dbo.TimeZoneStates
WHERE [year] = datepart(year,@StartDateIn)
and [State] = @State

/* Timezone offset algorithms */
-- if the start date falls during daylight savings time use that offset
select @StartDateIn BETWEEN @DTbegin and @DTend
BEGIN
select @StartDate = DateADD(Hour,@EDToffset, @StartDateIn)
END

-- if the start date falls during standard time use that offset
select @StartDateIn BETWEEN @STbegin and @STend
BEGIN
select @StartDate = DateADD(Hour,@ESToffset, @StartDateIn)
END

-- if the end date falls during daylight savings time use that offset
select @EndDateIn BETWEEN @DTbegin and @DTend
BEGIN
select @EndDate = DateADD(Hour,@EDToffset, @EndDateIn)
END

-- if the end date falls during standard time use that offset
select @EndDateIn BETWEEN @STbegin and @STend
BEGIN
select @EndDate = DateADD(Hour,@ESToffset, @EndDateIn)
END

--<  Business hours/days are custom by company. Place your algorithms here. For example:

-- if StartDate falls before the first business hour, set it to the start hour of that day
select @StartDate = CASE

-- if StartDate falls before the last business hour, set it to the start hour of the next day
WHEN datepart(hour,@StartDate) <= @StartHour
THEN dateadd(hour,@StartHour,dateadd(day,1,convert(char(8),@StartDate,112)))

-- if StartDate falls after the last business hour, set it to the start hour of the next day
WHEN datepart(hour,@StartDate) >= @EndHour
THEN dateadd(hour,@StartHour,dateadd(day,2,convert(char(8),@StartDate,112)))

-- if StartDate falls within the 10 business hour, set it to the start hour of the next day
WHEN datepart(hour,@StartDate) <= @EndHour
THEN dateadd(hour,@StartHour,dateadd(day,1,convert(char(8),@StartDate,112)))

ELSE dateadd(hour,@StartDate,dateadd(day,1,convert(char(8),@StartDate,112)))
END
END
END
-- end of sample algorithms>

return @BusDays 
END