Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


DROP TABLE IF EXISTS "public"."candidates";

CREATE TABLE "public"."candidates" (
    "day" timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "study_id" varchar(6) NOT NULL,
    "site_id" varchar(32),
    "status" varchar(32) NOT NULL,
    "total" int4 NOT NULL,
    "current" int4 NOT NULL
);

INSERT INTO "public"."candidates" ("day", "study_id", "site_id", "status", "total", "current") VALUES
('2020-01-01 00:00:00+00', 'TBX1', 'SWE-1', 'INCOMPLETE', 3, 2),
('2020-01-01 00:00:00+00', 'TBX1', 'SWE-2', 'INCOMPLETE', 7, 5),
('2020-01-01 00:00:00+00', 'TBX1', 'SWE-1', 'PENDING_CALLCENTER', 19, 17),
('2020-01-01 00:00:00+00', 'TBX1', 'SWE-2', 'PENDING_CALLCENTER', 29, 23),
('2020-01-01 00:00:00+00', 'TBX2', 'SWE-1', 'INCOMPLETE', 13, 11),
('2020-01-02 00:00:00+00', 'TBX1', 'SWE-1', 'INCOMPLETE', 37, 31),
('2020-01-02 00:00:00+00', 'TBX1', 'SWE-2', 'INCOMPLETE', 43, 41),
('2020-01-02 00:00:00+00', 'TBX1', NULL, 'INCOMPLETE', 120, 100),
('2020-01-02 00:00:00+00', 'TBX1', 'SWE-1', 'PENDING_CALLCENTER', 53, 47),
('2020-01-02 00:00:00+00', 'TBX1', NULL, 'PENDING_CALLCENTER', 85, 80),
('2020-01-03 00:00:00+00', 'RR1', 'A', 'PENDING_SITE', 79, 12),
('2020-01-03 00:00:00+00', 'RR1', 'A', 'REJECTED_SITE', 32, 31),
('2020-01-03 00:00:00+00', 'RR1', 'A', 'CONSENTED', 48, 3),
('2020-01-03 00:00:00+00', 'RR1', 'A', 'REJECTED_CONSENTED', 5, 5),
('2020-01-03 00:00:00+00', 'RR1', 'A', 'RANDOMIZED', 40, 16),
('2020-01-03 00:00:00+00', 'RR1', 'A', 'REJECTED_RANDOMIZED', 12, 12),
('2020-01-03 00:00:00+00', 'RR1', 'B', 'PENDING_SITE', 2, 0),
('2020-01-03 00:00:00+00', 'RR1', 'B', 'REJECTED_SITE', 2, 2),
('2020-01-03 00:00:00+00', 'RR1', 'C', 'PENDING_SITE', 1000, 1000);
19 rows affected

SELECT
	--sum("current")
	*
FROM (
	SELECT
		"study_id",
		"site_id",
		"status",
		max("day") AS "day"
	FROM
		"candidates"
	WHERE
		"study_id" in('TBX1')
		AND "status" in('INCOMPLETE')
	GROUP BY
		"study_id",
		"site_id",
		"status") AS "latest"
	INNER JOIN "candidates" ON "latest"."day" = "candidates"."day"
		AND "latest"."study_id" = "candidates"."study_id"
		AND "latest"."site_id" = "candidates"."site_id"
		AND "latest"."status" = "candidates"."status"


study_id
site_id
status
day
day
study_id
site_id
status
total
current


TBX1
SWE-1
INCOMPLETE
2020-01-02 00:00:00+00
2020-01-02 00:00:00+00
TBX1
SWE-1
INCOMPLETE
37
31

TBX1
SWE-2
INCOMPLETE
2020-01-02 00:00:00+00
2020-01-02 00:00:00+00
TBX1
SWE-2
INCOMPLETE
43
41


select distinct on (study_id, site_id,status) 
  candidates.* from candidates
WHERE
		"study_id" in('TBX1')
		AND "status" in('INCOMPLETE')
order by study_id, site_id,status,day desc		


day
study_id
site_id
status
total
current


2020-01-02 00:00:00+00
TBX1
SWE-1
INCOMPLETE
37
31

2020-01-02 00:00:00+00
TBX1
SWE-2
INCOMPLETE
43
41

2020-01-02 00:00:00+00
TBX1
null
INCOMPLETE
120
100

study_id	site_id	status	day	day	study_id	site_id	status	total	current
TBX1	SWE-1	INCOMPLETE	2020-01-02 00:00:00+00	2020-01-02 00:00:00+00	TBX1	SWE-1	INCOMPLETE	37	31
TBX1	SWE-2	INCOMPLETE	2020-01-02 00:00:00+00	2020-01-02 00:00:00+00	TBX1	SWE-2	INCOMPLETE	43	41