-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE members (
    id INTEGER NOT NULL,
    name VARCHAR(50) NOT NULL,
    start DATE NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO members VALUES(1,'A A','20181009');
INSERT INTO members VALUES(2,'B B','20181020');
INSERT INTO members VALUES(3,'C C','20171008');
INSERT INTO members VALUES(4,'D D','20171021');

SELECT * FROM members;

SELECT name,DATEDIFF(NOW(),start) AS memberdays FROM members;

SELECT '1 year' AS typ,name,365-DATEDIFF(NOW(),start) AS missingdays FROM members WHERE DATEDIFF(NOW(),start) BETWEEN 365-7 AND 365
UNION
SELECT '2 years' AS typ,name,2*365-DATEDIFF(NOW(),start) AS missingdays FROM members WHERE DATEDIFF(NOW(),start) BETWEEN 2*365-7 AND 2*365
ORDER BY missingdays;

SELECT ((DATEDIFF(NOW(),start) + 7) DIV 365) AS years,name,(365 - DATEDIFF(NOW(),start) % 365) AS missingdays FROM members WHERE DATEDIFF(NOW(),start) % 365 BETWEEN 365-7 AND 365 ORDER BY missingdays;

DROP TABLE members;

