By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Pledges(
PledgeID INTEGER NOT NULL PRIMARY KEY
,PledgeAmount VARCHAR(13) NOT NULL
,PledgeDate DATE NOT NULL
,DonorID INTEGER NOT NULL
);
INSERT INTO Pledges(PledgeID,PledgeAmount,PledgeDate,DonorID) VALUES (1,'100','04/03/2014',1);
INSERT INTO Pledges(PledgeID,PledgeAmount,PledgeDate,DonorID) VALUES (2,'200','04/03/2013',1);
INSERT INTO Pledges(PledgeID,PledgeAmount,PledgeDate,DonorID) VALUES (3,'100','04/03/2009',2);
INSERT INTO Pledges(PledgeID,PledgeAmount,PledgeDate,DonorID) VALUES (4,'2,000','01/01/2012',3);
INSERT INTO Pledges(PledgeID,PledgeAmount,PledgeDate,DonorID) VALUES (5,'1,000','01/01/2012',4);
INSERT INTO Pledges(PledgeID,PledgeAmount,PledgeDate,DonorID) VALUES (6,'500','01/01/2009',4);
6 rows affected
CREATE TABLE Donors(
DonorID INTEGER NOT NULL PRIMARY KEY
,Name VARCHAR(13) NOT NULL
,SpouseID INTEGER
);
INSERT INTO Donors(DonorID,Name,SpouseID) VALUES (1,'John Smith',3);
INSERT INTO Donors(DonorID,Name,SpouseID) VALUES (2,'Jack Johnson',NULL);
INSERT INTO Donors(DonorID,Name,SpouseID) VALUES (3,'Jane Smith',1);
INSERT INTO Donors(DonorID,Name,SpouseID) VALUES (4,'John Adams',NULL);
4 rows affected
with couples as (
select
case when d.SpouseID IS NULL then concat('',(d.DonorID * -1))
when d.SpouseID < d.DonorID then concat(d.SpouseID,d.DonorID)
else concat(d.DonorID,d.SpouseID)
end as combinedid
, case when d.SpouseID IS NULL then d.name
when d.SpouseID < d.DonorID then concat(s.name,d.name)
else concat(d.name, s.name)
end as names
from Donors d
left join Donors s on d.spouseid = s.donorid
)
select
*
from couples
| combinedid | names |
|---|---|
| 13 | John SmithJane Smith |
| -2 | Jack Johnson |
| 13 | John SmithJane Smith |
| -4 | John Adams |
select
case when d.SpouseID IS NULL then concat('',(d.DonorID * -1))
when d.SpouseID < d.DonorID then concat(d.SpouseID,d.DonorID)
else concat(d.DonorID,d.SpouseID)
end as combinedid
, max(year(PledgeDate)) MaxPledgeYear
from pledges p
left join donors d on p.donorid = d.donorid
group by
case when d.SpouseID IS NULL then concat('',(d.DonorID * -1))
when d.SpouseID < d.DonorID then concat(d.SpouseID,d.DonorID)
else concat(d.DonorID,d.SpouseID)
end
| combinedid | MaxPledgeYear |
|---|---|
| 13 | 2014 |
| -2 | 2009 |
| -4 | 2012 |
with couples as (
select
case when d.SpouseID IS NULL then concat('',(d.DonorID * -1))
when d.SpouseID < d.DonorID then concat(d.SpouseID,d.DonorID)
else concat(d.DonorID,d.SpouseID)
end as combinedid
, case when d.SpouseID IS NULL then d.name
when d.SpouseID < d.DonorID then concat(s.name,d.name)
else concat(d.name, s.name)
end as names
from Donors d
left join Donors s on d.spouseid = s.donorid
)
select distinct
couples.names, p.MaxPledgeYear
from couples
left join (
select
case when d.SpouseID IS NULL then concat('',(d.DonorID * -1))
when d.SpouseID < d.DonorID then concat(d.SpouseID,d.DonorID)
else concat(d.DonorID,d.SpouseID)
end as combinedid
, max(year(PledgeDate)) MaxPledgeYear
from pledges p
left join donors d on p.donorid = d.donorid
group by
case when d.SpouseID IS NULL then concat('',(d.DonorID * -1))
when d.SpouseID < d.DonorID then concat(d.SpouseID,d.DonorID)
else concat(d.DonorID,d.SpouseID)
end
) p on couples.combinedid = p.combinedid
where p.MaxPledgeYear < 2019
| names | MaxPledgeYear |
|---|---|
| Jack Johnson | 2009 |
| John Adams | 2012 |
| John SmithJane Smith | 2014 |