Need SQL Guru :-)

Questions and answers regarding general SQL and backend databases

Re: Need SQL Guru :-)

Postby ROCLASI » Wed Aug 12, 2015 2:38 pm

Right, I forgot to return the calculated dates as well.
Here is the working version:

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.recurrence * s.recurring_in_months * interval '1 month'), 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
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Need SQL Guru :-)

Postby Harjo » Wed Aug 12, 2015 3:13 pm

oke, I have changed the method and columnames to the column names I have in my database
and changed a little bit, so it does'nt return a sum, but a dataset with all the rows I need, for the moment...

Code: Select all
function onActionTestRecurring(event) {
var vSQL = "WITH RECURSIVE subs(subscriptionsid, invoicedate, everymonth, subscription_subtotal, recurrence) AS ( \
    SELECT subscriptionsid, invoicedate, everymonth, subscription_subtotal, 1 \
    FROM subscriptions \
    UNION ALL \
    SELECT s.subscriptionsid, s.invoicedate + (s.recurrence * s.everymonth * interval '1 month'), s.everymonth, s.subscription_subtotal, s.recurrence+1  \
    FROM subs s \
    WHERE s.invoicedate + (s.recurrence * s.everymonth * interval '1 month') BETWEEN ? AND ? \
   ) \
   SELECT subscriptionsid, subscription_subtotal, invoicedate \
   FROM subs \
   WHERE invoicedate BETWEEN ? AND ? AND subscriptionsid = 279"
   var vStartSearchDate = new Date(2015,0,1)
   var vStopSearchDate = new Date(2016,0,1)

   var vArguments = [vStartSearchDate, vStopSearchDate, vStartSearchDate, vStopSearchDate]
   var vDataset = databaseManager.getDataSetByQuery('directmanager',vSQL,vArguments   ,-1)
   application.output(vDataset.getAsText("\t","\n","",false))
}


the application.output returns

Code: Select all
279   500.0   2015-01-01 10:58:05.139
279   500.0   2015-02-01 10:58:05.139
279   500.0   2015-04-01 10:58:05.139
279   500.0   2015-07-01 10:58:05.139
279   500.0   2015-11-01 10:58:05.139


where it should return

Code: Select all
279   500.0   2015-01-01 10:58:05.139
279   500.0   2015-02-01 10:58:05.139
279   500.0   2015-03-01 10:58:05.139
279   500.0   2015-04-01 10:58:05.139
279   500.0   2015-05-01 10:58:05.139
279   500.0   2015-06-01 10:58:05.139
279   500.0   2015-07-01 10:58:05.139
279   500.0   2015-08-01 10:58:05.139
279   500.0   2015-09-01 10:58:05.139
279   500.0   2015-10-01 10:58:05.139
279   500.0   2015-11-01 10:58:05.139
279   500.0   2015-12-01 10:58:05.139
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: Need SQL Guru :-)

Postby ROCLASI » Wed Aug 12, 2015 5:07 pm

Found the issue, it was calculating the new dates over the new dates instead of over the original dates.
So here is the working version:

Code: Select all
WITH RECURSIVE subs(id, startdate, recurring_in_months, amount, recurrence) AS (
    SELECT id
        , startdate
        , recurring_in_months
        , amount
        , 1
        , startdate as originaldate
    FROM subscription
    UNION ALL
    SELECT id
        , originaldate + (recurrence * recurring_in_months * interval '1 month')
        , recurring_in_months
        , amount
        , recurrence + 1
        , originaldate
    FROM subs
    WHERE originaldate + (recurrence * recurring_in_months * interval '1 month') BETWEEN ? AND ?
)
SELECT id, sum(amount)
FROM subs
WHERE startdate BETWEEN ? AND ?
GROUP BY id



[Edit]

I guess I could take the occurrence column out and do calc it over the calculated date like so:
Code: Select all
WITH RECURSIVE subs(id, startdate, recurring_in_months, amount) AS (
    SELECT id
        , startdate
        , recurring_in_months
        , amount
    FROM subscription
    UNION ALL
    SELECT id
        , startdate + (recurring_in_months * interval '1 month')
        , recurring_in_months
        , amount
    FROM subs
    WHERE startdate + (recurring_in_months * interval '1 month') BETWEEN ? AND ?
)
SELECT id, sum(amount)
FROM subs
WHERE startdate BETWEEN ? AND ?
GROUP BY id

