By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64) Jun 12 2020 20:39:00 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE Customer (
AcctID INTEGER,
CustomerName VARCHAR(11)
);
INSERT INTO Customer
(AcctID, CustomerName)
VALUES
('11', 'Bobs Tires'),
('12', 'Neds Nails'),
('13', 'Good Eats'),
('14', 'Embers');
CREATE TABLE Activities (
AcctivityID INTEGER,
AcctID INTEGER,
Activity VARCHAR(15),
Date DATETIME
);
INSERT INTO Activities
(AcctivityID, AcctID, Activity, Date)
VALUES
('1', '11', 'Contact Added', '2021-01-01'),
('2', '11', 'Lead', '2021-01-05'),
('3', '11', 'Phone Call', '2021-01-06'),
('4', '12', 'Marketing Email', '2021-02-01'),
('5', '12', 'Lead', '2021-02-02'),
('6', '13', 'Lead', '2021-02-03'),
('7', '13', 'Phone Call', '2021-02-15'),
('8', '13', 'Sales Email', '2021-02-15'),
('9', '14', 'Cold Call', '2021-01-20');
13 rows affected
SELECT
c.CustomerName
, a.Activity
, a.Date
FROM Customer c
INNER JOIN Activities a ON c.AcctID = a.AcctID
WHERE
a.Date >= '2021-01-01'
AND c.AcctID IN (SELECT AcctID FROM Activities WHERE Activity LIKE 'Lead')
ORDER BY c.CustomerName, a.Date
| CustomerName | Activity | Date |
|---|---|---|
| Bobs Tires | Contact Added | 2021-01-01 00:00:00.000 |
| Bobs Tires | Lead | 2021-01-05 00:00:00.000 |
| Bobs Tires | Phone Call | 2021-01-06 00:00:00.000 |
| Good Eats | Lead | 2021-02-03 00:00:00.000 |
| Good Eats | Phone Call | 2021-02-15 00:00:00.000 |
| Good Eats | Sales Email | 2021-02-15 00:00:00.000 |
| Neds Nails | Marketing Email | 2021-02-01 00:00:00.000 |
| Neds Nails | Lead | 2021-02-02 00:00:00.000 |
SELECT
la.AcctID,
MIN(la.Date) as earliest_lead_date
FROM
Activities la
WHERE
la.Activity = 'Lead' AND
la.Date > '2021-01-01'
GROUP BY
la.AcctID
| AcctID | earliest_lead_date |
|---|---|
| 11 | 2021-01-05 00:00:00.000 |
| 12 | 2021-02-02 00:00:00.000 |
| 13 | 2021-02-03 00:00:00.000 |
SELECT
la.*,
MIN(CASE WHEN la.Activity='Lead' THEN la.Date END) OVER (PARTITION BY la.AcctId) as eld,
CASE
WHEN la.Date >= MIN(CASE WHEN la.Activity='Lead' THEN la.Date END) OVER (PARTITION BY la.AcctId) THEN 1
ELSE 0
END as include_item
FROM
Activities la
WHERE
la.Date > '2021-01-01'
| AcctivityID | AcctID | Activity | Date | eld | include_item |
|---|---|---|---|---|---|
| 2 | 11 | Lead | 2021-01-05 00:00:00.000 | 2021-01-05 00:00:00.000 | 1 |
| 3 | 11 | Phone Call | 2021-01-06 00:00:00.000 | 2021-01-05 00:00:00.000 | 1 |
| 4 | 12 | Marketing Email | 2021-02-01 00:00:00.000 | 2021-02-02 00:00:00.000 | 0 |
| 5 | 12 | Lead | 2021-02-02 00:00:00.000 | 2021-02-02 00:00:00.000 | 1 |
| 6 | 13 | Lead | 2021-02-03 00:00:00.000 | 2021-02-03 00:00:00.000 | 1 |
| 7 | 13 | Phone Call | 2021-02-15 00:00:00.000 | 2021-02-03 00:00:00.000 | 1 |
| 8 | 13 | Sales Email | 2021-02-15 00:00:00.000 | 2021-02-03 00:00:00.000 | 1 |
| 9 | 14 | Cold Call | 2021-01-20 00:00:00.000 | null | 0 |
Warning: Null value is eliminated by an aggregate or other SET operation.
WITH lead_activities AS (
SELECT
la.*,
CASE
WHEN la.Date >= MIN(CASE WHEN la.Activity='Lead' THEN la.Date END) OVER (PARTITION BY la.AcctId) THEN 1
ELSE 0
END as is_valid_lead_item
FROM
Activities la
WHERE
la.Date > '2021-01-01'
)
SELECT
c.CustomerName,
a.Activity,
a.Date
FROM
Customer c
INNER JOIN
lead_activities a ON c.AcctID = a.AcctID AND
a.is_valid_lead_item=1
ORDER BY c.CustomerName, a.Date
| CustomerName | Activity | Date |
|---|---|---|
| Bobs Tires | Lead | 2021-01-05 00:00:00.000 |
| Bobs Tires | Phone Call | 2021-01-06 00:00:00.000 |
| Good Eats | Lead | 2021-02-03 00:00:00.000 |
| Good Eats | Phone Call | 2021-02-15 00:00:00.000 |
| Good Eats | Sales Email | 2021-02-15 00:00:00.000 |
| Neds Nails | Lead | 2021-02-02 00:00:00.000 |
Warning: Null value is eliminated by an aggregate or other SET operation.