Solved

How to calculate a total across multiple overlapping values where at least one condition is true

Posted on 2006-11-17
3
203 Views
Last Modified: 2011-04-14
Now this is a tricky one and requires a good mathematical mind!

I have a table containing data, e.g.

id          campaign    start_datetime                                         duration    
----------- ----------- ------------------------------------------------------ -----------
      11086          25 2006-11-16 12:58:35.000                                       1326
      11087          22 2006-11-16 12:58:36.000                                       1321
      11088          19 2006-11-16 12:58:38.000                                       1316
      11089          16 2006-11-16 12:58:40.000                                       1309
      11090          13 2006-11-16 12:58:41.000                                         11
      11091          10 2006-11-16 12:58:43.000                                       1302
      11092          25 2006-11-16 13:24:33.000                                       4190
      11093          22 2006-11-16 13:24:34.000                                       4186
      11094          19 2006-11-16 13:24:36.000                                       4180
      11095          16 2006-11-16 13:24:37.000                                       4174
      11096          10 2006-11-16 13:24:39.000                                       4168
      11097          25 2006-11-16 14:42:36.000                                        483
      11098          22 2006-11-16 14:42:38.000                                        477
      11099          19 2006-11-16 14:42:39.000                                        473
      11100          16 2006-11-16 14:42:42.000                                        466
      11101          10 2006-11-16 14:42:43.000                                        461
      11102          25 2006-11-16 14:54:08.000                                       3159
      11103          22 2006-11-16 14:54:10.000                                       3154
      11104          19 2006-11-16 14:54:11.000                                       3147
      11105          16 2006-11-16 14:54:13.000                                       3141
      11106          10 2006-11-16 14:54:15.000                                       3131
      11107          13 2006-11-16 15:07:40.000                                       2330
      11108          25 2006-11-16 15:54:08.000                                       1009
      11109          22 2006-11-16 15:54:10.000                                       1003
      11110          19 2006-11-16 15:54:11.000                                        998
      11111          16 2006-11-16 15:54:13.000                                        993
      11112          13 2006-11-16 15:54:15.000                                        987
      11113          10 2006-11-16 15:54:16.000                                        973
      11115          13 2006-11-16 15:54:47.000                                        938
 etc

It describes when a campaign was started and how long in seconds it was live for.

This data can be summarised using the following example graph (not based on table above):

Time:  12__13__14__15__16__17__18__19__20
                   |----|                                                    Campaign 1 (13-14)
                       |------------|                                      Campaign 2 (13.30-16)
                             |------|                                        Campaign 3 (14.15-15.55)
                                               |----------|                 Campaign 4 (17-19)
                                                         |-----|             Campaign 5 (18.30-19.30)

What I am after is the amount of time across a particular dataset that AT LEAST one campaign was open. Using the graph above this would be 13.00 to 16.00 and 17.00 to 19.30 = 5.5 hours. Campaign 3 will not add to the figure as it overlaps with Camapign 2. Between 16.00 and 17.00 no campaign was live so will not be counted. And so on.

How given the table above could I write a SQL statement to give me this figure? A stored prodecure / function would also do if this cannot be expressed in a single SQL statement.

Top marks to whoever get the answer!

Thanks
0
Comment
Question by:kaiwhoeatspie
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
drydenhogg earned 500 total points
Comment Utility
I tried to think of a nice way to do this, and failed, so I came up with a nasty way, well perhaps not too nasty but pretty manual!

I created the table and used some combinations to test a few different options for overlaps. the following SQL certainly managed to print the correct answer for test but I didn't test the lot. I was not sure as to whether duration had any impact or not, so for time being have left it out, from your little diagram it seemed to matter not.

Basics are create a temp table,
use a cursor to loop through your existing table's records, grouped by campiang, getting max and min dates.
look for an overlapping range, if you dont find one insert, otherwise update the range record in temp you match to be extended. the updates it runs there are crude, but works.
Once you get to the end of that process the temp table can have a datediff in minutes applied (if that resolution is suitable, if not change it) it returns the number of mintues which I divided by 60 and printed, giving me 2.5


create table #test (startdate datetime,enddate datetime)

declare maintable cursor
for
select min(start_datetime) as min, max(start_datetime) as max
from tblcampaign
group by campaign

open maintable

declare @startdate datetime, @enddate datetime
fetch next from maintable into @startdate, @enddate
while (@@FETCH_STATUS <> -1)
BEGIN
      if (select count(*) from #test where
            (@startdate between startdate and enddate)
            OR
            (@enddate between startdate and enddate)
            ) = 0
            begin
                  insert into #test(startdate, enddate) values(@startdate,@enddate)
            end
      else
            begin
                  update #test set startdate=@startdate where
                        startdate > @startdate
                        AND
                        (
                              (startdate between @startdate and @enddate)
                        )
            
                  update #test set enddate=@enddate where
                        enddate < @enddate
                        AND
                        (
                              (enddate between @startdate and @enddate)
                        )
            end
      

      fetch next from maintable into @startdate, @enddate
END

declare @hours decimal

set @hours = (select sum(datediff(n,startdate,enddate)) as temp from #test)

print @hours/60

close maintable
deallocate maintable
drop table #test


hth
0
 

Author Comment

by:kaiwhoeatspie
Comment Utility
Thanks for your answer drydenhogg. I will test this and get back to you very soon!
0
 

Author Comment

by:kaiwhoeatspie
Comment Utility
Yes drydenhogg, your solution guided me in the right direction.

I found that I needed to sort the maintable cursor by start_datetime to make it work properly, and also use a derived "end_datetime" based on the "start_datetime" and "duration":

declare maintable cursor
for
select min(start_datetime) as min, max(DATEADD(s, duration, start_datetime)) as max
from tblcampaign
group by campaign
order by start_datetime

All points to you!

Thanks
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now