Advertisement

07.23.2008 at 04:27AM PDT, ID: 23588042
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
9.2

Tricky one about a query

Asked by nicky2k in ColdFusion Application Server

Tags: , ,

I have a table, which adds a record and stores the date added and the time added. This means that every time my page refreshes, I can have more than one record per day. What I want to do is display the first record found for the day, regardless of what time it is, so that I am not displayign the records more than once per day. Sounds simple?
I tried creating another query that gets just the date and the time, comparing with the original query (looping twice over each query object) and trying to display the records where the time is different. Did not work.
In the loop described earlier, in the if statement, I tried running another query, looping over that, and just adding records WHERE rownum=1. Did not work.
Help please!!Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:

<cfcomponent output="false">
 
	<cfscript>
	//returns a name for when the client access occured, relative to today
	//Will name each day of the current week, then by week number	
	function getSectionName(daysPast){
		var access_date = DateAdd("d",-arguments.daysPast,now());
		
		//var WeeksAgo=DateDiff("ww",access_date,now());
		var WeeksAgo=DatePart("ww",now()) - DatePart("ww",access_date);
	
		if(daysPast GTE 30){
			return "Older";
		}
		if (WeeksAgo GT 1){
			return "#WeeksAgo# Weeks Ago";
		}
		if (WeeksAgo EQ 1){
			return "#WeeksAgo# Week Ago";
		}	
		if (daysPast NEQ 0){
			return DayOfWeekAsString(DayOfWeek(access_date)) & " " & WeeksAgo;
		}
		if(daysPast EQ 0){
			return "Today";
		}
		
		return "Unknown";
	}
	</cfscript>
 
	<cffunction name="getRecentSubjectsHTML">
		<cfargument name="user_id">
		<cfset var recents = getRecentSubjectsRecordSet(arguments.user_id)>
		
		<cfsavecontent variable="rsuHTML">
		<ul class="TreeRoot">
		<cfoutput query="recents" group="section_name">
			<cfif recents.section_name neq "">
			<li class="TreeBranch" id="#recents.section_id#">
				<a href="javascript:treeToggle('#recents.section_id#')">
				#recents.section_name#
				</a>
				<ul class="RecentList">
					<cfoutput>
					<cfif rsu_access_date neq "">	
						<li class="#recents.subject_type_name#">
							<a 
								href="javascript:selectSubject('#recents.rsu_subject_ind#','#recents.rsu_subject_id#');"
								title="Accessed at #TimeFormat(recents.rsu_access_time,"HH:MM")# on #DateFormat(recents.rsu_access_date,"DD/MMM/YY")#"	
							>
							<cfif recents.subject_type_name EQ "Organisation">
								<img src="/cfi/application_store/skins/style/default/images/icon_org.gif">
							<cfelse>
								<img src="/cfi/application_store/skins/style/default/images/icon_person.gif">
							</cfif>						
							#recents.subject_name# (#recents.rsu_subject_id#)
							</a>
						</li>
					</cfif>
					</cfoutput>
				</ul>			
			</li>
			</cfif>
		</cfoutput>
		</ul>
		</cfsavecontent>
		<cfreturn rsuHTML />
	</cffunction>
	
	<cffunction name="getRecentSubjectsRecordSet">
		<cfargument name="user_id">
		<cfargument name="order" required="false" default="ASC">
		<cfset var sectionName="">
		<cfset var sectionNames=ArrayNew(1)>	
		<cfset var sectionIDs=ArrayNew(1)>
		<cfset var recents="">
		<cfset var tempQuery=QueryNew("rsu_subject_id,days_past,subject_type_name,subject_name,rsu_subject_ind,rsu_access_date,rsu_access_time")>
		<!--- <cfset var newQuery=QueryNew("section_id,section_name,rsu_subject_id,days_past,subject_type_name,subject_name,rsu_subject_ind,rsu_access_date,rsu_access_time")> --->
		
		<cfquery name="getAccessTime" datasource="#application.db.dsn#">
			SELECT  
				rsu_access_time,rsu_access_date 			
			FROM 
				 o_recent_subjects
			WHERE
				 rsu_user_id = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.user_id#">
		</cfquery>
		
		<cfquery name="getAccessTime1" datasource="#application.db.dsn#">
			SELECT  
				   rsu_access_time,rsu_access_date 			
			FROM 
				   o_recent_subjects
			WHERE
				   rsu_user_id = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.user_id#">
			AND  
				   rownum = 1
		</cfquery>
		
		<!--- Now run the main query itself --->
		<cfquery name="recents" datasource="#application.db.dsn#">
			SELECT 
				   rsu_subject_id,
				   rsu_subject_ind,
				   rsu_access_time,
				   rsu_access_date,
				   TRUNC(SYSDATE) - rsu_access_date AS days_past,
				   DECODE(rsu_subject_ind,
				   'O',cfi_person_org.return_oun_name(rsu_subject_id),
				   'P',cfi_person_org.return_per_name(rsu_subject_id),
				   'Unknown') subject_name,
				   DECODE(rsu_subject_ind,
				   'O','Organisation',
				   'P','Person') subject_type_name				   
			FROM 
				   o_recent_subjects
			WHERE
				   rsu_user_id = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.user_id#">
			ORDER BY 
				   rsu_access_date #arguments.order#
		</cfquery>
		
		<!--- <cfset newRow = QueryAddRow(tempQuery,#recents.recordcount#) />  --->
	
		<!--- Calculate the section names and add them as a new column --->
		<cfoutput query="recents" group="rsu_access_date">
			<cfset sectionName = getSectionName(recents.days_past)>
			<cfset ArrayAppend(sectionNames,sectionName)>
			<cfset ArrayAppend(sectionIDs,Replace(sectionName," ","_","ALL"))>
			
			<!--- <cfset QuerySetCell(tempQuery,"rsu_subject_id",#recents.rsu_subject_id#,#recents.currentrow#)>
			<cfset QuerySetCell(tempQuery,"rsu_subject_ind",#recents.rsu_subject_ind#,#recents.currentrow#)>
			<cfset QuerySetCell(tempQuery,"days_past",#recents.days_past#,#recents.currentrow#)>
			<cfset QuerySetCell(tempQuery,"subject_type_name",#recents.subject_type_name#,#recents.currentrow#)>
			<cfset QuerySetCell(tempQuery,"subject_name",#recents.subject_name#,#recents.currentrow#)>  --->
	
		</cfoutput>
		
		
	
		<!--- <cfset QueryAddColumn(tempQuery,"section_name",sectionNames)>
		<cfset QueryAddColumn(tempQuery,"section_id",sectionIDs)>  --->
		
		
		<cfset QueryAddColumn(recents,"section_name",sectionNames)>
		<cfset QueryAddColumn(recents,"section_id",sectionIDs)> 
		
		<cfreturn recents />   
		
		
		<!--- <cfdump var="#tempQuery#"><cfabort>
		<cfreturn tempQuery /> --->
	</cffunction>
	
</cfcomponent>
 
Keywords: Tricky one about a query
Title
1 Rownum
2 Rownum
3 Clear a query out and refresh
4 Visual Basic Looping Structure Problem
5 RowNum with SQLServer
6 quesion for @rownum :=@rownum+1
 
Loading Advertisement...
 
[+][-]07.23.2008 at 05:46AM PDT, ID: 22068586

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: ColdFusion Application Server
Tags: Adobe, ColdFusion, 7
Sign Up Now!
Solution Provided By: angelIII
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 - Hierarchy / EE_QW_2_20070628