?
Solved

How to show multiple records that have same date, under one date listing?

Posted on 2002-05-01
7
Medium Priority
?
170 Views
Last Modified: 2013-12-24
Hi,

I need some help with the following:

I have a database table where I display records from the last 7 days in a CF page.

How can I display the date once for all records records that have the same date?
0
Comment
Question by:g118481
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 1

Expert Comment

by:FranzRinkleff
ID: 6984094
you could have some queries that gets the numbers of records for each day

for instance

<cfquery name="dateCountQuery" datasource="#datasource#">
 select * from my_table where date = '#theDate#'
</cfquery>
<cfset dateCount = dateCountQuery.RecordCount>

then in your table

<table>
<tr><th>Date></th><th>Other</th></tr>
<cfoutput query="otherQuery">
<tr>
  <td rowspan="dateCount">#otherQuery.daate#<td>


</cfoutput>
0
 
LVL 1

Expert Comment

by:FranzRinkleff
ID: 6984100
i prematurely submitted the previous message

you could have some queries that gets the numbers of records for each day

for instance

<cfquery name="dateCountQuery" datasource="#datasource#">
select * from my_table where date = '#theDate#'
</cfquery>
<cfset dateCount = dateCountQuery.RecordCount>

then in your table use the rowspan attribute of the td tag like this.  

otherQuery is your query for the last 7 days

<table>
<tr><th>Date></th><th>Other</th></tr>
<cfoutput query="otherQuery">
<tr>
 <td rowspan="dateCount">#otherQuery.date#<td>
  <td>#otherQuery.other#</td>
</tr>
</cfoutput>
</table>
0
 
LVL 1

Author Comment

by:g118481
ID: 6984432
I'm not sure you understand what I am trying to do here.  So, here is an example of what is needed:
Below are three sets of records from the table.  
each record has the same date, but only one has the date listed.

/***********************************************/
Tuesday April 30, 2002

10:00 AM - 12:00 PM EDT Scheduled
JAD 374364.Integrated Voice Access in CBSS.
DPSR NUMBER:
SPM ID:
SES LOE DUE DATE:
BRIDGE NUMBER/PASSCODE: 301-277-8057
LOCATION: Bridge
SES MANAGER: Greg Golombisky
SES PHONE NUMBER: 813-979-5813
PROGRAM MANAGER & PHONE NUMBER:
RELEASE INFO/COMMENTS: August
CREATE DATE: 2002-04-26 16:39:43
REVISION DATE: 2002-04-26 16:54:20

01:00 PM - 03:00 PM EST Scheduled
INSP 374816.0PDQ 585 $5 Minimum Spend Level
DPSR NUMBER: Not available
SPM ID: E1354A0
SES LOE DUE DATE:
BRIDGE NUMBER/PASSCODE: N/A
LOCATION: K401 Sabal
SES MANAGER: Janet Tomberlin
SES PHONE NUMBER: 813-979-5572
PROGRAM MANAGER & PHONE NUMBER:
RELEASE INFO/COMMENTS:
CREATE DATE:
REVISION DATE:

02:00 PM - 04:30 PM EDT Scheduled
JAD 374183.OMT PERMANENT NO TREAT/TEMPORARY NO TREAT
DPSR NUMBER:
SPM ID:
SES LOE DUE DATE:
BRIDGE NUMBER/PASSCODE: 813.273.6622, pc 9786137#
LOCATION: Tech Center - B201
SES MANAGER: Bob Coates
SES PHONE NUMBER: 813.978.6137
PROGRAM MANAGER & PHONE NUMBER:
RELEASE INFO/COMMENTS: October
CREATE DATE: 2002-04-03 12:53:53
REVISION DATE: 2002-04-18 11:55:44
0
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 
LVL 1

Accepted Solution

by:
FranzRinkleff earned 150 total points
ID: 6984458
i am not sure what you want either.


do you want the page to look like what you have displayed above.  Do you want the date to appear once,
Tuesday April 30, 2002, as shown above followed by the records for that date.

If so try something like this

<cfset tempDate = "">
--loop for records
<cfoutput query="theQuery">
-- following header appears only when the if statement true
<cfif theQuery.date neq tempDate>
  <cfset tempDate = theQuery.date>
  <h3>#tempDate#</h3>
</cfif>
--.... code for the rest of the records
</cfoutput>
0
 
LVL 1

Expert Comment

by:Agatheeswaran
ID: 6985367
Hi g118481,
First I will explain what i had understood from your question,

"Date" is also one of the column in the table, right!
if so, follow the code, its a straight forward solution,

select the records with date,
then in the cfm page code like this,
<tr>
<td>Date</td>
<td>Required column1</td>
.
.
<td>Required columnN</td>
</tr>
<cfset idate="">
<cfloop through the query>
   <tr>
   <cfif idate is not columndate>
     <cfset idate = columndate>
      <td>Date</td>
   <cfelse><td>&nbsp;</td>
   </cfif>
       <td>Required column1</td>
         .
         .
       <td>Required columnN</td>
    </tr>
</cfloop>

Basically, logic in it is first make the idate variable as null, then compare that idate with column date retrieved from the table, if there is no match then store that value in the variable and loop at the same time display it, or otherwise just put one space in that column.

Thanks
aga
0
 

Expert Comment

by:hop702
ID: 6991367
Could you use a group by in your query to group on the date?

Paul
0
 

Expert Comment

by:jriver12
ID: 7180303
g118481,

if you are still trying:

see if this is what you need.

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<CFQUERY NAME="testquery" DATASOURCE="dsn">
SELECT     PaymentDate, PaymentAmount, customerNumber
FROM         DenormalizePayments
WHERE     (PaymentDate BETWEEN '07/14/02' AND '07/24/02')
GROUP BY PaymentDate, PaymentAmount, customerNumber
ORDER BY PaymentDate
</CFQUERY>
<body bgcolor="#FFFFFF" text="#000000">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <cfoutput query="testquery"group="paymentdate">
    <tr><td>Date</td></tr>
     <tr>
      <td colspan="3"> #dateformat(paymentdate)#</td>
    <tr>
     <tr><td>CustomerNumber</td> <TD valign="top">Payment Amount</TD></tr>
      <td width="27%"> <cfoutput> #testquery.customernumber#<br>
        </cfoutput> </td>
         
      <td width="73%"><cfoutput>#dollarformat(testquery.PaymentAmount)#<br></cfoutput></td>
    </tr>
    <tr>
      <td colspan="2"></tr>
  </cfoutput>
</table>
</body>
</html>
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question