Do not count the same event twice 1

[graabein]

I have a function that checks how many minutes an event overlaps a certain period like this:

dbo.udf_CountMinutes(event_start, event_end, from, to)

When I have a very long event it can overlap two periods and I want to count the overlapping minutes on both ends, but I do not want to count the event twice!

Test data:

-- One event.
'2008-11-02 02:00', '2008-11-02 21:00'

-- Two periods.
'2008-11-01 20:00', '2008-11-02 04:00'
'2008-11-02 20:00', '2008-11-03 04:00'

Here I want number of events to say 1 and total time to say 180 minutes. Two hours off the start (04-02) and one at the end (21-20).

My query looks something like this:

select 
    ...,
    total = sum(
        case when dbo.udf_CountMinutes(e.From, e.To, p.From, p.To) > 0 
            then 1 else 0 end
        ),
    duration = sum(
        dbo.udf_CountMinutes(e.From, e.To, p.From, p.To)
        ),
    ...
from
    events e, #periods p
group by
    ...

The events have a unique row ID that I might use to avoid counting the same event twice. I'm just not sure how to do it.


graabein

 

[PWise]

try

Select eventid ,Datediff(n,eventStart ,StartPeriods.PeriodEnd)+
Datediff(n,EndPeriods.PeriodStart ,eventend)

from events
inner join Periods StartPeriods
on eventStart Between StartPeriods.PeriodStart and StartPeriods.PeriodEnd
inner join Periods EndPeriods
on eventEnd Between EndPeriods.PeriodStart and EndPeriods.PeriodEnd

 

[graabein]

You're brilliant! That worked just as I wanted it to. Going to study the code and learn what exactly it does. Thanks!


graabein

 

[graabein]

I've got a follow up question: This code doesn't seem to handle cases where events overlap start and end periods. I moved the second period to overlap the event but end before it like so: '2008-11-02 18:00', '2008-11-03 19:00'

In this case I want just the same numbers (1 event and 3 hours) but I miss the event with the 2nd between clause...


graabein

 

[graabein]

Sorry about the typo. It was supposed to be on the same date. 2008-11-02 from 18:00 to 19:00.


graabein