Skip to content

Instantly share code, notes, and snippets.

View sbealer's full-sized avatar

Stephen Bealer sbealer

1 follower · 8 following
  • Lulus.com
View GitHub Profile
@sbealer
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
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
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
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
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
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
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
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
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
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"')