-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE log (
    id INTEGER NOT NULL,
    username VARCHAR2(32),
    entrydate DATE,
    PRIMARY KEY(id)
);

INSERT INTO log VALUES(1,'A', TO_DATE('05-09-2008 17:10', 'DD-MM-YYYY HH24:MI'));
INSERT INTO log VALUES(2,'A', TO_DATE('05-09-2008 20:30', 'DD-MM-YYYY HH24:MI'));
INSERT INTO log VALUES(3,'B', TO_DATE('05-09-2008 23:00', 'DD-MM-YYYY HH24:MI'));
INSERT INTO log VALUES(4,'A', TO_DATE('06-09-2008 15:20', 'DD-MM-YYYY HH24:MI'));

SELECT username,TRUNC(SYSDATE)-TRUNC(entrydate)+1 AS days,COUNT(entrydate) AS records
FROM log 
GROUP BY username,TRUNC(SYSDATE)-TRUNC(entrydate)+1
HAVING TRUNC(SYSDATE)-TRUNC(entrydate)+1 <= 3
ORDER BY TRUNC(SYSDATE)-TRUNC(entrydate)+1,username;

SELECT t1.username AS username,t1.days AS days,NVL(t2.records,0) AS records
FROM (SELECT t1username.username AS username,t1days.days AS days 
     FROM (SELECT DISTINCT username FROM log) t1username,
          (SELECT ROWNUM AS days FROM all_objects WHERE ROWNUM <= 3) t1days) t1 LEFT JOIN
(SELECT username,TRUNC(SYSDATE)-TRUNC(entrydate)+1 AS days,COUNT(entrydate) AS records
FROM log 
GROUP BY username,TRUNC(SYSDATE)-TRUNC(entrydate)+1
HAVING TRUNC(SYSDATE)-TRUNC(entrydate)+1 <= 3) t2
ON t1.username=t2.username AND t1.days=t2.days
ORDER BY t1.days,t1.username;
DROP TABLE log;
