carpii’s gists

carpii / gist:6236099

Created August 14, 2013 22:00

Create and populate 'finance' table, which represents the resultset after your original query has run This is the same data you gave me, except I only need the amount and type fields (you'd also include the date field, but I had to just use dummy data for that)

	CREATE TABLE [finance] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[amount] [decimal](10, 4) NULL ,
	[type] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
	) ON [PRIMARY]
	GO

	insert into finance (amount, type) values (0.0000,'PRINCIPAL');
	insert into finance (amount, type) values (150.0000,'PRINCIPAL');
	insert into finance (amount, type) values (100.0000,'PRINCIPAL');

carpii / gist:6236105

Created August 14, 2013 22:01

Query to loop over finance data, populating the memory table with running totals of principal, compound interest etc

	SET NOCOUNT ON;

	-- declare memory table
	DECLARE @totals TABLE (id int not null, effectiveDate datetime, daysSinceLastPayment int, type varchar(20), amount decimal(12,5), totalPrincipal decimal(12,5), totalFees decimal(12,5), totalPayments decimal(12,5), totalInterest decimal(12,5), balance decimal(12, 5))

	insert into @totals (id, amount, type) select id, amount, type from finance

	-- populate with some pretend dates
	update @totals set effectiveDate = DATEADD(DD, -100+id, DATEADD(MM, -6, GETDATE()));

carpii / gist:6236117

Created August 14, 2013 22:03

Example result set from your data I haven't tested any of the results heavily, even the compound interest, but it will be trivial to fix if it is wrong

	id effectiveDate daysSinceLastPayment type amount totalPrincipal totalFees totalPayments totalInterest balance
	----------- ------------------------------------------------------ -------------------- -------------------- -------------- -------------- -------------- -------------- -------------- --------------
	1 2012-11-07 22:58:15.000 NULL PRINCIPAL .00000 .00000 .00000 .00000 .00000 .00000
	2 2012-11-08 22:58:15.000 NULL PRINCIPAL 150.00000 150.00000 .00000 .00000 .00000 150.00000
	3 2012-11-09 22:58:15.000 NULL PRINCIPAL 100.00000 250.00000 .00000 .00000 .00000 250.00000
	4 2012-11-10 22:58:15.000

carpii / calculate blocks of free time

Created December 6, 2013 16:20

	drop table temp_times;
	create table temp_times (id int, user_id int, start_time datetime, end_time datetime);
	insert into temp_times (id, user_id, start_time, end_time) values (1, 1, '2013-01-01 13:45', '2013-01-01 15:50');
	insert into temp_times (id, user_id, start_time, end_time) values (2, 1, '2013-01-01 10:00', '2013-01-01 13:20');
	insert into temp_times (id, user_id, start_time, end_time) values (3, 1, '2013-01-01 15:00', '2013-01-01 16:02');
	insert into temp_times (id, user_id, start_time, end_time) values (4, 1, '2013-01-01 08:10', '2013-01-01 09:10');
	insert into temp_times (id, user_id, start_time, end_time) values (4, 1, '2013-01-02 09:10', '2013-01-01 11:55');

	insert into temp_times (id, user_id, start_time, end_time) values (1, 2, '2013-01-01 13:45', '2013-01-01 15:50');
	insert into temp_times (id, user_id, start_time, end_time) values (2, 2, '2013-01-01 10:00', '2013-01-01 13:20');

carpii / disable_ram_warning

Created November 20, 2021 20:57

	#!/bin/sh

	# Disables invalid memory notifications on Synology NAS (including the notifications in DiskStation itself)
	# these warnings occur when you use perfectly compatible RAM, which is not Synology branded
	#
	# Modify `MANUFACTURER` and `DBFILE` to match your environment (DBFILE will be the filename of the .db in DBPATH)
	# Use Synology Task Scheduler to run it on boot, as root user
	# Finally run `sudo ~/disable_ram_warning'

	# This script is safe to run repeatedly, and will also work if Synology update DiskStation later, and make additions to the db file

carpii / watch_1password

Created April 20, 2022 20:37

log 1password filesystem events, to troubleshoot it opening more than one instance

	#!/bin/sh
	USER=`whoami`
	LOGFILE="/home/${USER}/1password.log"
	D=`date +"%Y/%m/%d %H:%M:%S"`
	echo "=======================================" >> ${LOGFILE}
	echo "${D} - LAUNCHING WATCHER" >> ${LOGFILE}

	inotifywait -m -e close_write,moved_to,create,delete,modify --format '%:e %f' --excludei "chromium\|Network changed\|sqlite" ~/.config/1Password \| while read -r filename; do
	REGEXP='(Singleton[^ ]*)'
	D=`date +"%Y/%m/%d %H:%M:%S"`