#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    111
    Rep Power
    16

    SUM date ranges SQL


    I hope this makes sense to everybody. I need to get the max number of books out at one time between a start and end date.

    Currently I am getting the total of all books in the booking with this query. I don't really know where to go from there. So any help would be greatly appreciated.

    I used the info on http://forums.devshed.com/ms-sql-dev...on-277324.html to fine tune my query.


    Code:
    SELECT SUM(quantity) as booktotal, id 
        FROM book_club_reservation
        WHERE reserved_set = #form.id#    
    	
        AND #form.dateend# >= date_add(reservation_start, INTERVAL -1 day) 
        AND #form.datestart# <= date_add(reservation_end, INTERVAL -1 day)

    Edit: I realize this doesn't even explain it... I was going to include a graphic with this but am having issues getting it right.
    Last edited by dsfx; January 3rd, 2014 at 10:44 AM.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,648
    Rep Power
    1945
    From you description and SQL, it looks like you want to get all books which has been reserved/booked in a specific time frame/intervan? Correct?

    How does the query result differ from your expectation?
    What kind of information does the the field quantity contains? I would expect that one row is one reservation.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    111
    Rep Power
    16
    Originally Posted by MrFujin
    From you description and SQL, it looks like you want to get all books which has been reserved/booked in a specific time frame/intervan? Correct?

    How does the query result differ from your expectation?
    What kind of information does the the field quantity contains? I would expect that one row is one reservation.
    The problem is I need to get the highest single number during that booking period. If there are overlapped dates then I need to get the total of books for all the overlapping bookings.


    The books come in sets of 20 and we allow patrons to borrow them based on their needs. So they may only take 9 or 4 out of that set. Which would leave a few left over to be borrowed.

    If we use my query listed above it creates a scenario where
    BOOKING A <!---------------------------------------------------------------->
    BOOKING B<!------------------------------------------------------------------>
    New Booking <!------------------------------------------------------------------------------------------------------------------->

    If Booking A has 9 copies on reserve

    Booking B has 4 copies on reserve

    And somebody tries to make a new booking between A and B then it will say 13 copies are on reserve but really it should only be 9 since A and B never overlap. This is simplified, usually there are multiple overlapping bookings but this should help paint the picture.

    Nothing like a good lunch to start to clear things up.
    Last edited by dsfx; January 3rd, 2014 at 12:17 PM.
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,648
    Rep Power
    1945
    To make it easier to read i decide to copy the nice illustration by r937
    Originally Posted by r937
    Code:
                 FS        FE             
                 |         |               
    1   SD---ED  |         |               
                 |         |               
    2         SD-|-ED      |               
                 |         |               
    3            | SD---ED |               
                 |         |               
    4            |      SD-|-ED 
                 |         |               
    5         SD-|---------|-ED       
                 |         |               
    6            |         |  SD---ED
    To have a better understanding, you have following steps to take into consideration.

    a. Get all reservation in the range FS-FE
    b. Get maximum quantity of non-overlapping reservation. For example between #2 and #4
    c. Add the quantity of overlapping reservation. For example between '#3 and #5' or '#4 and #5'.
    d. Subtract the total number of reservation from the set quantity.

    I tried to look it up and it seems you will have to make a join to get one row with information about #3 and #5.
    Maybe this page can help you:
    http://stackoverflow.com/questions/2358673/count-number-of-overlapping-rows-in-sql-server

    In that page, it only count the number of overlaps. In your case it will also be necessary to count the quantity.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    111
    Rep Power
    16
    Originally Posted by MrFujin
    To make it easier to read i decide to copy the nice illustration by r937


    To have a better understanding, you have following steps to take into consideration.

    a. Get all reservation in the range FS-FE
    b. Get maximum quantity of non-overlapping reservation. For example between #2 and #4
    c. Add the quantity of overlapping reservation. For example between '#3 and #5' or '#4 and #5'.
    d. Subtract the total number of reservation from the set quantity.

    I tried to look it up and it seems you will have to make a join to get one row with information about #3 and #5.
    Maybe this page can help you:
    http://stackoverflow.com/questions/2358673/count-number-of-overlapping-rows-in-sql-server

    In that page, it only count the number of overlaps. In your case it will also be necessary to count the quantity.
    Sorry for the lack luster diagram haha I tried to copy r937's over but for whatever reason it wouldn't keep the proper spacing.

    Anyways I'm going to give this a go and see where I end up. Stay tuned for more questions or a possible answer!

    Thanks,
    DSFX
  10. #6
  11. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,648
    Rep Power
    1945
    Will be interesting to see your result.

    Tried to look at as it can be seen here: http://sqlfiddle.com/#!2/d189a/1

    The tricky part is how to get value from step b and c.
    If you look at my link, you can see that booking_id 1 (A_ID) is overlapping booking_id 2, 4 and 5 (B_ID).
    But you can't just sum it all together, since booking_id 5 doesn't overlap booking_id 2.

    Just trying to describe it makes me confused

    Maybe this should be done in the application code instead of using SQL.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    111
    Rep Power
    16
    Originally Posted by MrFujin
    Will be interesting to see your result.

    Tried to look at as it can be seen here: http://sqlfiddle.com/#!2/d189a/1

    The tricky part is how to get value from step b and c.
    If you look at my link, you can see that booking_id 1 (A_ID) is overlapping booking_id 2, 4 and 5 (B_ID).
    But you can't just sum it all together, since booking_id 5 doesn't overlap booking_id 2.

    Just trying to describe it makes me confused

    Maybe this should be done in the application code instead of using SQL.
    I was thinking of that as well. I was going to write another query to get all overlapping dates, compare it to the original query, take the highest number and go with that. I think that may be my best bet because at this point I've still just got scribbles on paper.... but I hate to give up!
  14. #8
  15. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,648
    Rep Power
    1945
    Sounds possible, although I'm not sure if you still talk about using SQL only.

    Just have one ask about:
    Originally Posted by dsfx
    take the highest number
    Which number do you talk about here?

    As I see it, the issue is how to get the list of all combinations.
    In my example, it would be like this:
    Code:
    1   ->   2   ->   4  
    1   ->   4   ->   5   
    3   ->   null
    Having this list should make it easier to get the total (max) quantity.

    I hope it's not too much nonsense
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    111
    Rep Power
    16
    So this is tentatively working now... Thank you for all your help MrFujin. I'm in testing with the users currently.

    What I did... this will make you SQL purists cringe but I couldn't figure it out with pure SQL so I had to rely on ColdFusion to lend a helping hand.

    I'll go through this more later and add comments that make sense but I've gotta get some other things finished right now.

    Queries:
    Code:
    <!---Start Date Selection--->
    <!---First query will select the max number of bookings of  a non overlapping booking date--->
    <CFQUERY name="reservationlookup" datasource="external">
    	SELECT MAX(quantity) as booktotal, id 
        FROM book_club_reservation
        WHERE reserved_set = #form.id#    	
        AND #createodbcdate(form.dateend)# >= date_add(reservation_start, INTERVAL -1 day) 
        AND #createodbcdate(form.datestart)# <= date_add(reservation_end, INTERVAL -1 day) 
    </CFQUERY>
    
    <!---Select overlapping dates and add them together--->
    <CFQUERY name="reservationlookupoverlap" datasource="external">
      SELECT sum(quantity) as bookoverlaptotal, id
      FROM (
        select dr1.* from book_club_reservation dr1
        inner join book_club_reservation dr2
        where dr2.reservation_start > dr1.reservation_start 
          and dr2.reservation_start < dr1.reservation_end 
          and dr1.reserved_set = #form.id# 
          and dr2.reserved_set = #form.id# ) as total
          WHERE {d '2014-04-15'} >= date_add(reservation_start, INTERVAL -1 day) 
          AND {d '2014-03-18'} <= date_add(reservation_end, INTERVAL -1 day)
    </CFQUERY>
    
    <!---End Date Selection--->
    <!---Select all dates that fall between the startdate and the enddate--->
    <CFQUERY name="reservationlookup2" datasource="external">
    	SELECT SUM(quantity) as booktotal2, id
        FROM book_club_reservation
        WHERE reserved_set = #form.id#
        <!---Record count will not work for this because we are using the SUM function in SQL so it will always return a record --->
       	<CFIF ISNUMERIC(reservationlookup.id)>
          <CFLOOP query="reservationlookup">
              AND id != #id#<!---This removes all duplicate lookups--->
          </CFLOOP>
        </CFIF>
        AND #createodbcdate(form.datestart)# BETWEEN date_add(reservation_start, INTERVAL -1 day) and date_add(reservation_end, INTERVAL 1 day)    
        AND #createodbcdate(form.dateend)# BETWEEN date_add(reservation_start, INTERVAL -1 day) and date_add(reservation_end, INTERVAL 1 day)
    </CFQUERY>

    Then I take the results from those queries, dump them into variables and post them.

    Code:
    <CFSET var1 = #setlookup.quantity#>
    
    <!---This takes the values from the first and second queries then selects the highest one and saves it to a variable--->
    <CFIF reservationlookup.booktotal gt reservationlookupoverlap.bookoverlaptotal>
    	<CFSET var2 = #reservationlookup.booktotal# >
    <CFELSE>
    	<CFSET var2 = #reservationlookupoverlap.bookoverlaptotal#>
    </CFIF>    
    <CFSET var3 = #reservationlookup2.booktotal2#>
    
    
    
    <CFSET var2 = #var2# + #var3#>
    I realize there are probably 100 better ways to do this and i'm open to suggestions as I've still got a few weeks of development left but this works for now and it seems to be working well.
    Last edited by dsfx; January 8th, 2014 at 08:54 AM.

IMN logo majestic logo threadwatch logo seochat tools logo