Query Help

Posted on 2005-04-27
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 68

    Expert Comment

    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 68

    Expert Comment

    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


    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.

    LVL 17

    Accepted Solution

    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 68

    Expert Comment

    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 68

    Expert Comment

    OOPS, do need a temp table to store the results so they can be reported as a whole.
    LVL 68

    Assisted Solution

    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
    DEALLOCATE reservCsr

    SELECT *
    FROM #results
    LVL 30

    Expert Comment

    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

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    755 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

    16 Experts available now in Live!

    Get 1:1 Help Now