i have two table
table1
| CustomerEmailHashed | Year | booking date | Has Lapsed |
|---|---|---|---|
| 1 | 2018 | 07/10/2017 | 0 |
| 1 | 2018 | 28/12/2017 | 1 |
| 1 | 2022 | 09/03/2022 | 0 |
| 1 | 2022 | 22/02/2023 | 0 |
| 1 | 2023 | 16/04/2023 | 0 |
I want my output like
table 2
| CustomerEmailHashed | Life | start date | end date | total booking |
|---|---|---|---|---|
| 1 | 1 | 07/10/2017 | 28/12/2017 | 2 |
| 1 | 2 | 09/03/2022 | Null | 3 |
So it is summarising this on email and “start date” is first “booking date” and “end date” is last booking date if it has lapsed value 1 or if the value is 0 then it should be null.
it should also count the total number of booking which will come in total booking
SELECT
email,
MIN([booking date]) AS [start date],
CASE
WHEN MAX([Has Lapsed]) = 1 THEN MAX(CASE WHEN [Has Lapsed] = 1 THEN [booking date] END)
ELSE NULL
END AS [end date],
COUNT(*) AS [total booking]
FROM
table_name
GROUP BY
email;
New contributor
Deepankar Srigyan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.