I have a table with sales like this:
Select Id, startdate, endate
From tabe
Output:
1 '01-01-2022' '06-01-2022'
1 '02-01-2022' '05-01-2022'
1 '13-01-2022' '17-01-2022'
2 '01-01-2022' '02-01-2022'
2 '01-01-2022' '03-01-2022'
Etc
So basically one id can be in different sales. What i need is this table format:
01-01-2022 1
02-01-2022 1
03-01-2022 1
04-01-2022 1
05-01-2022 1
06-01-2022 1
07-01-2022 1
13-01-2022 1
14-01-2022 1
15-01-2022 1
16-01-2022 1
17-01-2022 1
01-01-2022 2
02-01-2022 2
03-01-2022 2
Etc.
I also can't create a new table for a calendar. I have found a solution with unequal join (on date between startdate and enddate) i have a table with dates similar to calendar. But clickhouse can't join on inequalities. Anybody has a thought how can i do this?
Try this:
SELECT DISTINCT
c1 AS id
, toDate (arrayJoin (range (toUInt32 (c2), toUInt32 (c3) + 1)))
AS dt
FROM VALUES
(
(1, toDate ('2022-01-01'), toDate ('2022-01-06'))
, (1, toDate ('2022-01-02'), toDate ('2022-01-05'))
, (1, toDate ('2022-01-13'), toDate ('2022-01-17'))
, (2, toDate ('2022-01-01'), toDate ('2022-01-02'))
, (2, toDate ('2022-01-01'), toDate ('2022-01-03'))
)
| id | dt |
|---|---|
| 1 | 2022-01-01 |
| 1 | 2022-01-02 |
| 1 | 2022-01-03 |
| 1 | 2022-01-04 |
| 1 | 2022-01-05 |
| 1 | 2022-01-06 |
| 1 | 2022-01-13 |
| 1 | 2022-01-14 |
| 1 | 2022-01-15 |
| 1 | 2022-01-16 |
| 1 | 2022-01-17 |
| 2 | 2022-01-01 |
| 2 | 2022-01-02 |
| 2 | 2022-01-03 |