0

I have a SQL table called "Data", dates are in the first column and a number value in the second column which is updated daily.

I would like to view certain dates based on the value in second column and create a new table which shows all combinations of dates into two columns including a calculated days difference column.

The created table needs to update automatically as the "Data" table is updated.

enter image description here

Any help would be appreciated.

axiac
  • 68,258
  • 9
  • 99
  • 134
Swulph
  • 49
  • 3
  • I think you will need to clarify what you mean by "view certain dates based on the value in second column" and "shows all combinations of dates" because looking at your data I don't think you mean all combinations. – jwolf Feb 17 '18 at 19:44
  • Sorry about that, I have one table at the moment that has the dates and values. I would like that information sent to a new table that is updated automatically as I update the table daily. The information I want sending is all the dates that are equal to 100 and put them into a table that has two columns with the date combinations starting with the earliest date in the first column and any dates after that in the second column, (combination of dates). I would accept duplicates in both columns if it was easier. Then a 3rd column letting me know the days difference between the dates. – Swulph Feb 17 '18 at 20:08

2 Answers2

1

Consider a CROSS JOIN on same table, Data, that avoids reverse duplicates and same dates:

SELECT d1.`Date`, d2.`Date`, DATEDIFF(d1.Date, d2.Date) AS `Days`
FROM Data d1
CROSS JOIN Data d2
WHERE d1.`Date` < d2.`Date`
  AND d1.`Value` = 100 AND d2.`Value` = 100

Equivalently with INNER JOIN and ON clause:

SELECT d1.`Date`, d2.`Date`, DATEDIFF(d1.Date, d2.Date) AS `Days`
FROM Data d1
INNER JOIN Data d2
 ON  d1.`Value` = d2.`Value`
 AND d1.`Value` = 100
 AND d1.`Date` < d2.`Date`

And for inclusion in new table, build table, Table100, and run an INSERT INTO ... SELECT using above select query. The NOT EXISTS in WHERE clause is to avoid duplicate rows.

-- RUN ONLY ONCE
CREATE TABLE Table100 (
  `Date1` Date,
  `Date2` Date,
  `Days` Integer
);

-- RUN AFTER EACH Data UPDATE
INSERT INTO Table100 (Date1, Date2, Days)
SELECT d1.`Date`, d2.`Date`, DATEDIFF(d1.Date, d2.Date) AS `Days`
FROM Data d1
INNER JOIN Data d2
 ON  d1.`Value` = d2.`Value`
 AND d1.`Value` = 100
 AND d1.`Date` < d2.`Date`

WHERE NOT EXISTS 
    (SELECT 1 FROM Table100 t
     WHERE t.`Date1` = d1.`Date`
       AND t.`Date2` = d2.`Date`);
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • That is excellent, thanks again. If I wanted to filter by use of the Mod Function instead of value of 100. For example filtering out the value of 4 using the following function: MOD(Value, 10). – Swulph Feb 17 '18 at 21:03
  • @Swulph - IIUC - replace the condition: `d1.Value = 100` with `MOD(Value, 10) = 4` – Parfait Feb 18 '18 at 04:21
  • Yikes! Thought I had. That was supposed to be the ride-along comment. – jwolf Feb 18 '18 at 04:27
0

Looking at your data, I think you want the following:

select t1.date as date1, t2.date as date2, datediff(t1.date, t2.date) as days
    from table1 t1
    cross join table2 t2
    where t1.value = 100 and t2.value = 100
        and t1.date <> t2.date

We have two versions up now, Parfait's I believe will work. Mine differs in that it gives all combinations of dates both forward-looking and backward-looking and the last line and t1.date <> t2.date (which is optional) eliminates same day combo's. Parfait's is forward-looking only and my money's on him :)

I hope this helps.

jwolf
  • 908
  • 7
  • 13