CF created Report

[PaulSc]

Hi after picking a few brains please..

I have a sql query which generates data for a report to be produced by ColdFusion (essentially basic table/excel output) however I'm having some fun with the formatting and request some expert advice please.....

data is in format

date , time1, username, value, value...value
date , time2, username, value, value...value
date1 , time, username, value, value...value
date1 , time2, username, value, value...value
date2 , time, username, value, value...value
date2 , time2, username, value, value...value etc

I've been asked to output as

date
time1, user1,value,value...value
time2, user2,value,value,value,value

date1
time1, user1,value,value...value
time2, user2,value,value,value,value

date2
time1, user1,value,value...value
time2, user2,value,value,value,value

etc

Essentially group by date but only print a single date record with multi slots

Any suggestions please???

TIA

PaulSc.

 

[LyndonOHRC]

Is this what you mean?

query="getDate"
select dbDate , dbTime, dbUsername, dbvalue1, dbValue2
From dbTable
Order By dbDate, dbTime, dbUsername

<cfset lastDate=''>
<cfoutput query="getgetDate">
 <cfif lastDate neq DateFormat(getDate.dbDate,'mm/dd/yyyy')>
  <br><br>
  #getDate.dbDate#
  <br>
 </cfif>
 #getDate.dbTime# #getDate.dbUsername# #getDate.dbvalue1# #getDate.dbvalue2#<br>
 <cfset lastDate=DateFormat(getDate.dbDate,'mm/dd/yyyy')>
</cfoutput>

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[PaulSc]

ooohhh Perfect!!! Thank you...