Skip to content

Instantly share code, notes, and snippets.

View johnmackintosh's full-sized avatar

John MacKintosh johnmackintosh

199 followers · 153 following
View GitHub Profile
@johnmackintosh
johnmackintosh / basic-row-of-dots-with-floored-time.sql
Last active October 26, 2023 08:40
flooring datetime to 15 minute intervals and using case when
SELECT [MovementDateTime],
[FirstName],
[LastName],
[Ward_Dept],
[Staging_Post],
[Movement_Type],
[IN_OUT],
cast(round(floor(cast([MovementDateTime] AS float(53))*24*4)/(24*4),5) AS smalldatetime) AS Movement15,
(CASE WHEN IN_OUT = 'IN' THEN 1 ELSE -1 END) AS [counter]
FROM [DB].[dbo].[TABLENAME]
@johnmackintosh
johnmackintosh / windowing example.sql
Last active May 31, 2018 23:58
windowing query for row of dots to enable cumulative count
SELECT x.[MovementDateTime],
x.[FirstName],
x.[LastName],
x.[Ward_Dept],
x.[Staging_Post],
x.[Movement_Type],
x.[IN_OUT],
x.[Movement15],
x.[counter],
ROW_NUMBER() OVER (PARTITION BY IN_OUT, Movement_Type,Staging_Post,Movement15 ORDER BY (MovementDateTime))AS R_Number
@johnmackintosh
johnmackintosh / windowing and sequence number.sql
Last active May 31, 2018 23:55
final nested query for row of dots
SELECT y.MovementDateTime,
y.FirstName,
y.LastName,
y.Ward_Dept,
y.Staging_Post,
y.Movement_Type,
y.IN_OUT,
y.Movement15,
y.[counter],
y.[counter] * y.R_Number AS Movement_15_SEQNO
@johnmackintosh
johnmackintosh / row-of-dots-dplyr-transform.R
Created May 31, 2018 23:23
comparing SQL with dplyr
plot_data <- data %>%
mutate(Movement15 = lubridate::floor_date(MovementDateTime,"15 minutes")) %>%
group_by(IN_OUT, Movement_Type,Staging_Post,Movement15) %>%
mutate(counter = case_when(
IN_OUT == 'IN' ~ 1,
IN_OUT == 'OUT' ~ -1)) %>%
mutate(Movement_15_SEQNO = cumsum(counter)) %>%
ungroup()
@johnmackintosh
johnmackintosh / succinct.sql
Created June 3, 2018 21:40
more succint version of sql example from row of dots post
SELECT * ,
ROW_NUMBER() OVER (PARTITION BY IN_OUT, Movement_Type,Staging_Post,Movement15 ORDER BY (MovementDateTime)) * [counter] AS Movement_15_SEQNO
FROM (
SELECT [MovementDateTime],
[FirstName],
[LastName],
[Ward_Dept],
[Staging_Post],
[Movement_Type],
[IN_OUT],
@johnmackintosh
johnmackintosh / succinct row of dots dplyr.r
Created June 3, 2018 21:50
even shorter version of row of dots dplyr
plot_data <- data %>%
mutate(Movement15 = lubridate::floor_date(MovementDateTime,"15 minutes")) %>%
group_by(IN_OUT, Movement_Type,Staging_Post,Movement15) %>%
mutate(counter = if_else(IN_OUT == 'IN',1,-1),
Movement_15_SEQNO = cumsum(counter)) %>%
ungroup()
@johnmackintosh
johnmackintosh / midtable-predictions.R
Created August 11, 2018 23:15
obtainin combinations of possible football results
#Setup current fixtures and points per outcome
library(dplyr)
library(purrr)
library(tidyr)
library(ggplot2)
library(ggalt)
library(ggrepel)
library(ggExtra)
library(stringr)
@johnmackintosh
johnmackintosh / keybase.md
Created February 21, 2019 21:16
Keybase verification

Keybase proof

I hereby claim:

  • I am johnmackintosh on github.
  • I am johnmackintosh (https://keybase.io/johnmackintosh) on keybase.
  • I have a public key ASDQIp7YliqUjDZBfWl37te22cunTdTKJ2nKeYaEyF9Wdgo

To claim this, I am signing this object:

@johnmackintosh
johnmackintosh / nhsr_datasets.R
Created August 15, 2019 22:56
trial run with NHSRdatasets package and runcharter
library(NHSRdatasets) # install from github for latest dataset
library(runcharter)
library(data.table)
library(magrittr)
# Load
data("ae_attendances")
DT <- setDT(ae_attendances)
@johnmackintosh
johnmackintosh / nhsr_conference_2019_links.md
Last active November 5, 2019 13:19
links to stuff highlighted on twitter at the NHSR Conference 2019