looking for a set based solution

  • Scenario:

    I have a set of agreements that have a date the agreement was started. From those agreements I track activities that occur during 3 month periods starting from the start date. I calculate those periods on the fly to report the number of activities that occurred during the current period and the previous period. The tricky part is that the agreement can be suspended for any number of times for any length of time and when this happens the duration of the periods needs to be extended so the person has the full 3 months to collect their activities and be rewarded. The data I have to work is:

    select

    555 partyid,777 roleid, 12345 memberagreementid,convert(datetime,'1/22/2016') editablestartdate

    into memberagreement

    select

    1 suspensionid,12345 targetentityid, convert(datetime,'3/12/2016') begintime, convert(datetime,'3/30/2016') endtime

    into suspension

    union all

    select 2,12345,'5/10/2016','5/30/2016'

    union all

    select 3,12345,'7/10/2016','7/20/2016'

    union all

    select 4,12345,'9/10/2016','9/30/2016'

    union all

    select 5,12345,'11/10/2016','11/30/2016'

    The calculation for the incentive periods is:

    declare @today datetime = '9/22/2016'

    ;with

    incentivedates as(

    select roleid,partyid,memberagreementid,editablestartdate,previncstartdate,previncenddate,dateadd(dd,1,previncenddate) currincstartdate,dateadd(mm,3,previncenddate) currincenddate

    from(

    select row_number() over (PARTITION BY roleid

    ORDER BY memberAgreementId desc) RN,roleid,partyid,memberagreementid,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end editablestartdate,

    case when dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end )) >= @today

    then

    dateadd(mm,-3,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,dateadd(mm,-1,@today))/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end))

    else

    dateadd(mm,-3,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ))

    end previncstartdate,

    case when dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ) )>= @today

    then

    dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,dateadd(mm,-1,@today))/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ))

    else

    dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ) )

    end previncenddate

    from memberagreement

    ) previncedates

    where rn = 1

    )

    select * from incentivedates

    memberagreement.memberagreementid joins suspension.targetentityid

    The part about the 9/4/2007 date is that the reward period was reset for for agreements starting before 9/4/2007.

    the proposed solution is to put the baseline period dates in a temp table and run a cursor or loop over the suspension table to update the dates based on the number of days the suspension. It would irk me to do that but a set based solution has evaded me thus far.

    Final result set

    roleid,partyid,memberagreementid,editablestartdate,previncstartdate,previncenddate,currincstartdate,currincenddate

    777,555,12345,1/22/2016,5/30/2016,9/7/2016,9/8/2016,1/17/2017

    If run in a cursor

    result of first suspension

    roleid,partyid,memberagreementid,editablestartdate,previncstartdate,previncenddate,currincstartdate,currincenddate

    777,555,12345,1/22/2016,5/10/2016,8/8/2016,8/9/2016,11/8/2016

    result of second suspension

    roleid,partyid,memberagreementid,editablestartdate,previncstartdate,previncenddate,currincstartdate,currincenddate

    777,555,12345,1/22/2016,5/30/2016,8/28/2016,8/29/2016,11/28/2016

    result of third suspension

    roleid,partyid,memberagreementid,editablestartdate,previncstartdate,previncenddate,currincstartdate,currincenddate

    777,555,12345,1/22/2016,5/30/2016,9/7/2016,9/8/2016,12/8/2016

    result of fourth suspension

    roleid,partyid,memberagreementid,editablestartdate,previncstartdate,previncenddate,currincstartdate,currincenddate

    777,555,12345,1/22/2016,5/30/2016,9/7/2016,9/8/2016,12/28/2016

    result of fifth suspension

    roleid,partyid,memberagreementid,editablestartdate,previncstartdate,previncenddate,currincstartdate,currincenddate

    777,555,12345,1/22/2016,5/30/2016,9/7/2016,9/8/2016,1/17/2017


  • Quick questions

    😎

    1) By activity period of 3 months, do you mean three full calendar months, i.e. 1st. January to 1st. April, including both start and end dates?

    2) Does the duration of each suspension include both the start and end dates, i.e. 1st. February to 10th. February would be a duration of 10 days?

    3) If the suspension in 2) is applied to the activity period in 1), would the actual end date be 11th April?

    This query will aggregate the duration of the suspensions and extend the activity period's end date accordingly.

    ;WITH BASE_DATA AS

    (

    SELECT

    DMA.partyid

    ,DMA.roleid

    ,DMA.memberagreementid

    ,DMA.editablestartdate AS INITIAL_START_DATE

    ,CASE

    WHEN DMA.editablestartdate < convert(datetime,'09/04/2007',101) THEN convert(datetime,'09/04/2007',101)

    ELSE DMA.editablestartdate

    END AS ACTUAL_START_DATE

    FROM dbo.memberagreement DMA

    )

    ,INITIAL_AGREEMENT_START_END AS

    (

    SELECT

    BD.partyid

    ,BD.roleid

    ,BD.memberagreementid

    ,BD.INITIAL_START_DATE

    ,BD.ACTUAL_START_DATE

    ,DATEADD(MONTH,3,BD.ACTUAL_START_DATE) AS INITIAL_END_DATE

    FROM BASE_DATA BD

    )

    ,SUSPENSION_AND_DURATION AS

    (

    SELECT

    SUS.suspensionid

    ,SUS.targetentityid

    ,SUS.begintime

    ,SUS.endtime

    ,DATEDIFF(DAY,SUS.begintime,DATEADD(DAY,1,SUS.endtime)) AS SUSPENSION_DURATION

    ,SUM(DATEDIFF(DAY,SUS.begintime,DATEADD(DAY,1,SUS.endtime))) OVER

    (

    PARTITION BY SUS.targetentityid

    ORDER BY SUS.begintime ASC

    ROWS BETWEEN UNBOUNDED PRECEDING

    AND CURRENT ROW

    ) AS ACCUM_SUSP_DURATION

    ,COUNT(SUS.targetentityid) OVER

    (

    PARTITION BY SUS.targetentityid

    ORDER BY SUS.begintime ASC

    ROWS BETWEEN UNBOUNDED PRECEDING

    AND CURRENT ROW

    ) AS ACCUM_COUNT_SUSP

    FROM dbo.suspension SUS

    )

    SELECT

    IASE.partyid

    ,IASE.roleid

    ,IASE.memberagreementid

    ,IASE.INITIAL_START_DATE

    ,IASE.ACTUAL_START_DATE

    ,IASE.INITIAL_END_DATE

    ,SAD.suspensionid

    ,SAD.begintime

    ,SAD.endtime

    ,SAD.SUSPENSION_DURATION

    ,SAD.ACCUM_SUSP_DURATION

    ,SAD.ACCUM_COUNT_SUSP

    ,DATEADD(DAY,SAD.ACCUM_SUSP_DURATION,IASE.INITIAL_END_DATE) AS NEW_END_DATE

    FROM INITIAL_AGREEMENT_START_END IASE

    LEFT OUTER JOIN SUSPENSION_AND_DURATION SAD

    ON IASE.memberagreementid = SAD.targetentityid

    WHERE SAD.targetentityid = 12345;

    Output using the sample data

    partyid roleid memberagreementid INITIAL_START_DATE ACTUAL_START_DATE INITIAL_END_DATE suspensionid begintime endtime SUSPENSION_DURATION ACCUM_SUSP_DURATION ACCUM_COUNT_SUSP NEW_END_DATE

    ----------- ----------- ----------------- ----------------------- ----------------------- ----------------------- ------------ ----------------------- ----------------------- ------------------- ------------------- ---------------- -----------------------

    555 777 12345 2016-01-22 00:00:00.000 2016-01-22 00:00:00.000 2016-04-22 00:00:00.000 1 2016-03-12 00:00:00.000 2016-03-30 00:00:00.000 19 19 1 2016-05-11 00:00:00.000

    555 777 12345 2016-01-22 00:00:00.000 2016-01-22 00:00:00.000 2016-04-22 00:00:00.000 2 2016-05-10 00:00:00.000 2016-05-30 00:00:00.000 21 40 2 2016-06-01 00:00:00.000

    555 777 12345 2016-01-22 00:00:00.000 2016-01-22 00:00:00.000 2016-04-22 00:00:00.000 3 2016-07-10 00:00:00.000 2016-07-20 00:00:00.000 11 51 3 2016-06-12 00:00:00.000

    555 777 12345 2016-01-22 00:00:00.000 2016-01-22 00:00:00.000 2016-04-22 00:00:00.000 4 2016-09-10 00:00:00.000 2016-09-30 00:00:00.000 21 72 4 2016-07-03 00:00:00.000

    555 777 12345 2016-01-22 00:00:00.000 2016-01-22 00:00:00.000 2016-04-22 00:00:00.000 5 2016-11-10 00:00:00.000 2016-11-30 00:00:00.000 21 93 5 2016-07-24 00:00:00.000

  • The incentive periods are 3 calendar months if there are no suspensions not including the end date in your example.

    If an agreement starts on 1/1/2016 then the baseline incentive periods would run like this

    incstartincend

    01/01/201603/31/2016

    04/01/201606/30/2016

    07/01/201609/30/2016

    10/01/201612/31/2016

    01/01/201703/31/2017

    04/01/201706/30/2017

    07/01/201709/30/2017

    10/01/201712/31/2017

    the suspension would not include the end date as that is the date of return. so 2/1/16 to 2/10/16 would be 9 days of suspension.

    changing the period to :

    01/01/201604/09/2016

    04/10/2016 07/09/2016


  • mrpolecat (9/25/2016)


    The incentive periods are 3 calendar months if there are no suspensions not including the end date in your example.

    If an agreement starts on 1/1/2016 then the baseline incentive periods would run like this

    incstartincend

    01/01/201603/31/2016

    04/01/201606/30/2016

    07/01/201609/30/2016

    10/01/201612/31/2016

    01/01/201703/31/2017

    04/01/201706/30/2017

    07/01/201709/30/2017

    10/01/201712/31/2017

    the suspension would not include the end date as that is the date of return. so 2/1/16 to 2/10/16 would be 9 days of suspension.

    changing the period to :

    01/01/201604/09/2016

    04/10/2016 07/09/2016

    Just change the CTEs to adjust the periods.

    😎

    Question, can each entity have multiple periods?

  • Each entity has successive periods. They can continue forever.

    Your CTE is adding the total suspension duration in the first period. The solution must apply the suspension to the correct period and adjust the start and end dates of the following period (with suspensions occurring in that period applied) as indicated in the result set I supplied in the initial post.


  • Here is what I am looking at which provides the proper results but still seems too RBAR.

    declare @today datetime = '9/23/2016'

    declare @rc int

    declare @complete table (suspid int);

    declare @id table (roleid varchar(50),partyid int,memberagreementid int,editablestartdate datetime,previncstartdate datetime,previncenddate datetime,currincstartdate datetime, currincenddate datetime,suspid int);

    -- if (object_id('tempdb..#id') is not null) drop table #id;

    -- if (object_id('tempdb..#complete') is not null) drop table #complete;

    -- select 0 suspid into #complete

    ;with

    incentivedates as(

    select roleid,partyid,memberagreementid,editablestartdate,previncstartdate,previncenddate,dateadd(dd,1,previncenddate) currincstartdate,dateadd(mm,3,previncenddate) currincenddate

    from(

    select row_number() over (PARTITION BY roleid

    ORDER BY memberAgreementId desc) RN,roleid,partyid,memberagreementid,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end editablestartdate,

    case when dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end )) >= @today

    then

    dateadd(mm,-3,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,dateadd(mm,-1,@today))/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end))

    else

    dateadd(mm,-3,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ))

    end previncstartdate,

    case when dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ) )>= @today

    then

    dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,dateadd(mm,-1,@today))/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ))

    else

    dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ) )

    end previncenddate

    from memberagreement

    ) previncedates

    where rn = 1

    )

    insert into @id select *,0 from incentivedates

    select * from @id

    --

    set @rc = 1

    while @rc > 0

    begin

    update id

    set previncstartdate = dateadd(dd,datediff(dd,s.begintime,s.endtime),previncstartdate)

    ,previncenddate = dateadd(dd,datediff(dd,s.begintime,s.endtime),previncenddate)

    ,currincstartdate = dateadd(dd,datediff(dd,s.begintime,s.endtime),currincstartdate)

    ,currincenddate = dateadd(dd,datediff(dd,s.begintime,s.endtime),currincenddate)

    ,suspid = s.suspensionid

    output inserted.suspid into @complete

    --select *

    from @id id

    join suspension s on memberagreementid = targetentityid

    and endtime <= previncenddate and begintime <= previncstartdate

    and s.suspensionid not in (select suspid from @complete)

    set @rc = @@ROWCOUNT

    end

    --select * from @id

    --

    set @rc = 1

    while @rc > 0

    begin

    update id

    set previncenddate = dateadd(dd,datediff(dd,s.begintime,s.endtime),previncenddate)

    ,currincstartdate = dateadd(dd,datediff(dd,s.begintime,s.endtime),currincstartdate)

    ,currincenddate = dateadd(dd,datediff(dd,s.begintime,s.endtime),currincenddate)

    ,suspid = s.suspensionid

    output inserted.suspid into @complete

    --select *

    from @id id

    join suspension s on memberagreementid = targetentityid

    and endtime <= currincenddate and begintime <= previncenddate

    and s.suspensionid not in (select suspid from @complete)

    set @rc = @@ROWCOUNT

    end

    --select * from @id

    --

    set @rc = 1

    while @rc > 0

    begin

    update id

    set currincenddate = dateadd(dd,datediff(dd,s.begintime,s.endtime),currincenddate)

    ,suspid = s.suspensionid

    output inserted.suspid into @complete

    --select *

    from @id id

    join suspension s on memberagreementid = targetentityid

    and endtime <= currincenddate and begintime >= currincstartdate

    and s.suspensionid not in (select suspid from @complete)

    set @rc = @@ROWCOUNT

    end

    --select * from @id

    select * from @id

    Also added soem more test data to my test tabes

    select 555 partyid,777 roleid, 12345 memberagreementid,convert(datetime,'1/22/2016') editablestartdate

    into memberagreement

    union all

    select 556 partyid,778 roleid, 12346 memberagreementid,convert(datetime,'2/15/2016') editablestartdate

    union all

    select 557 partyid,779 roleid, 12347 memberagreementid,convert(datetime,'1/5/2016') editablestartdate

    select

    1 suspensionid,12345 targetentityid, convert(datetime,'3/12/2016') begintime, convert(datetime,'3/30/2016') endtime,'N' complete

    into suspension

    union all

    select 2,12345,'5/10/2016','5/30/2016','N'

    union all

    select 3,12345,'7/10/2016','7/20/2016','N'

    union all

    select 4,12345,'9/10/2016','9/30/2016','N'

    union all

    select 5,12345,'11/10/2016','11/30/2016','N'

    union all

    select 6,12346,'5/10/2016','5/30/2016','N'

    union all

    select 7,12346,'7/10/2016','7/20/2016','N'

    union all

    select 8,12346,'9/10/2016','9/30/2016','N'

    union all

    select 9,12346,'11/10/2016','11/30/2016','N'


  • >> I have a set of agreements that have a date the agreement was started. From those agreements I track activities that occur during 3 month periods starting from the start date. I calculate those periods on the fly to report the number of activities that occurred during the current period and the previous period. <<

    This is not how we write SQL; this is basically 1970's COBOL written in T-SQL dialect along with some really weird data modeling.

    Do you understand that SQL is a database language, we do not like doing calculations. We look things up with tables. we do not calculate things "on the fly" because we have a firm data model that we know about in advance. We set our little make-believe universe up one time and go with it.

    Since you did not bother to post any DDL (please read the forum rules), we have to make some guesses about keys and everything else. But you failed to use the ANSI ISO standard format for dates, use the old Sybase proprietary convert () function, and have an awful lot of dialect in your code. Let us start off of the idea of a table of report periods.

    https://www.simple-talk.com/sql/t-sql-programming/temporal-data-techniques-in-sql-/

    Build a look up table of reporting periods

    CREATE TABLE Report_Periods

    (report_name VARCHAR(30) NOT NULL PRIMARY KEY,

    report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,

    report_end_date DATE NOT NULL,

    CONSTRAINT date_ordering

    CHECK (report_start_date <= report_end_date),

    etc);

    These reports can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them;etc.

    I like the MySQL convention of using zeroes in the ISO-8601 display format because it sorts correctly and is language independent. This uses "yyyy-mm-00" for months within a year and "yyyy-00-00" for entire years.

    The basic skeleton for use with these tables is

    SELECT R.report_name, << summary computations >>

    FROM ReportRanges AS R, Events AS E

    WHERE E.event_date BETWEEN R.report_start_date AND report_end_date

    AND R.report_name IN (<<report name list>>)

    GROUP BY R.report_name;

    The next concept you seem to have missed is that of "<something>_status" during those time slots. The ISO model of time is based on half open intervals. We know the initial or start time and the terminal or endding time can be a precise point in time (which is never actually reached, but forms a limit) or is a null, if the status is still current. download the Rick Snodgrass book on temporal queries in SQL; it is a free PDF from the University of Arizona.

    >> The tricky part is that the when this happens, the duration of the periods needs to be extended so the person has the full 3 months to collect their activities and be rewarded. <<

    No, it is not tricky at all. this is a standard SQL idiom. But you need to use some constraints to guarantee that the durations do not overlap and are contiguous. Google around you will find constraints for guaranteeing this. the Google search for this will be "https://www.simple-talk.com/author/alex-kuznetsov"

    >> SELECT 555 AS party_id, 777 AS role_id, 12345 memberagreement_id, CAST('2016-01-22' AS DATE) AS editable_start_date

    INTO member_agreement –-fake scratch tape!

    .. <<

    This piece of code has all kinds of problems. Identifiers (I assume that is what you meant by "_id") are never numerics; you do not do math with them! This looks like Kabbalah magic; this is an old Hebrew superstition that God assigns a number to every entity in creation, and if you know it is special Kabbalah number, you have all kinds of magical powers over it. You can animate a golem, etc. this is absolutely the antithesis of RDBMS and logic.

    We do not use the old Sybase convert () string function today. T-SQL now has the ANSI/ISO standard cast () function. You also used the old Sybase insertion syntax that has been overridden for many many years.

    Back when we had magnetic tapes, we had to materialize the data on either punchcards or tapes to use it. This is where Sybase got the "SELECT ..INTO.." proprietary feature. Besides having names that violate ISO 11179 rules, what you are really doing is faking scratch tapes. if you do not know the correct syntax. Here is a skeleton:

    INSERT INTO Foobar

    VALUE

    (2, 12345, '2016-10-05', '2016-05-30'),

    (3, 12345, '2016-10-07', '2016-07-20'),

    (4, 12345, '2016-10-08', '2016-09-30'),

    (5, 12345, '2016-11-10', '2016-11-30');

    >> the proposed solution is to put the baseline period dates in a temp table [scratch tapes, complete with a row_number () to fake sequential record numbers of a tape] and run a cursor or loop over the suspension table to update the dates based on the number of days the suspension. It would irk me to do that but a set based solution has evaded me thus far. <<

    I assume you are aware that the word "cursor" is derived from the root word "curse" to SQL programmers? 😀 Using the cursor says that your design is wrong. Yes, it is possible that a cursor can be a solution. In 30+ years with SQL, I have written five myself; I know I could have gotten rid of three of them if we had the current features of ANSI/ISO standard SQL.

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL

  • mrpolecat (9/25/2016)


    The incentive periods are 3 calendar months if there are no suspensions not including the end date in your example.

    If an agreement starts on 1/1/2016 then the baseline incentive periods would run like this

    incstartincend

    01/01/201603/31/2016

    04/01/201606/30/2016

    07/01/201609/30/2016

    10/01/201612/31/2016

    01/01/201703/31/2017

    04/01/201706/30/2017

    07/01/201709/30/2017

    10/01/201712/31/2017

    the suspension would not include the end date as that is the date of return. so 2/1/16 to 2/10/16 would be 9 days of suspension.

    changing the period to :

    01/01/201604/09/2016

    04/10/2016 07/09/2016

    Do you always have the periods started from ast of month?

    Is there a possibility to have it started from, say

    11/28/2016

    11/29/2016

    11/30/2016

    ?

    What would be the last days for periods starting from these days?

    How a 2 days suspension would affect the last day of a period started on 11/28/2016?

    _____________
    Code for TallyGenerator

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply