I am fairly new to DB2 and SQL. There exists a table of customers and their visits. I need to write a query to find visits by the same customer subsequent and within 24hr to a visit when Sale = 'Y'.
Based on this example data:
| CustomerId | VisitID | Sale | DateTime |
|---|---|---|---|
| 1 | 1 | Y | 2021-04-23 20:16:00.000000 |
| 2 | 2 | N | 2021-04-24 20:16:00.000000 |
| 1 | 3 | N | 2021-04-23 21:16:00.000000 |
| 2 | 4 | Y | 2021-04-25 20:16:00.000000 |
| 3 | 5 | Y | 2021-04-23 20:16:00.000000 |
| 2 | 6 | N | 2021-04-25 24:16:00.000000 |
| 3 | 7 | N | 2021-5-23 20:16:00.000000 |
The query results should return:
| VisitID |
|---|
| 3 |
| 6 |
How do I do this?
CodePudding user response：
Try this. You may uncomment the commented out block to run this statement as is.
/*
WITH MYTAB (CustomerId, VisitID, Sale, DateTime) AS
(
VALUES
(1, 1, 'Y', '2021-04-23 20:16:00'::TIMESTAMP)
, (1, 3, 'N', '2021-04-23 21:16:00'::TIMESTAMP)
, (2, 2, 'N', '2021-04-24 20:16:00'::TIMESTAMP)
, (2, 4, 'Y', '2021-04-25 20:16:00'::TIMESTAMP)
, (2, 6, 'N', '2021-04-25 23:16:00'::TIMESTAMP)
, (3, 5, 'Y', '2021-04-23 20:16:00'::TIMESTAMP)
, (3, 7, 'N', '2021-05-23 20:16:00'::TIMESTAMP)
)
*/
SELECT VisitID
FROM MYTAB A
WHERE EXISTS
(
SELECT 1
FROM MYTAB B
WHERE B.CustomerID = A.CustomerID
AND B.Sale = 'Y'
AND B.VisitID <> A.VisitID
AND A.DateTime BETWEEN B.DateTime AND B.DateTime 24 HOUR
)