Query Help

I am making a timline reporting tool for reservations.

There is the reservation data
idReservation                idFacility             startTime                                 endTime                         Participants
1                                   1                       1/1/2005 10:00 AM                  1/6/2005  11:00 AM              10
2                                   1                       1/3/2005 3:00 PM                    1/8/2005  3:00 PM                25
3                                   2                       1/1/2005 2:00 AM                    1/4/2005  4:00 PM                5

I need to write a query that returns information summing the participants where reservations overlap
An example of the results I want is......................
idFacility             startTime                           endTime                     Participants
1                        1/1/2005 10:00 AM            1/3/2005 3:00 PM                 10
1                        1/3/2005 3:00 PM              1/6/2005  11:00 AM              35
1                        1/6/2005  11:00 AM           1/8/2005  3:00 PM                25
2                        1/1/2005 2:00 AM              1/4/2005  4:00 PM                 5

I know this is some sort of self join....but that is as far as I have gotten
Who is Participating?
BillAn1Connect With a Mentor Commented:
Here's a query that will do it with joins. However I suspect for more than a few records, this may be quite poor performance.
This is one of those cases where a cursor may actually be the more efficient method.

You first need to split each record into 2 seperate transactions - one that adds X particpants at startTime, and a second that subtracts X participants at the endTime. Then you need to order all these transactions, and get a running total of the participants.

      a.ts startTime,
      (select min(ts) from (      select distinct idfacility, starttime ts from reservations
      select distinct idfacility, endtime ts from reservations) c where c.ts > a.ts and c.idfacility = a.idfacility) endTime,
      sum(participants) participants
      select distinct idfacility, starttime ts from reservations
      select distinct idfacility, endtime ts from reservations
) a
      select idfacility, startTime ts, participants from reservations
      union all
      select idfacility, endTime ts, -participants from reservations
) b
on b.idfacility = a.idfacility and b.ts <= a.ts
group by a.idfacility, a.ts
having sum(participants) > 0
order by 1,2

the cursor method will look like this :

create table #tempReservations (idFacility integer, ts datetime, participants integer)

insert into #tempReservations
select idFacility, startTime, participants from reservations
select idFacility, endTime, -participants from reservations

create table #tempRunningTotals(idfacility integer, startTime datetime, endtime datetime, participants integer)

declare @total integer
set @total = 0
declare @lastid integer
set @lastid = -1
declare @idFacility integer
declare @ts datetime
declare @participants integer

declare mycurs cursor
for select idFacility, ts, participants
from #tempReservations order by 1,2

open mycurs
fetch next from mycurs into @idFacility, @ts, @participants
while @@fetch_status <> -1
      if @lastid <> @idfacility set @total = @participants
      else set @total = @total + @participants
      set @lastid = @idfacility
      update #tempRunningTotals set endtime = @ts where endtime is null and idfacility = @idfacility
      insert into #tempRunningTotals values (@idfacility, @ts, NULL, @total)
      fetch next from mycurs into @idFacility, @ts, @participants
close mycurs
deallocate mycurs

select * from #tempRunningTotals where endTime is not null
order by 1,2
Scott PletcherSenior DBACommented:
I really don't see any way to avoid a cursor here.  Sort the rows by idFacility, startTime and endTime, then use table variables/other T-SQL logic to accumulate the totals.
Scott PletcherSenior DBACommented:
If you would be willing to use that type of logic, let me know.

I know often people want *specifically* a SQL SELECT statement, with *no* cursor.  In general I too try to avoid cursors because of performance, but here I don't see a choice.  Maybe someone else does :-) .
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

dbashley1Author Commented:

Yes, I would like to avoid a cursor if possible.....but I'll take what I can get.
Can you get me started on the logic using a cursor.  I'm not really able to visualize it.

Scott PletcherSenior DBACommented:
I wasn't thinking of a temp table or a UNION, just a "straight" ORDER BY.  The logic required is then more sophisticated but overhead is a lot less.

Will post a sample as soon as I can.
Scott PletcherSenior DBACommented:
OOPS, do need a temp table to store the results so they can be reported as a whole.
Scott PletcherConnect With a Mentor Senior DBACommented:
Have to go home now, will post the overall logic, have to complete the "tricky"/sophisticated part tomorrow:

--overall results from all facilities
CREATE TABLE #results (
      idFacility INT,
      startTime DATETIME,
      endTime DATETIME,
      Participants INT
--"work area" for accumulating totals for each facility individually (emptied after each fac)
DECLARE @facility TABLE (
      startTime DATETIME,
      endTime DATETIME,
      Participants INT

SELECT idFacility, startTime, endTime, Participants
FROM reservations
ORDER BY idFacility, startTime, endTime

DECLARE @idFacility INT
DECLARE @participants SMALLINT
DECLARE @previousFacility INT
DECLARE @firstRowForFacility BIT
SET @firstRowForFacility = 1

OPEN reservCsr

FETCH NEXT FROM reservCsr INTO @idFacility, @startTime, @endTime, @participants
      IF @@FETCH_STATUS = 0
            IF @idFacility <> @previousFacility
                  INSERT INTO #results
                  SELECT @previousFacility, startTime, endTime, Participants
                  FROM @facility
                  DELETE FROM @facility
                  SET @previousFacility = @idFacility
            END --IF                  
      END --IF
      IF @firstRowForFacility = 1
            INSERT INTO @facility VALUES(@startTime, @endTime, @participants)
      END --IF
            /* remaining (SOPHISTICATED) logic goes here */
      END --ELSE

CLOSE reservCsr

FROM #results
I wonder if there is a way to join your table to a static calendar table (with every day as a row), so that you get a bunch of transaction lines, then just summarise those transactional lines by the date.

Timestamp may mess that idea up though.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.