September 24, 2016 at 5:27 pm
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
September 25, 2016 at 4:00 am
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
September 25, 2016 at 5:20 am
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
September 25, 2016 at 7:35 am
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?
September 25, 2016 at 8:09 am
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.
September 25, 2016 at 6:59 pm
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'
September 26, 2016 at 9:25 am
>> 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
October 5, 2016 at 3:14 pm
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