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
dbashley1Asked:
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.


select
      a.idfacility,
      a.ts startTime,
      (select min(ts) from (      select distinct idfacility, starttime ts from reservations
      union
      select distinct idfacility, endtime ts from reservations) c where c.ts > a.ts and c.idfacility = a.idfacility) endTime,
      sum(participants) participants
from
(
      select distinct idfacility, starttime ts from reservations
      union
      select distinct idfacility, endtime ts from reservations
) a
join
(
      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
union
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
begin
      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
end
close mycurs
deallocate mycurs

select * from #tempRunningTotals where endTime is not null
order by 1,2
0
 
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.
0
 
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 :-) .
0
Get your problem seen by more experts

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

 
dbashley1Author Commented:
Scott,

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.

Thanks
0
 
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.
0
 
Scott PletcherSenior DBACommented:
OOPS, do need a temp table to store the results so they can be reported as a whole.
0
 
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
)

DECLARE reservCsr CURSOR FOR
SELECT idFacility, startTime, endTime, Participants
FROM reservations
ORDER BY idFacility, startTime, endTime

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

OPEN reservCsr

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

CLOSE reservCsr
DEALLOCATE reservCsr

SELECT *
FROM #results
0
 
nmcdermaidCommented:
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.
0
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.