Gives me the same result and has less columns to fetch :)


Hope his helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Need SQL Guru :-)

Postby ROCLASI » Thu Aug 13, 2015 9:25 am

Hi Harjo,

Looking at this with a fresh pair of eyes (and coffee ;)) I see the query can be optimized by moving the last WHERE clause into the first part of the CTE. This will make it process MUCH less rows to get to it's result.
A simple EXPLAIN shows the difference.

Here is the optimized code:
Code: Select all
WITH RECURSIVE subs(id, startdate, recurring_in_months, amount) AS (
    SELECT id
        , startdate
        , recurring_in_months
        , amount
    FROM subscription
    WHERE startdate BETWEEN ? AND ?
    UNION ALL
    SELECT id
        , startdate + (recurring_in_months * interval '1 month')
        , recurring_in_months
        , amount
    FROM subs
    WHERE startdate + (recurring_in_months * interval '1 month') BETWEEN ? AND ?
)
SELECT id, sum(amount), count(id)
FROM subs
GROUP BY id

The order of the parameters stays the same.

Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Need SQL Guru :-)

Postby Harjo » Thu Aug 13, 2015 9:43 am

With your latest example, if I search from 01-01-2015..01-01-2016,
I get indeed 12 results,

Code: Select all
279   500.0   2015-01-01 10:58:05.139
279   500.0   2015-02-01 10:58:05.139
279   500.0   2015-03-01 10:58:05.139
279   500.0   2015-04-01 10:58:05.139
279   500.0   2015-05-01 10:58:05.139
279   500.0   2015-06-01 10:58:05.139
279   500.0   2015-07-01 10:58:05.139
279   500.0   2015-08-01 10:58:05.139
279   500.0   2015-09-01 10:58:05.139
279   500.0   2015-10-01 10:58:05.139
279   500.0   2015-11-01 10:58:05.139
279   500.0   2015-12-01 10:58:05.139


this is my method (adjusted to my development system)
Code: Select all
var vSQL = "WITH RECURSIVE subs(subscriptionsid, invoicedate, everymonth, subscription_subtotal) AS ( \
             SELECT subscriptionsid \
             , invoicedate \
             , everymonth \
             , subscription_subtotal \
         FROM subscriptions \
         WHERE invoicedate BETWEEN ? AND ? \
         UNION ALL \
         SELECT subscriptionsid \
             , invoicedate + (everymonth * interval '1 month') \
             , everymonth \
             , subscription_subtotal \
         FROM subs \
         WHERE invoicedate + (everymonth * interval '1 month') BETWEEN ? AND ? \
         ) \
         SELECT subscriptionsid, subscription_subtotal, invoicedate \
         FROM subs \
         WHERE subscriptionsid = 279" 
   var vStartSearchDate = new Date(2015,0,1)
   var vStopSearchDate = new Date(2016,0,1)

   var vArguments = [vStartSearchDate, vStopSearchDate, vStartSearchDate, vStopSearchDate]
   var vDataset = databaseManager.getDataSetByQuery('directmanager',vSQL,vArguments   ,-1)
   application.output(vDataset.getAsText("\t","\n","",false))


but when I search from
Code: Select all
var vStartSearchDate = new Date(2015,1,1)//01-02-2015
I get zero results..
The same problem as before...
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: Need SQL Guru :-)

Postby mboegem » Thu Aug 13, 2015 9:56 am

I believe this is because the dates filter out the original records, therefor not generating the recurring records.

