Hi,
I need some SQL guidance.
We have a database called subscriptions with the following data:
columns:
ID (Integer), startdate (Timestamp), stopdate (Timestamp), recurring_in_months (Integer)
with rows like (we round the time-peace to 00:00)
100, 01-01-2015, 01-04-2015, 3 (period of 3 months)
or
101, 05-01-2015, 05-02-2015, 1
What we want to be able to do now, is query in different recurring patterns,
for example, the first row: wil have this pattern:
01-01-2015, 01-04-2015
01-04-2015, 01-07-2015
01-07-2015, 01-10-2015
jumps of 3 months and so on..
In some way if I query the startdate between 01-07-2015 and 01-08-2015 (the startdate of the third pattern 01-07-2015 matches!) I want to find row with ID 100, because that matches the pattern..
We want to prevent to save the patterns into the database.
I hope I made my struggle a bit clear this way.
Can some one help me to find or create an sql query for this, or give me some directions?
We use postgreSQL for this.