Solved

Setup subscriptionrules

Posted on 2016-07-18
6
57 Views
Last Modified: 2016-07-25
Hi,

I want to add subscription based invoicing to my application. The invoicing itself is not part of this question as that is already in place.

As multiple customers will use this functionaity I want to setup subscriptionrules in a very flexible way. The biggest challange for me is to allow different periods (intervals) for each rule (one per rule). F.e. when you look at recurring appointments in MS Outlook (see attached image - Dutch) you get an idea what I'm aiming for.Patterns-of-recurrence.png
My challange is how to efficiently store all these settings into a table (datamodal) and to determine which rule(s) need to be invoiced based on the current date. After a rule is invoiced the last-invoiced-date will be set for the rule.
  • How to store these subscriptionrules efficiently in a database?
  • How to determine which rule(s) must be executed (invoiced) based on a given day?
  • How to make sure a rule is never skipped (f.e. when application isn't used for some days/weeks))
If a rule should have been executed a few times (based on last-invoiced-date, period and current date) all 'missed' executions must be all executed anyhow afterwards.

Any simple source-examples are very welcome as is any advice on the issue at hand.
Thanks!
0
Comment
Question by:Delphiwizard
  • 4
6 Comments
 
LVL 13

Assisted Solution

by:Russell Fox
Russell Fox earned 250 total points
ID: 41718069
Hi Delphiwizard, since you're using SQL Server you should take a look at the Agent scheduling in SQL. It has the same flexibility you're looking for and you should be able to use the user's input to create an Agent job that runs the invoicing procedure on schedule, regardless of whether or not they use the application that day. Below is the syntax for the sp_add_schedule internal store procedure, so you can see how Microsoft stores this info (and you can then use sp_add_schedule to create your user's schedule):
sp_add_schedule [ @schedule_name = ] 'schedule_name'   
    [ , [ @enabled = ] enabled ]  
    [ , [ @freq_type = ] freq_type ]  
    [ , [ @freq_interval = ] freq_interval ]   
    [ , [ @freq_subday_type = ] freq_subday_type ]   
    [ , [ @freq_subday_interval = ] freq_subday_interval ]   
    [ , [ @freq_relative_interval = ] freq_relative_interval ]   
    [ , [ @freq_recurrence_factor = ] freq_recurrence_factor ]   
    [ , [ @active_start_date = ] active_start_date ]   
    [ , [ @active_end_date = ] active_end_date ]   
    [ , [ @active_start_time = ] active_start_time ]   
    [ , [ @active_end_time = ] active_end_time ]   
    [ , [ @owner_login_name = ] 'owner_login_name' ]  
    [ , [ @schedule_uid = ] schedule_uid OUTPUT ]  
    [ , [ @schedule_id = ] schedule_id OUTPUT ]  
    [ , [ @originating_server = ] server_name ] /* internal */  

Open in new window


Hope it helps!
0
 

Author Comment

by:Delphiwizard
ID: 41718153
@Russell Fox: Your idea is definitely usefull, but my application including SQL Server are frequently installed on a stand-alone desktop/laptop which is shutdown add the end of the day and might not be used for a few days or more. Therefor I prefer to store settings in a table (also for easy access and maintenance of rules by the enduser).

By the way I use SQL Server 2014 Express edition where SQL Server Agent is not available.
0
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 250 total points
ID: 41719151
in oracle 12.1 i have scheduling groups as a set of recurring items

CREATE TABLE SCHED
(
  GROUPID                 INTEGER               NOT NULL,
  ID                      INTEGER               DEFAULT "S_SCHED"."NEXTVAL" NOT NULL,
  INTERVAL_DAYS           NUMBER,
  DT_ONETIME              DATE,
  WEEKDAYNR               INTEGER,
  MONTHDAYNR              INTEGER,
  STARTTIME               VARCHAR2(20 CHAR),
  INTERVAL_DURATION_DAYS  NUMBER
);

CREATE TABLE SCHEDGROUP
(
  GROUPID  INTEGER                              DEFAULT "S_SCHEDGROUP"."NEXTVAL" NOT NULL,
  NAME     VARCHAR2(50 CHAR),
  DESCR    VARCHAR2(500 CHAR),
  ENABLED  INTEGER                              DEFAULT 1
);

ALTER TABLE REFMAN.SCHED ADD (
  CONSTRAINT FK_SCHED_SCHEDGROUP 
  FOREIGN KEY (GROUPID) 
  REFERENCES REFMAN.SCHEDGROUP (GROUPID)
  ENABLE VALIDATE);

Open in new window