try this (although this is calculating all history, therefor probably a lot slower):
Code: Select all
var vSQL = "WITH RECURSIVE subs(subscriptionsid, invoicedate, everymonth, subscription_subtotal) AS ( \
             SELECT subscriptionsid \
             , invoicedate \
             , everymonth \
             , subscription_subtotal \
         FROM subscriptions \
         WHERE invoicedate <= ? \
         UNION ALL \
         SELECT subscriptionsid \
             , invoicedate + (everymonth * interval '1 month') \
             , everymonth \
             , subscription_subtotal \
         FROM subs \
         WHERE invoicedate + (everymonth * interval '1 month') BETWEEN ? AND ? \
         ) \
         SELECT subscriptionsid, subscription_subtotal, invoicedate \
         FROM subs \
         WHERE subscriptionsid = 279 AND invoicedate BETWEEN ? AND ?" 
   var vStartSearchDate = new Date(2015,0,1)
   var vStopSearchDate = new Date(2016,0,1)

   var vArguments = [vStopSearchDate, vStartSearchDate, vStopSearchDate, vStartSearchDate, vStopSearchDate]
   var vDataset = databaseManager.getDataSetByQuery('directmanager',vSQL,vArguments   ,-1)
   application.output(vDataset.getAsText("\t","\n","",false))
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Need SQL Guru :-)

Postby ROCLASI » Thu Aug 13, 2015 10:07 am

Marc, you are correct.
I just came to the same conclusion. So much for the fresh pair of eyes and coffee ;)
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Need SQL Guru :-)

Postby Harjo » Thu Aug 13, 2015 10:09 am

HI Mark,

yes, now if I search
Code: Select all
var vStartSearchDate = new Date(2015,1,1)
var vStopSearchDate = new Date(2016,0,1)


I get 11 rows, which is good:

Code: Select all
279   500.0   2015-02-01 10:58:05.139
279   500.0   2015-03-01 10:58:05.139
279   500.0   2015-04-01 10:58:05.139
279   500.0   2015-05-01 10:58:05.139
279   500.0   2015-06-01 10:58:05.139
279   500.0   2015-07-01 10:58:05.139
279   500.0   2015-08-01 10:58:05.139
279   500.0   2015-09-01 10:58:05.139
279   500.0   2015-10-01 10:58:05.139
279   500.0   2015-11-01 10:58:05.139
279   500.0   2015-12-01 10:58:05.139


but again, shift the search one month up like this:

Code: Select all
var vStartSearchDate = new Date(2015,2,1)
var vStopSearchDate = new Date(2016,0,1)


this results again in zero results..
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: Need SQL Guru :-)

Postby ROCLASI » Thu Aug 13, 2015 10:18 am

Hi Harjo,

I can not reproduce your issue.
This is what I see when I query just the monthly subscription:
Screen Shot 2015-08-13 at 10.15.46.png
original data and recurrences
Screen Shot 2015-08-13 at 10.15.46.png (127.46 KiB) Viewed 7675 times

You see the same result when you run the query in a query tool?
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Need SQL Guru :-)

Postby Harjo » Thu Aug 13, 2015 10:26 am

Change the record with ID 3, to:

startdate = 1-1-2015
stopdate = 1-2-2015
recurring_in_month = 1

and do the same search again :wink:
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: Need SQL Guru :-)

Postby ROCLASI » Thu Aug 13, 2015 10:39 am

Hmm..I guess we should drop the BETWEEN altogether in the CTE because when the date range is not met in the first iteration it effectively drops out of the result set.
So this should work (fingers crossed!)
Code: Select all
WITH RECURSIVE subs(id, startdate, recurring_in_months, amount) AS (
    SELECT id
        , startdate
        , recurring_in_months
        , amount
    FROM subscription
    WHERE startdate <= ?
    UNION ALL
    SELECT id
        , startdate + (recurring_in_months * interval '1 month')
        , recurring_in_months
        , amount
    FROM subs
    WHERE startdate + (recurring_in_months * interval '1 month') <= ?
)
SELECT *
FROM subs
WHERE startdate BETWEEN ? AND ?

parameters = [endDate, endDate, startDate, endDate]
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Need SQL Guru :-)

Postby Harjo » Thu Aug 13, 2015 11:07 am

YES, YES YES! now we are getting somewhere!!! :D :D
I will go further from here.
Maybe later I come back, with one feature added, but I'm not sure the customer wants that!

Robert & Marc, you get beer from me, on the next SW! :lol:
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: Need SQL Guru :-)

Postby mboegem » Thu Aug 13, 2015 11:18 am

Harjo wrote:Robert & Marc, you get beer from me, on the next SW!


We're getting close to boarding... ;-)
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Previous

Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 8 guests