I have an input table like this
| date | amount |
|---|---|
| 2011-01-02 | 50 |
| 2011-01-03 | 40 |
| 2011-01-04 | 20 |
| 2011-01-05 | 10 |
Am looking to group it and create a one-to-many where the difference of date A1 is compared to every other date (A1 – A2, A1 – A3, A1 – A4 ) and repeated for the rest (A2 – A1, A2 – A3, etc)
| date | difference in days | date compared |
|---|---|---|
| 2011-01-02 | 1 day | 2011-01-03 |
| 2011-01-02 | 2 days | 2011-01-04 |
| 2011-01-02 | 3 days | 2011-01-05 |
| 2011-01-03 | -1 days | 2011-01-02 |
| 2011-01-03 | 1 days | 2011-01-04 |
| 2011-01-03 | 2 days | 2011-01-05 |
| 2011-01-04 | -2 days | 2011-01-02 |
| 2011-01-04 | -1 days | 2011-01-03 |
| 2011-01-04 | 1 days | 2011-01-05 |
Nightengale is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Assuming you are using PostgreSQL (your question doesn’t clarify), you can use the DATE_PART function to subtract the days between dates:
SELECT
a.date
, b.date as compared_date
, DATE_PART('day', b.date) - DATE_PART('day', a.date) as diff
FROM mytable as a
CROSS JOIN mytable as b
Where mytable is your original table.
We use the CROSS JOIN to link all dates from the table to all dates, then the second step is to use DATE_PART to find the difference between the two.
Feel free to learn more by checking out this link.