sbealer’s gists

sbealer / gist:8566395

Last active January 4, 2016 04:09

Using Query function in Google Spreadsheets. In this case, using a cell value for the condition. In this example you'll have a sheet named 2014_Q3. You're looking for a value where A = a cell in the current sheet (notice the &'s - Ampersands)

=query('2014_Q3'!$A$2:$B$8; "select B where A ='"&A2&"' ")

sbealer / gist:8739130

Created January 31, 2014 18:20

Advanced Google Query to pivot values

	say you have a spreadsheet formatted thusly (for instance a survey form):

	timestamp, user, question
	'2013-01-01', person1, Y
	'2013-01-01', person2, Y
	'2013-01-01', person3, N

	and you want to get a count, by day, of the responses like:

	Date, Y, N

sbealer / gist:f876cffdc2b50f976258

Last active August 29, 2015 14:10

SQL Server - Get local datetime from utc datetime

	-- the utc value
	declare @utc datetime = '20/11/2014 05:14'

	-- the local time

	select DATEADD(hh, DATEDIFF(hh, getutcdate(), getdate()), @utc)


	--Conversely (to go from local to UTC)
	DECLARE @LOCAL DATETIME = getdate();

sbealer / get max row from multiple columns in same row using VALUES clause.sql

Last active January 19, 2016 18:30

Get max row from multiple columns using VALUES clause

	/* This method has the best performance over other methods normally used (union all, etc)*/

	SELECT TOP 50
	INV.PONUMBER
	,INV.KEYINDATE
	,INV.PAIDDATE
	,INV.DATE
	,
	(SELECT
	MAX(LASTUPDATEDATE)

sbealer / HOUR_OFFSET_FUNCTION.sql

Last active November 18, 2015 22:39

Function to calculate DST offset hour for given datetime

	ALTER FUNCTION [dbo].[UTC_TO_LOCAL_TIME_W_DST_ADJUST] (@DTTM DATETIME2 -- In UTC
	)
	RETURNS DATETIME2
	AS
	BEGIN
	--DECLARE @DTTM AS DATETIME2 = '01-Mar-2015 09:01:00';
	DECLARE @LOCAL AS DATETIME2;
	DECLARE @DT_YEAR AS VARCHAR(4) = DATEPART(YEAR,@dttm);
	DECLARE @STARTDLS AS DATETIME2 = '01-Mar-' + @DT_YEAR;
	DECLARE @STOPDLS AS DATETIME2 = '01-Nov-' + @DT_YEAR;

sbealer / python_multiprocessing_example.py

Last active December 12, 2022 22:48

Python Multiprocessing/Parallel/Multi Example

	import os
	import multiprocessing
	import random
	import time
	from services import logger

	LOG = logger.get_logger(os.path.basename(__file__))

	proc_name = 'Parallel Processing Example using multiprocessing module'

sbealer / redshift_dim_date.sql

Last active November 11, 2022 20:32

Redshift Date Dimension SQL

	create
	--drop
	view dw.dim_date_vw as

	WITH nums AS (
	SELECT TOP 15000 row_number() over (
	PARTITION BY NULL order by id) n
	FROM l_browser -- or some other large table;
	)

sbealer / redshift_dim_time.sql

Created November 1, 2016 20:43

Redshift Time Dimension SQL

	CREATE VIEW dw.dim_time_vw AS
	with nums AS (
	SELECT TOP 86400
	row_number() over (
	PARTITION BY NULL ORDER BY id) AS num
	FROM l_browser)

	SELECT
	to_char(
	DATEADD(second, num-1, cast('2000-01-01' AS date)), 'HH24MISS') AS time_key,

sbealer / FIRST_VALUE vs. LAST_VALUE...WHICH ONE DO I USE?!?

Last active June 29, 2017 17:20

first_value_vs_last_value_example

	/*Just use FIRST_VALUE. LAST_VALUE takes more coding (in SQL Server) to get the same thing done, such as the inclusion of a ROWS BETWEEN clause:

	(Please note that this article applies to SQL Server. Redshift requires you to have a window function regardless of which method you use)

	The below query tries to get the 'most recent' value without having to join back on itself.
	Two methods, last_value and first_value are used in the attempt:
	*/

	WITH DATES_EXAMPLE
	AS

sbealer / import csv into SQL Server no format file.sql

Last active May 18, 2017 21:48

Import csv into SQL Server without need of format file

	INSERT INTO [YOURLINKEDSERVER].[YOURDB].[DBO].YOURTABLE
	SELECT
	*
	,cast('2017-05-20' as date)
	FROM OPENROWSET('MSDASQL'
	, 'Driver={Microsoft Access Text Driver (.txt, .csv)};
	DBQ=C:\first_directory\second_directory\'
	, 'select * from "your_file.csv"')

Stephen Bealer sbealer