and a function to evaluate for the occurence of the schedule based on a specific start time
CREATE OR REPLACE function next_date(ischedgroupid in integer, ifromdate in date default sysdate) return date
is
  vnextdate date;
  vfirstdate date;
  procedure evaldates is  
  begin
    if vnextdate is not null and (vfirstdate is null or vnextdate < vfirstdate) then 
      vfirstdate := vnextdate;
    end if;  
  end; 
begin
  vnextdate := null;
  vfirstdate := null;
  
  -- one time only events
  begin 
    select dt_onetime into vnextdate
    from schedgroup sg, sched s
    where sg.groupid = s.groupid
      and sg.groupid = ischedgroupid
      and s.dt_onetime > ifromdate
      and s.dt_onetime is not null
    order by s.dt_onetime asc      
    fetch first 1 row only;
  exception
    when NO_DATA_FOUND then 
      null;
  end;
  evaldates;
  
  --specific starting time in day of week or day of month
  begin
    select
      dt into vnextdate 
    from (
      select  
        to_date(to_char(      
          case 
            when s.weekdaynr is not null then trunc(ifromdate, 'IW') + s.weekdaynr -1
            else trunc(ifromdate, 'MM') + s.monthdaynr -1
          end, 'dd/mm/yyyy') ||' '||s.starttime, 'dd/mm/yyyy hh24:mi:ss') dt 
      from schedgroup sg, sched s
      where sg.groupid = s.groupid
        and sg.groupid = ischedgroupid
        and s.interval_days is null
        and s.starttime is not null
        and (s.weekdaynr is not null or s.monthdaynr is not null)
        and  to_date(to_char(      
          case 
            when s.weekdaynr is not null then trunc(ifromdate, 'IW') + s.weekdaynr -1
            else trunc(ifromdate, 'MM') + s.monthdaynr -1
          end, 'dd/mm/yyyy') ||' '||s.starttime, 'dd/mm/yyyy hh24:mi:ss') > ifromdate)
    order by 1
    fetch first 1 row only; 
  exception 
    when no_data_found then 
      null;
  end;
  evaldates;
  
  -- interval with duration for specific day or specific month day
  begin
    with 
      dttemp as (
        select
          s.id,  
          case 
            when s.weekdaynr is not null then trunc(ifromdate, 'IW') + s.weekdaynr -1
            else trunc(ifromdate, 'MM') + s.monthdaynr -1
          end dt_day_start,
          s.starttime,
          s.interval_days,
          s.interval_duration_days
        from schedgroup sg, sched s
        where sg.groupid = s.groupid
          and sg.groupid = ischedgroupid
          and s.interval_days is not null
          and s.starttime is not null
          and (s.weekdaynr is not null or s.monthdaynr is not null)),
      dtrange as (          
        select 
          id, 
          to_date(to_char(dt_day_start, 'dd/mm/yyyy')||' '||starttime, 'dd/mm/yyyy hh24:mi:ss') dt_start,
          interval_days,
          decode(interval_duration_days, 
            null, dt_day_start+1, 
            to_date(to_char(dt_day_start, 'dd/mm/yyyy')||' '||starttime, 'dd/mm/yyyy hh24:mi:ss') + interval_duration_days) dt_end  
        from dttemp),
      dtrecs as (
        select level nr  
        from dual connect by level < 1000)
    select r.dt_start + r.interval_days*(dr.nr-1) into vnextdate
    from dtrange r, dtrecs dr
    where r.dt_start + r.interval_days*(dr.nr-1) > ifromdate
      and r.dt_start + r.interval_days*(dr.nr-1) <= r.dt_end
    order by 1
    fetch first 1 row only;
  exception 
    when no_data_found then 
      null;
  end;
  evaldates;
  
  return vfirstdate; 
end;
/

Open in new window


typically i use the last occurrence of the event to calculate the next occurrence
this way i can calculate in the past and in the future

i think you'll have a job on to convert this to mssql
it's using hierarchical recursive subquery factoring
a temp table might help to solve that
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Delphiwizard
ID: 41719374
Thank you Geert, I'll have a look at it.
0
 

Accepted Solution

by:
Delphiwizard earned 0 total points
ID: 41722199
I found a solution at following link:
https://github.com/eugeniomiro/TestScheduledJobs
This has all the basic functionality I need.
0
 

Author Closing Comment

by:Delphiwizard
ID: 41727379
Thank you for your advice and pointing me in the right direction.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
grouping logic 6 46
update joined tables 2 30
Help with Sorting Full Text results 2 14
Sql query 107 24
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now