Query Help

Posted on 2005-04-27
Medium Priority
Last Modified: 2010-03-19
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
Question by:dbashley1
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13880778
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.
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13880797
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 :-) .

Author Comment

ID: 13880836

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.

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

LVL 17

Accepted Solution

BillAn1 earned 1000 total points
ID: 13880893
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
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13881129
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.
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13881167
OOPS, do need a temp table to store the results so they can be reported as a whole.
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 13881242
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
LVL 30

Expert Comment

ID: 13882015
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.

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 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