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

March 31, 2013

Findstr - a windows equivalent to Unix grep

Findstr - a Windows string parser similar to grep 

Findstr 

findstr [/b] [/e] [/l | /r] [/s] [/i] [/x] [/v] [/n] [/m] [/o] [/p] [/f:<File>] [/c:<String>] [/g:<File>] [/d:<DirList>] [/a:<ColorAttribute>] [/off[line]] <Strings> [<Drive>:][<Path>]<FileName>[ ...] 

Parameters
/b         Matches pattern if at the beginning of a line.
/e         Matches pattern if at the end of a line.
/l         Uses search strings literally.
/r         Uses search strings as regular expressions.
/s         Searches for matching files in the current directory and all subdirectories.
/i         Specifies that the search is not to be case-sensitive.
/x         Prints lines that match exactly.
/v         Prints only lines that do not contain a match.
/n         Prints the line number before each line that matches.
/m         Prints only the filename if a file contains a match.
/o         Prints character offset before each matching line.
/p         Skip files with non-printable characters.
/Off[line] Do not skip files with offline attribute set.
/a:attr    Specifies color attribute with two hex digits. See “color /?”
/f:file    Reads file list from the specified file(/ stands for console).
/c:string  Uses specified string as a literal search string.
/g:file    Gets search strings from the specified file(/ stands for console).
/d:dir     Search a semicolon delimited list of directories

strings    Text to be searched for.

[drive:][path]filename Specifies a file or files to search.


Regular expression metacharacters that can be used with findstr:


.
Wildcard: any character
*
Repeat: zero or more occurrences of the previous character or class
^
Line position: beginning of the line
$
Line position: end of the line
[class]
Character class: any one character in a set
[^class]
Inverse class: any one character not in a set
[x-y]
Range: any characters within the specified range
\x
Escape: literal use of a metacharacter x
\<string
Word position: beginning of the word
string\>
Word position: end of the word



examples

To search for "smith" in the file named myfile.txt 
findstr smith myfile.txt

To search for "John Smith" in the file myfile.txt
findstr /c: "John Smith" myfile.txt


November 11, 2012

SSRS: Formatting Numbers and Dates


Use the following subset of Visual Basic format strings to format numbers and dates in SSRS data regions using the Properties dialog box. 

SSRS Numeric formats:

Format

         Value

 Formatted

N0

         10000.00

10,000

N1

         10000.00
10,000.0 

C
         
         10000.00

$10,000.00

D2
         
         10000.00

10000

P1
        
         10000.00

1,000,000.0%

E
        
         10000.00                                        

1.000000E+004

SSRS Date and Time formats:

Format

Value

Formatted

d                

11/11/2012 11:30.000 AM

11/11/2012 11:30:00 AM

D

11/11/2012 11:30.000 AM

Sunday, November 11, 2012

t

11/11/2012 11:30.000 AM                 

11:30 AM

T

11/11/2012 11:30.000 AM

11:30:00 AM

f

11/11/2012 11:30.000 AM

Sunday, November 11, 2012 11:30:00 AM

F

11/11/2012 11:30.000 AM

Sunday, November 11, 2012 11:30 AM

g

11/11/2012 11:30.000 AM

11/11/2012 11:30 AM

G

11/11/2012 11:30.000 AM

11/11/2012 11:30:00 AM

M or m

11/11/2012 11:30.000 AM

November 11

Y or y

11/11/2012 11:30.000 AM

November, 2012

Yyyy      

11/11/2012 11:30.000 AM

2012