Data Mining Coldfusion

[aycmike]

Im trying to search my database and find out the previous 7 days unique ip addresses. Users ip addresses are stored within the table events under svname. I have a small piece of java that is supposed to count back 6 days.

At the moment its saying there have been 0 unique ip addresses accessing the site within the last 6 days but i know that is wrong!

I know google analyitcs does this much better but im just trying to fix some old sites up, any help would be much appreciatred!

http://newmedia.leeds.ac.uk/ug06/cs06mtr/skate/logs/index.cfm

<!--The Java Function-->
<cfscript>
                    function myBack(myday)
                    {
                    return DateAdd("d", -myday, DateFormat(now(), "MM/DD/YYYY"));
                    }
</cfscript>


<!--Query-->
<cfquery name="week_unique_ip" datasource="062909cs06mtr">
SELECT *, IPCount FROM
(
SELECT DISTINCT (svname), COUNT (*) AS IPCount
FROM events
where udate = <cfoutput>'#DateFormat(myBack(6), "DD/MM/YYYY")#'</cfoutput>
GROUP BY svname
)

</cfquery>

<!--Output-->
<cfoutput>#week_unique_ip.RecordCount#</cfoutput>

[hinde]

You are checking for event ips that have a udate of EXACTLY 6 days ago, not all events that have occurred since 6 days ago (use > instead of =). You also seem to be querying udate as if its an varchar. Hopefully this isn't the case because I am not sure if the > operator would give you what you want unless you casted all the values or altered your schema. That field should be a datetime.