The Forum has been Updated
The code has been upgraded to the latest .NET core version. Please check instructions in the Community Announcements about migrating your account.
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: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.”
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 20100614201006
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 thefact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.”
Postet den
Try this:
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.
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.
Postet den
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.”
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 thefact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.”
Postet den
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.
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.
Postet den
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:Works 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.”
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 functionIt 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 thefact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.”
Postet den
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 .
.