February 7th, 2014, 08:26 AM
Grouping overlapping dates
So I've got a query that spits out a startdate, enddate and quantity.
I need it to group the concurrent overlapping dates together and add the quantity up.
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
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.
February 7th, 2014, 11:27 AM
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.
February 7th, 2014, 11:31 AM
February 7th, 2014, 11:46 AM
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!
February 11th, 2014, 10:52 AM
So I figured it out, I just needed a long weekend to clear my head.
I put the query results into arrays
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.
<CFSET i = 0>
<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 />
Then I use array max to get the greatest value in my array.
<CFSET groupnum = 1>
<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#]>
<CFSET groupnum = #groupnum# + 1>
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.