| Author |
 Topic  |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
 Posted - 31 March 2010 : 04:46:53
|
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: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.
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
Edited by - Shaggy on 31 March 2010 04:47:29 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 31 March 2010 : 05:10:09
|
Try this:
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. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 31 March 2010 : 06:13:53
|
Thanks, Rui, that got me very close to what I want to achieve 
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?
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 31 March 2010 : 06:31:01
|
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. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 31 March 2010 : 07:01:17
|
Thanks again, Rui
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.
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 functionWorks a treat and returns an array of months exactly as I need them
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. |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
Edited by - Shaggy on 31 March 2010 07:02:08 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
| |
Topic |
|
|
|
Selecting unique dates from date ranges
