Need SQL Guru :-)

[Harjo]

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.

[ROCLASI]

Hi Harjo,

I hope I made my struggle a bit clear this way.

Erm, nope?

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..

But you said that row 100 looked like this:

100, 01-01-2015, 01-04-2015, 3 (period of 3 months)

How will this match in any way that date range ?

We want to prevent to save the patterns into the database.

Are you talking about the interval between the start and stop dates as the pattern you don't want to store ? Or are you talking about those (quarter) date ranges you mentioned later in your post?

[Harjo]

How will this match in any way that date range ?

Because we know the period in months, we want to somehow, that SQL calculates the patterns for us.

Are you talking about the interval between the start and stop dates as the pattern you don't want to store ? Or are you talking about those (quarter) date ranges you mentioned later in your post?

I'm talking about those (quarter) date ranges. In my example I took a period of 3 months, (info in the last column) but it can also be 1 month, 3 months, 6 months, or 12 months.

[ROCLASI]

Hi Harjo,

So you want to search for any date ranges based on the start date column only, the fact that there is some interval pattern (1 month, 3 months) between the start and stop date is irrelevant?
Or are they used in your pattern matching as well ?

[ROCLASI]

Or are you saying I have a pattern here (01-07-2015, 01-10-2015) which is an interval of 3 months, now give me all records that have an interval of 3 months (don't even look at the dates in general)?

[Harjo]

Hi Harjo,
So you want to search for any date ranges based on the start date column only,

yes, forget the stopdate, in this example, we need that for other purposes.

the fact that there is some interval pattern (1 month, 3 months) between the start and stop date is irrelevant?

yes irrelevant, it's only relevant between the startdate and the next startdate (in the pattern)

Or are they used in your pattern matching as well ?

no

[ROCLASI]

Hi Harjo,

Perhaps you should explain what the purpose of this query is. You say this is for subscriptions. Do you want to just see if a subscription is due for invoicing (and create an invoice records with it)? Or are you adding new subscription records based on previous records? Or is there another use case ?

[Harjo]

It is just for totaling subscriptions correctly, in a tableview

When I have a subscription of 1 month starting from 01-01-2015 with an amount of 10 Euro (for your info, that subscription will be invoiced every month, in total 12 times per year)
And I query for the whole year: 01-01-2015 ... 01-01-2016, I want to find THAT subscription 12 times! (so the sum is 120 Euro)

Also when I query from 01-07-2015...01-09.2015 I want to find THAT subscription 2 times.

[ROCLASI]

Hi harjo,

So you want to work with recurring events (and sum them up).
Take a look at this:

http://justatheory.com/computers/databa ... vents.html

A fork of that function can also be found here:
https://github.com/bakineggs/recurring_events_for

[Harjo]

I don't want to create/store recurring records in a database...
Can this be done, that (PostgreSQL) SQL calculates this on the fly?

EDIT: ah wait, I see it does calculates it on the fly, let me study this...

[ROCLASI]

That is what that function does.

[ROCLASI]

Okay, couldn't help myself....
You can do this with a recursive Common Table Expression (Pg only)

Code: Select all

WITH RECURSIVE subs(id, startdate, recurring_in_months, amount, recurrence) AS (
    SELECT id, startdate, recurring_in_months, amount, 1
    FROM subscription
    UNION ALL
    SELECT s.id, s.startdate, s.recurring_in_months, s.amount, s.recurrence+1
    FROM subs s
    WHERE s.startdate + (s.recurrence * s.recurring_in_months * interval '1 month') BETWEEN ? AND ?
)
SELECT id, sum(amount)
FROM subs
WHERE startdate BETWEEN ? AND ?
GROUP BY id

params: [ startDate, endDate, startDate, endDate]

More info on (recursive) CTE's are here:
http://www.postgresql.org/docs/9.4/stat ... -with.html

Hope this helps.

[Harjo]

Thanks Robert,

really appreciate this!
in your example, the recurrence is set fixed as 1
but it can be any number from 1 till 12

[ROCLASI]

No you control this with your where clause. So your params control the window you want to calculate the subscription amounts in.
The CTE itself handles ANY recurrence option (1, 2, 3, 6, 12 month subscriptions) so you don't have to touch anything in the CTE.

Or to rephrase this:
The recurrence calc is based on your recurring_in_months value. So you only have to worry about over what window you want to see the totals.

[Harjo]

oke, but in my case I have this in the database:

startdate = 01-01-2015 and recurring in months = 3

And I use your query, and search between 01-02-2015 ...01-01-2016, it doesn't find the record!

var vStartDate = new Date(2015,1,1)
var vStopDate = new Date(2016,0,1)
var vArguments = [vStartDate, vStopDate,vStartDate, vStopDate]