#username# #visits#  
---------------------Coldfusion syntaxt----------------------- [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => reya276 [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) -->
 #username# #visits#  
---------------------Coldfusion syntaxt----------------------- [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Dan [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Months and dates issues - Coldfusion - Advanced Techniques

Months and dates issues - Coldfusion - Advanced Techniques

I need to get a count for how many visitors came into my site on a per month basis, I came up with this query which gives me the results, but on the coldfusion side I get the months repeated when the month name should be on the first line and then list every user that came in for each month, is there anyone who has done this and can you help. -----------------------SQL Query---------------- SELECT DISTINCT(DATENAME(month,(visit_date))) AS Month_Name,username,COUNT(*) AS visits,YEAR(visit_date) AS Year, Month(visit_date) AS Month_Number FROM user_stats GROUP BY DATENAME(month,(visit_date)),username,visit_date ORDER BY Month_Name DESC -----------------------SQL Query---------------- ---------------------Coldfusion syntaxt----------------------- <CFSET oldmonth ...

  1. #1

    Default Months and dates issues

    I need to get a count for how many visitors came into my site on a per month
    basis, I came up with this query which gives me the results, but on the
    coldfusion side I get the months repeated when the month name should be on the
    first line and then list every user that came in for each month, is there
    anyone who has done this and can you help.

    -----------------------SQL Query----------------
    SELECT DISTINCT(DATENAME(month,(visit_date))) AS Month_Name,username,COUNT(*)
    AS visits,YEAR(visit_date) AS Year,
    Month(visit_date) AS Month_Number
    FROM user_stats
    GROUP BY DATENAME(month,(visit_date)),username,visit_date
    ORDER BY Month_Name DESC
    -----------------------SQL Query----------------

    ---------------------Coldfusion syntaxt-----------------------
    <CFSET oldmonth = 99>

    <cfoutput query="monthlyStats">
    <CFIF Month(Month_Number) NEQ oldmonth>
    <CFSET oldmonth = Month(Month_Number)>
    <tr>
    <td width="61%" height="18" align="left"
    class="titlethree">&nbsp;&nbsp;#Month_Name# - #Year#</td>
    <td width="39%" align="left" class="title">Number of visits:</td>
    </tr>
    <tr>
    <td colspan="4" align="left" bgcolor="##006699"><img src="images/0.gif"
    width="1" height="1" /></td>
    </tr>

    <tr>
    <td colspan="2">
    <!---data table--->
    <table width="100%" border="0" cellspacing="0" cellpadding="2">
    <tr>
    <td width="61%" height="18" align="left" valign="middle"
    class="title">&nbsp;#username#</td>
    <td width="17%" align="center" valign="middle"
    class="textsm"><strong>#visits#</strong></td>
    <td width="22%" align="left" valign="middle" class="textsm">&nbsp;</td>
    </tr>
    <tr>
    <td colspan="3" align="left"><img src="images/0.gif" width="1" height="2"
    /></td>
    </tr>
    </table>
    <!---data table--->
    </td>
    </tr>
    <tr>
    <td colspan="2"><img src="images/0.gif" width="1" height="10" /></td>
    </tr>
    </CFIF>
    </cfoutput>

    ---------------------Coldfusion syntaxt-----------------------

    reya276 Guest

  2. #2

    Default Re: Months and dates issues

    Use someyhing like this:

    <cfset my_month = "">
    <cfoutput query="monthlyStats" group="Month_Name ">
    <cfif monthlyStats.Month_Name NEQ my_month >

    #Month_Name# - #Year#

    CF_Oracle Guest

  3. #3

    Default Re: Months and dates issues

    In your sql query, take username out of the select clause. Also, month name
    and month number are redundant, so one of them can go.

    In your group by clause, just group by the month and the year.

    Originally posted by: reya276
    I need to get a count for how many visitors came into my site on a per month
    basis, I came up with this query which gives me the results, but on the
    coldfusion side I get the months repeated when the month name should be on the
    first line and then list every user that came in for each month, is there
    anyone who has done this and can you help.

    -----------------------SQL Query----------------
    SELECT DISTINCT(DATENAME(month,(visit_date))) AS Month_Name,username,COUNT(*)
    AS visits,YEAR(visit_date) AS Year,
    Month(visit_date) AS Month_Number
    FROM user_stats
    GROUP BY DATENAME(month,(visit_date)),username,visit_date
    ORDER BY Month_Name DESC
    -----------------------SQL Query----------------

    ---------------------Coldfusion syntaxt-----------------------
    <CFSET oldmonth = 99>

    <cfoutput query="monthlyStats">
    <CFIF Month(Month_Number) NEQ oldmonth>
    <CFSET oldmonth = Month(Month_Number)>
    <tr>
    <td width="61%" height="18" align="left"
    class="titlethree">&nbsp;&nbsp;#Month_Name# - #Year#</td>
    <td width="39%" align="left" class="title">Number of visits:</td>
    </tr>
    <tr>
    <td colspan="4" align="left" bgcolor="##006699"><img src="images/0.gif"
    width="1" height="1" /></td>
    </tr>

    <tr>
    <td colspan="2">
    <!---data table--->
    <table width="100%" border="0" cellspacing="0" cellpadding="2">
    <tr>
    <td width="61%" height="18" align="left" valign="middle"
    class="title">&nbsp;#username#</td>
    <td width="17%" align="center" valign="middle"
    class="textsm"><strong>#visits#</strong></td>
    <td width="22%" align="left" valign="middle" class="textsm">&nbsp;</td>
    </tr>
    <tr>
    <td colspan="3" align="left"><img src="images/0.gif" width="1" height="2"
    /></td>
    </tr>
    </table>
    <!---data table--->
    </td>
    </tr>
    <tr>
    <td colspan="2"><img src="images/0.gif" width="1" height="10" /></td>
    </tr>
    </CFIF>
    </cfoutput>

    ---------------------Coldfusion syntaxt-----------------------



    Dan Guest

  4. #4

    Default Re: Months and dates issues

    yes I understand what you mean but what I need is to display the month and
    under each month display the username for every person that logged on to the
    site which is why I have username there, so I cannot remove it.

    reya276 Guest

  5. #5

    Default Re: Months and dates issues

    I still need to display every username for every month so it would look
    something like this:

    October
    list of user for the month

    November
    list of user for the month

    December
    list of user for the month

    and so on....

    reya276 Guest

  6. #6

    Default Re: Months and dates issues

    Run a query to get the raw data you need. Use Q of Q to summarize it. I hope
    your number of users is small.

    Originally posted by: reya276
    I still need to display every username for every month so it would look
    something like this:

    October
    list of user for the month

    November
    list of user for the month

    December
    list of user for the month

    and so on....



    Dan Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •