February 19, 2009 at 12:18 pm
Here's what I was able to come up with so far ... do you guys think there is a way to simplify this?
declare @event table (id int identity(1,1), beginDate datetime, endDate datetime)
declare @range table (id int identity(1,1), beginDate datetime, endDate datetime)
declare @tally table (section varchar(10))
insert into @tally values('precede')
insert into @tally values('middle')
insert into @tally values('exceed')
insert into @range values('2/1/2009', '2/28/2009')
insert into @event values('1/20/2009','3/10/2009')--encompasses range
insert into @event values('1/20/2009','2/15/2009')--precedes range
insert into @event values('2/15/2009','3/10/2009')--exceeds range
insert into @event values('2/10/2009','2/20/2009')--within range
select
e.id,
e.beginDate as originalBegin,
e.endDate as originalEnd,
case
--when event is bigger than the range
when e.beginDate r.endDate then
case
when t.section = 'precede' then e.beginDate
when t.section = 'middle' then r.beginDate
when t.section = 'exceed' then r.endDate end
--when event is before the range
when e.beginDate < r.beginDate and e.endDate < r.endDate then
case
when t.section = 'precede' then e.beginDate
when t.section = 'middle' then r.beginDate
when t.section = 'exceed' then null end
--when event extends beyond the range
when e.beginDate > r.beginDate and e.endDate > r.endDate then
case
when t.section = 'precede' then null
when t.section = 'middle' then e.beginDate
when t.section = 'exceed' then r.endDate end
--when event is completely within the range
else
case
when t.section = 'precede' then null
when t.section = 'middle' then e.beginDate
when t.section = 'exceed' then null end
end as periodStart,
case
--when event is bigger than the range
when e.beginDate r.endDate then
case
when t.section = 'precede' then r.beginDate
when t.section = 'middle' then r.endDate
when t.section = 'exceed' then e.endDate end
--when event is before the range
when e.beginDate < r.beginDate and e.endDate < r.endDate then
case
when t.section = 'precede' then r.beginDate
when t.section = 'middle' then e.endDate
when t.section = 'exceed' then null end
--when event extends beyond the range
when e.beginDate > r.beginDate and e.endDate > r.endDate then
case
when t.section = 'precede' then null
when t.section = 'middle' then r.endDate
when t.section = 'exceed' then e.endDate end
--when event is completely within the range
else
case
when t.section = 'precede' then null
when t.section = 'middle' then e.endDate
when t.section = 'exceed' then null end
end as periodEnd,
t.section
from
@event e, @range r, @tally t
February 19, 2009 at 12:34 pm
I'm not sure that this is precisely what you want, but if not you should be able to tweak it for your needs.
DECLARE @beginDate datetime
DECLARE @endDate datetime
SELECT @beginDate = '2009-02-01', @endDate = '2009-02-10'
SELECT
E.id,
section = 'precede',
periodStart = E.beginDate,
periodEnd = CASE WHEN E.endDate < @beginDate THEN E.endDate ELSE @beginDate END
FROM @event E
WHERE (E.beginDate < @beginDate)
UNION ALL
SELECT
E.id,
section = 'middle',
periodStart = CASE WHEN E.beginDate >= @beginDate THEN E.beginDate ELSE @beginDate END,
periodEnd = CASE WHEN E.endDate <= @endDate THEN E.endDate ELSE @endDate END
FROM @event E
WHERE (E.beginDate <= @endDate AND E.endDate >= @beginDate)
UNION ALL
SELECT
E.id,
section = 'exceed',
periodStart = CASE WHEN E.beginDate > @endDate THEN E.beginDate ELSE @endDate END,
periodEnd = E.endDate
FROM @event E
WHERE (E.endDate > @endDate)
ORDER BY id, periodStart
EDIT: Sorry just fixed up formatting of < and > characters so that they display properly in forum
February 19, 2009 at 12:46 pm
AnzioBake (10/23/2008)
Is this not a very Hard coded solution for the particular example that was given.Surely the more generic solution is not much more difficult and should be the prefered solution.? 🙂
AnzioBake (10/23/2008)
It seems I can not post code to the site, I wanted to post a more generic solution
[font="Verdana"]Actually, I would say that the "more generic solution" is to create a permanent Calendar table in the database. They are handy for so many different scenarios, including calculating month ends, month starts, date ranges, holidays/working days and so on.
[/font]
February 19, 2009 at 1:21 pm
Edit: I think your solution is good ... let me do some more tests ... if so, that would be awesome!
Edit-Edit: Your solution is pretty freakin' awesome! Yours is basically the simpler way to calculate this (compared to mine)- you win the Internet today!
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply