Selecting unique dates from date ranges

Snitz™ Forums 2000
https://forum.snitz.com/forumTopic/Posts/69260?pagenum=1
05 November 2025, 09:06

Topic

Shaggy
Selecting unique dates from date ranges
31 March 2010, 04:46


It's probably the long hours causing code blindness but I can't, for the life of me, figure this one out.
I have a number of records in a database each with a start date & an end date and what I need to do is select all the months that fall between each date, without duplication, in descending order.
Say, for example, I had the following records:
Code:
ID	START		END
1	20091022	20091115
2	20100102	20100217
3	20100208	20100525
4	20100612	20100614
I'd need a query and/or script that would return the following:
201006
201005
201004
201003
201002
201001
200911
200910
Obviously, with a single date, this is easily achieved with the following query:
SELECT DISTINCT LEFT(FIELD,6) FROM TABLE GROUP BY LEFT(FIELD,6) ORDER BY FIELD DESC
But I'm at a complete loss as to how to achieve the same result with a range of dates. It's a MySQL database, if that makes a difference.

 

Replies ...

ruirib
31 March 2010, 05:10


Try this:
Code:

SELECT * FROM (
SELECT LEFT(FIELD,6) As MyFIELD FROM TABLE
UNION
SELECT RIGHT(FIELD,6) As MyFIELD FROM TABLE 
) AS T
ORDER BY T.MyFIELD DESC

Any MySQL version >= 5.0 should deal with it fine.
P.S.: You can also use only SELECT MyFIELD on the main query, if you prefer.
Shaggy
31 March 2010, 06:13


Thanks, Rui, that got me very close to what I want to achieve smile
The only problem with it is it only return the first and last months in a range. If you look at record 3 above, you'll see a start date of 20100208 and an end date of 20100525. Your query would only return 201002 and 201005 for this record but I also need it to return 201003 and 201004.
Would I be correct in thinking that this isn't possible with SQL alone and that I'd be better off selecting all the start & end dates and then running them through a function to build my array?
ruirib
31 March 2010, 06:31


Hi Shaggy,

Well, a simple SQL select will only retrive you what is there and those in between months are not (201003 and 201004). The only way to do that would be to have a stored procedure that would determine the need to calculate the in between ranges and returned them.
Alternatively, you'd need a function, to process it, yes.
Shaggy
31 March 2010, 07:01


Thanks again, Rui smile
Anticipating that your answer would be what I was already thinking, I've come up with the following query:
SELECT M_START,MID(M_END,5,2)-MID(M_START,5,2) FROM MEMBERS ORDER BY M_START DESC
Which then gets passed through a function that:
  • loops through each record, checking if the start date is already in the array and adding it to the array if not,
  • Converts my date to a normal date and then runs a loop adding 1 month to that date until the difference in months between start and end date is reached, again checking the array and adding to it accordingly.
Code:
arrMonths=getmonths(rsRecords.getrows(adGetRowsRest))

function getmonths(arr)
	dim arrTemp,intDiff,strDate,strTemp
	arrTemp=array()
	for x=0 to ubound(arr,2)
		strTemp=arr(0,x)
		if not inarray(arrTemp,strTemp) then
			redim preserve arrTemp(ubound(arrTemp)+1)
			arrTemp(ubound(arrTemp))=strTemp
		end if
		intDiff=arr(1,x)
		if intDiff<0 then intDiff=12+intDiff
		if intDiff>0 then
			strDate=stringtodate(strTemp&"01","",false,"",false)
			for y=1 to intDiff
				strTemp=datetostring(dateadd("m",y,strDate),false)
				if not inarray(arrTemp,strTemp) then
					redim preserve arrTemp(ubound(arrTemp)+1)
					arrTemp(ubound(arrTemp))=strTemp
				end if
			next
		end if
	next
	for x=0 to ubound(arrTemp)
		for y=x+1 to ubound(arrTemp)
			if arrTemp(x)<arrTemp(y) then
				strTemp=arrTemp(x)
				arrTemp(x)=arrTemp(y)
				arrTemp(y)=strTemp
			end if
		next
	next
 	getmonths=arrTemp
end function
Works a treat and returns an array of months exactly as I need them smile
It could probably do with a bit of a clean up but, with my aforementioned code blindness, it took all my efforts to get this far so any suggestions for improvements would be welcomed.
ruirib
31 March 2010, 08:55


Not the best of days here, either. Had a long night and have another one like that coming... Anyway, you seem to have sorted it smile.
© 2000-2021 Snitz™ Communications