March 19, 2011 at 1:50 am
Hi,
I have to get calendar due date tasks based on recurrence.
recurrence will be
Daily
Weekly
Monthly
Suppose my table is like
TaskID TaskSubect Startdate Enddate Occurance
1 Test task 12-03-2011 29-03-2011 weekly
so for above task recurrence are 12, 12+7=19, 19+7=26, 26+7=1st April(this is > end date) will not occure.
so recurrence are 12,19,26.
While displaying
today date is 12-03-2011 then due date should 12-03-2011
today date is 13-03-2011 then due date should 19-03-2011
today date is 20-03-2011 then due date should 26-03-2011
today date is 27-03-2011 then no recurrance will come because as end date is 29th.
Could you please help me out how to achieve with sql query.
March 19, 2011 at 7:49 pm
Here is one way of doing it. In this, I am not trying to be efficient or write compact code; instead I am trying to make my logic clear.
declare @tbl table
(
TaskId int,
TaskSubsect varchar(31),
StartDate date,
EndDate date,
Occurrence varchar(31)
);
insert into @tbl values
(1,'Task1','20110115','20110420','Monthly'),
(2,'Task1','20110118','20110420','Monthly'),
(3,'Task1','20110119','20110420','Monthly'),
(4,'Task1','20110121','20110420','Monthly'),
(5,'Task1','20110121','20110220','Monthly'),
(6,'Task1','20110621','20110925','Monthly');
declare @date datetime = '20110319';
--cte1 : finds the recurrence date that is in the same period as @date. may be earlier than @date
--cte2 : finds the next recurrence date after the one found in cte1.
--cte3 : picks the recurrence date that is >= to @date
--final select: picks the date found in cte3 if it is on or before enddate.
with cte1 as
(
select
*,
case
when Occurrence = 'Monthly' then dateadd(mm,datediff(mm,StartDate,@date),StartDate)
when Occurrence = 'Weekly' then dateadd(wk,datediff(wk,StartDate,@date),StartDate)
when Occurrence = 'Daily' then dateadd(dd,datediff(dd,StartDate,@date),StartDate)
end as date1
from
@tbl
),
cte2 as
(
select
*,
case
when Occurrence = 'Monthly' then dateadd(mm,1,date1)
when Occurrence = 'Weekly' then dateadd(wk,1,date1)
when Occurrence = 'Daily' then dateadd(dd,1,date1)
end as date2
from
cte1
),
cte3 as
(
select
*,
case when date1 >= @date then date1 else date2 end as date3
from
cte2
)
select
*,
case when date3 > EndDate or date3 < StartDate then null else date3 end as NextRecurrence
from
cte3;
And, now that I think about it, all these cte's would not be required if you were interested only in "Daily" recurrence, but I like the symmetry of the code.
March 20, 2011 at 11:05 pm
Thanks for your reply.It did not work like as i mentinoed.
please provide any other alternative.
March 21, 2011 at 5:16 am
Ashok do one of two things (or may be both):
a) My code is long and drawn out, specifically so the logic is clear. Look through the code and make the changes to fix the problem if you are able to.
b) Reply with a set of input data that can be used to populate the test table in my example so I can see the output produces, and also tell me what the output you expect is.
April 9, 2011 at 12:20 pm
I have been planning to write the code for this requirement since i saw this thread. But time dint permit me. But now, it has 😀
Lets first set-up the test environment :
SET NOCOUNT ON
GO
IF OBJECT_ID('TempDB..#Recurrence') IS NOT NULL
DROP TABLE #Recurrence
GO
CREATE TABLE #Recurrence
(
TaskId INT,
TaskSubsect VARCHAR(31),
StartDate DATETIME,
EndDate DATETIME,
Occurrence VARCHAR(31)
);
GO
INSERT INTO #Recurrence VALUES
(1,'Task1','12-MAR-2011','29-MAR-2011','weekly'),
(2,'Task2','21-Mar-2011','14-Nov-2011','Monthly'),
(3,'Task3','01-Jan-2011','04-Jan-2011','Daily');
SELECT TaskId
,TaskSubsect
,StartDate
,EndDate
,Occurrence
FROM #Recurrence ;
Now for the code:
; WITH Tens (N) AS
(
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
)
, Hundreds (N) AS
(
SELECT T1.N FROM Tens T1 CROSS JOIN Tens T2
)
, Thousands (N) AS
(
SELECT T1.N FROM Hundreds T1 CROSS JOIN Hundreds T2
)
, NumbersTable(N) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) FROM Thousands
)
SELECT RT.TaskId , RT.TaskSubsect , RT.Occurrence ,
TodaysDate =
CASE
WHEN RT.Occurrence = 'Daily' THEN DATEADD(DD,( NT.N -1 ), RT.StartDate)
WHEN RT.Occurrence = 'Weekly' THEN DATEADD(WK,( NT.N -1 ), RT.StartDate+1)
WHEN RT.Occurrence = 'Monthly' THEN DATEADD(MM,( NT.N -1 ), RT.StartDate+1)
END ,
DueDate = CASE
WHEN RT.Occurrence = 'Daily' THEN DATEADD(DD,( NT.N -1 ), RT.StartDate)
WHEN RT.Occurrence = 'Weekly' THEN CASE WHEN DATEADD(WK,( NT.N ), RT.StartDate) > RT.EndDate THEN RT.EndDate
ELSE DATEADD(WK,( NT.N ), RT.StartDate)
END
WHEN RT.Occurrence = 'Monthly' THEN CASE WHEN DATEADD(MM,( NT.N ), RT.StartDate) > RT.EndDate THEN RT.EndDate
ELSE DATEADD(MM,( NT.N ), RT.StartDate)
END
END
FROM NumbersTable NT
CROSS JOIN #Recurrence RT
WHERE
NT.N <= CASE WHEN RT.Occurrence = 'Daily' THEN DATEDIFF(DD,RT.StartDate , RT.EndDate) + 1
WHEN RT.Occurrence = 'Weekly' THEN DATEDIFF(WK,RT.StartDate , RT.EndDate)
WHEN RT.Occurrence = 'Monthly' THEN DATEDIFF(MM,RT.StartDate , RT.EndDate)
END
ORDER BY RT.TaskId , NT.N
Hope that helps 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply