;WITH RecentVotes AS
(
	SELECT p.PostID, v.VoteID,
	RN = ROW_NUMBER() OVER (PARTITION BY p.PostID ORDER BY v.VoteDateTime DESC)
	FROM Posts P
	JOIN Votes V
	 on P.PostID = V.PostID
)

SELECT SUM( (case when upvote then 1 else -1 end)
                   / (1+datediff(month, v.VotedateTime, getdate())))
          / count(votes)
FROM ... ;

/**
 * Find Server & DB location, basic client info and transaction information where processing has encountered key reference issues.
 *
 * Note: Connect to server PBISQL72I1\IFR to run this.
 *
 * Manual Check #1
 * Run `03 - Telehealth Mark Status As Error.sql` if...
 *   - result of query `02 - TeleHealth GetPatient ID.sql` based on `Patient.[THVSPI_PATIENTNAMELAST]` is null,
 *   update Processed to 'E'.
 * or...
 *   - `Patient.[THVSPI_INSERTDATE]` from this query is between `epi_StartOfEpisode` and `epi_EndOfEpisode`

CREATE LOGIN hashtest WITH PASSWORD = 'aaaaaaaaaa', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF, DEFAULT_LANGUAGE = us_english;

SELECT COALESCE(UPPER([sys].[fn_varbintohexstr](CONVERT(VARBINARY(MAX), LOGINPROPERTY(sp.name, 'PasswordHash')))), '')
FROM sys.server_principals sp
WHERE sp.name = 'hashtest';

CREATE LOGIN hashtest WITH PASSWORD = 'aaaaaaaaaa', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF, DEFAULT_LANGUAGE = us_english;

SELECT COALESCE(UPPER([sys].[fn_varbintohexstr](CONVERT(VARBINARY(MAX), LOGINPROPERTY(sp.name, 'PasswordHash')))), '')
FROM sys.server_principals sp
WHERE sp.name LIKE 'hashtest%';

DROP LOGIN hashtest;

CREATE LOGIN hashtest WITH PASSWORD = 'aaaaaaaaaa', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF, DEFAULT_LANGUAGE = us_english;

SELECT COALESCE(UPPER([sys].[fn_varbintohexstr](CONVERT(VARBINARY(MAX), LOGINPROPERTY(sp.name, 'PasswordHash')))), '')

CREATE TABLE dbo.Pwds
(
	Pwd VARCHAR(128)
);
INSERT INTO dbo.Pwds (Pwd)
VALUES ('aaaaaaaaaa');

SELECT sl.name
	, p.Pwd
FROM sys.syslogins sl
	, dbo.Pwds p
WHERE 0x0100 + CONVERT(VARBINARY(4), SUBSTRING(CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY(MAX), sl.password, 0)), 2, 2))
	+ HASHBYTES('SHA1', p.Pwd + SUBSTRING(CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY(MAX), sl.password, 0)), 2, 2))
	= CONVERT(VARBINARY(MAX), sl.password, 0);