Ok, let's see if I can explain this properly. Also, I apologize for the length of this post but…. I've been charged with the task of sorting and displaying our software releases on our website so that depending on the software initially chosen, the latest major release with its latest Service Pack (if it has one) gets displayed first followed by its related Hotfixs, which all get displayed on the first page. Then the subsequent release (be it a Major or Minor release) and it's related Hotfixes and Service Packs are displayed on a separate page and finally the rest of the releases and corresponding HF and SP on yet another page. The version number is separated by decimal points. Ex. Major Minor ServicePack Hotfix 6. 2. 2. 3 So to display this properly it should look like this: 6.2.2.0 6.2.2.3 6.2.2.2 6.2.2.1 On the next page: 6.2.1.0 6.2.1.2 … and so on. Our info is held in a database which gets queried as ex: <cfquery name="getLatest" datasource="#application.dsn#"> SElECT Major, Minor, ServicePack, Hotfix FROM Release WHERE ID = #URL.productID# ORDER by Major DESC, Minor DESC, ServicePack DESC, Hotfix DESC </cfquery> Table ex: ID Major Minor ServicePack Hotfix 801 6 2 2 3 801 6 2 2 2 801 6 2 2 1 801 6 2 2 0 801 6 2 1 2 801 6 2 1 1 801 6 2 1 0 801 6 2 0 2 801 6 2 0 1 801 6 2 0 0 801 6 1 0 0 801 6 1 2 3 801 6 1 2 2 Realizing that the last number in the sequence when it's a zero is dependent on the previous number and that that number (if it's a zero) is also dependent on the number before it is, and so on, my logic gets a little mixed up. I know that if a zero is encountered, the previous number is decreased by 1 unless it's also a zero, which in that case the number before it needs to be checked to see if it's a zero. If it's greater than zero, I need to decrease the number by one, if not, I need to check the next number before it and so on. Any help in arranging the logic so this can be coded neatly instead of a bunch of nested or stacked "if" statements would be greatly appreciated. Thanks in advance
I'm not sure I get it. Would this be the correct output for the table you listed: Page 1: 801 6 2 2 0 801 6 2 2 3 801 6 2 2 2 801 6 2 2 1 Page 2: 801 6 2 1 0 801 6 2 1 2 801 6 2 1 1 Page 3: 801 6 2 0 0 801 6 2 0 2 801 6 2 0 1 Page 4: 801 6 1 0 0 801 6 1 2 3 801 6 1 2 2
Yes, that's pretty much it. The 801 number is just the product ID so that doesn't get displayed. And page 4 would be combined with page 3 along with anything that followed after that. So let's say we left off at Page 3: 6 2 0 0 6 2 0 2 6 2 0 1 6 1 0 0 6 1 2 3 6 1 2 2 It would then be followed on the same page with: 6 1 2 1 6 1 2 0 6 1 1 ... and so on. Now there is a little quirk to the problem presented. Not all releases will follow the pattern being shown here. Sometimes a hotfix or servicepack will be disabled and you end up with the sequence being disrupted so that for example if the last sequence was 6.2.3.0, the next might be 6.2.1.0 followed by the rest of the sequence. Does this make any sense? Thanks again
I think I get it. Can you PM me a CSV with some table data? I don't feel like creating a table and typing this stuff in right now
Ok , that was weird. I kept getting a database error from the site when trying to respond to your last post. Anyway, here's the attachment. It's been edited because of the size limitation for attachments from this forum but should give you a fairly good idea. Thanks
First, thanks robhustle for responding and getting me thinking. Do appreciate the help. Now if anyone else runs into a similar situation, I'd like to share my approach and logic that seems to have worked for me. This is also not the only way to solve the problem I'm sure, for instance, I probably could of used a two dimensional array. But in this particular case, this happened to make more sense to me. <cfquery name="ReleaseInfo" datasource="#application.dsn#"> SELECT ID, Grouping_ID, Major, Minor, ServicePack, Hotfix FROM Release WHERE Grouping_ID = <CFQUERYPARAM VALUE="#grpID#" CFSQLTYPE="CF_SQL_INTEGER"> AND LinkType_CD = 'A' ORDER BY Major DESC, Minor DESC, ServicePack DESC, Hotfix DESC </cfquery> <!--- Keep track of the rows being checked ---> <CFSET rowCount = 0> <!--- Loop through the query and find the latest entry, then find the latest Service Pack (SP) ---> <CFOUTPUT> <CFLOOP QUERY="ReleaseInfo" STARTROW="1" ENDROW="#ReleaseInfo.RecordCount#"> <!--- Here's the latest entry assigned to a list variable---> <CFSET Release = #Major# & '.' & #Minor# & '.' & #Servicepack# & '.' & #Hotfix#> <!--- Assign each entry a matching variable ---> <CFSET HotFix = #ListGetAt(Release,4, '.')#> <CFSET SP = #ListGetAt(Release,3, '.')#> <CFSET Miner = #ListGetAt(Release,2, '.')#> <CFSET Maj = #ListGetAt(Release,1, '.')#> <!--- Check the last entry to see if it's greater than zero. If so, it's looped through and for each iteration the HotFix number is subtracted by one until it reaches zero. The last entry being your lastest update and the prior entries being the associated hotfixes. ---> <CFIF HotFix GT 0> <CFSET Release = ListSetAt(Release, 4, #HotFix#, '.')> <CFSET HotFix = HotFix - 1> <CFIF Major EQ Maj AND Minor EQ Miner AND ServicePack EQ SP AND HotFix GTE 0> <CFSET Release = #Major# & '.' & #Minor# & '.' & #Servicepack# & '.' & #Hotfix#> <CFSET rowCount = rowCount + 1> Row Num = #rowCount# | LatestHotfixes= #Release#<br /><BR> </CFIF> <CFELSEIF HotFix EQ 0> <CFSET Release = #Release#>Latest Release Update = #Release#<br /><br /> <CFSET rowCount = rowCount + 1><CFBREAK> </CFIF> </CFLOOP> <!--- Now we do the same for each remaining variable ---> <CFLOOP QUERY="ReleaseInfo" STARTROW="#rowCount#" ENDROW="#ReleaseInfo.RecordCount#"> <CFIF SP GT 0> <CFSET Release = ListSetAt(Release, 3, #SP#, '.')> <CFSET SP = SP - 1> <CFLOOP QUERY="ReleaseInfo" STARTROW="#rowCount#" ENDROW="#ReleaseInfo.RecordCount#"> <CFIF Major EQ Maj AND Minor EQ Miner AND ServicePack EQ SP AND SP GTE 0> <CFSET Release = #Major# & '.' & #Minor# & '.' & #Servicepack# & '.' & #Hotfix#> <CFSET rowCount = rowCount + 1> Row Num = #rowCount# | Prior Hotfixes = #Release#<BR> </CFIF> </CFLOOP> <CFELSEIF SP EQ 0> <CFSET Release = #Release#>Row Num = #rowCount# | Prior Release = #Release#<br /><br /> <CFSET rowCount = rowCount + 1><CFBREAK> </CFIF> </CFLOOP> <CFLOOP QUERY="ReleaseInfo" STARTROW="#rowCount#" ENDROW="#ReleaseInfo.RecordCount#"> <CFIF Miner GT 0> <CFSET Release = ListSetAt(Release, 2, #Miner#, '.')> <CFSET Miner = Miner - 1> <CFLOOP QUERY="ReleaseInfo" STARTROW="#rowCount#" ENDROW="#ReleaseInfo.RecordCount#"> <CFIF Major EQ Maj AND Minor EQ Miner AND Miner GTE 0> <CFSET Release = #Major# & '.' & #Minor# & '.' & #Servicepack# & '.' & #Hotfix#> <CFSET rowCount = rowCount + 1> Row Num = #rowCount# | New = #Release#<BR> </CFIF> </CFLOOP> <CFELSEIF Miner EQ 0> <CFSET Release = #Release#>Latest Release = #Release#<br /><br /> <CFSET rowCount = rowCount + 1><CFBREAK> </CFIF> </CFLOOP> <CFLOOP QUERY="ReleaseInfo" STARTROW="#rowCount#" ENDROW="#ReleaseInfo.RecordCount#"> <CFIF Maj GT 0> <CFSET Release = ListSetAt(Release, 1, #Maj#, '.')> <CFSET Maj = Maj - 1> <CFLOOP QUERY="ReleaseInfo" STARTROW="#rowCount#" ENDROW="#ReleaseInfo.RecordCount#"> <CFIF Major EQ Maj AND Maj GT 0> <CFSET Release = #Major# & '.' & #Minor# & '.' & #Servicepack# & '.' & #Hotfix#> <CFSET rowCount = rowCount + 1> Row Num = #rowCount# | New = #Release#<BR> </CFIF> </CFLOOP> <CFELSEIF Maj EQ 0> <CFSET Release = #Release#> Oldest Release = #Release#<br /><CFBREAK> </CFIF> </CFLOOP> </CFOUTPUT> If anyone else would like to contribute in offering a more optimal code please feel free.
Another possibility is to store a zero padded version of the numbers. Either in a separate columns or a database view. Example: Major, Minor, ServicePack, Hotfix (Integer Value) 6, 1, 1, 13 (Zero Padded) 00006, 00001, 00001, 00013 Then concatenate the values in a view, to generate the different levels of version information: Full Version = 00006.00001.00002.00002 ServicePack = 00006.00001.00002 MajorMinor = 00006.00001 ... Then you could query the view using MAX, MIN, >= , etc.. ======================================= SELECT MAX(ReleaseFullVersion) LatestVersion FROM yourViewName Results: 00006.00001.00002.00002 ======================================= SELECT MAX(ReleaseServicePack) LatestServicePack FROM yourViewName Results: 00006.00001.00002 ======================================= SELECT ReleaseFullVersion FROM yourViewName WHERE ReleaseFullVersion >= '00006.00001.00002' Results: 00006.00001.00002.00002 00006.00001.00002.00001 00006.00001.00002.00000 =======================================