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

    Join Date
    Apr 2009
    Posts
    113
    Rep Power
    16

    Grouping overlapping dates


    So I've got a query that spits out a startdate, enddate and quantity.
    Code:
    February 10, 2014 - March 13, 2014 - 8
     February 18, 2014 - March 22, 2014 - 8
     March 19, 2014 - April 18, 2014 - 10 
     March 26, 2014 - April 26, 2014 - 8
    I need it to group the concurrent overlapping dates together and add the quantity up.

    So for example, this would spit out two variables Group1=26 and Group2=8. Group1 would consist of the first three dates and Group2 would have the last date in it.

    We are loaning out sets of books but instead of loaning out the whole set we are allowing it to be broken up so I need to find a maximum number of books out at one time over a given date range so I don't lend more than what we have.

    I'm getting lost in my loops so any help would be appreciated.

    Thanks,
    DSFX
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,286
    Rep Power
    968
    You really should be doing this in SQL. That said, it sounds like it will be complicated regardless of where you perform the grouping. If your database supports reporting functions (Oracle ROLLUP, etc.), that may help.
  4. #3
  5. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,286
    Rep Power
    968
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    113
    Rep Power
    16
    To be honest the query i'm using is a bit over my head, I only half understand it and that doesn't sit well with me. I modified it to spit those results out so I could use CF to finish it off.

    The DB i'm using is MySQL 5.5 and I don't think it has support for that.

    I think I've almost got it I just need to figure out the condition on the inner loop. I'll post it up when I get it for anybody that might stumble upon this.

    Edit: saw your second post... this could work splendidly!
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    113
    Rep Power
    16
    So I figured it out, I just needed a long weekend to clear my head.

    I put the query results into arrays
    Code:
    <CFSET i = 0>
    <CFLOOP query="dategrab1">
    <CFSET i = i + 1>
    <CFSET startdates[#i#] = #dateformat(reservation_start, "long")#>
        <CFSET enddates[#i#] = #dateformat(reservation_end, "long")#>
        <CFSET bookquantity[#i#] = #quantity#>
    
        <CFOUTPUT>#startdates[i]#</CFOUTPUT> - <CFOUTPUT>#enddates[i]#</CFOUTPUT> - <CFOUTPUT>#bookquantity[i]#</CFOUTPUT><br />
    
    </CFLOOP>
    Then I created an array named Group to save the groups values to and loop through the date ranges. I'm looking to see if the start date falls between any of the date ranges in the above array.

    Code:
    <CFSET groupnum = 1>
    
    <CFOUTPUT>
    <CFLOOP query="dategrab1">
    
    	<CFSET group[#groupnum#] = 0>
        
        <CFLOOP from="1" to="#i#" index="x">    		
        
        		<CFIF DATECOMPARE(startdates[#groupnum#], startdates[#x#]) GTE 0 AND DATECOMPARE(startdates[#groupnum#], enddates[#x#]) LTE 0>
    				<CFSET group[#groupnum#] = group[#groupnum#] + bookquantity[#x#]>
                    yes,
                    <CFELSE>
                    no,
            	</CFIF>
                
        </CFLOOP>
        
        #group[groupnum]#<br />
    
        
        <CFSET groupnum = #groupnum# + 1>
    
    </CFLOOP>
    </CFOUTPUT>
    Then I use array max to get the greatest value in my array.
    Code:
    #arraymax(group)#

    I honestly have no clue how this would have been done in SQL. But this solution is working great so far, I've spent the morning trying to break it to no avail.

    Thanks for the help Kite.

IMN logo majestic logo threadwatch logo seochat tools logo