CODEWITHSUNDEEP
coldfusioncoldfusion-9cfml
Merle_the_Pearl
Merle_the_Pearl

Reputation: 1521

Displaying CFQuery Result Over Time Span

I'm able to get query's over a time span of say 1hr - in 15 minute increments...

I am just trying to stop displaying repetitive result when the Primary Key is the same.

ie: If something starts at 6:45am - and ends at 8:00am - I only want it to span the DIV once if the primary key (masterid) is the same. And then if something else is at 8am with a different primary key - to span that div time frame etc...

I am contemplating a cfloop or the like - checking that the mastered is either the same or different/

Thoughts on how to do this...

Query code working fine - as is my display code... Image included to give idea of what I'm trying to do.

Output

    <cfloop index="incr" from="0" to="#loopreps#">
    <cfoutput>

    Loopreps is thru the time spans of 15 mins...

     <cfquery name="scht" datasource="#ds#">
 Proper Query
 </cfquery>

    <cfif scht.recordcount is not 0>
    <cfset mid = #scht.masterid#>
    This is where I am lost to hold it to only 1 result when spanning time


    Proper Display Across Div Height Span once if MasterID is same

    </cfif>

   </cfoutput>
   </cfloop>

Upvotes: 1

Views: 182

Answers (3)

Leigh
Leigh

Reputation: 28873

As noted in the comments, as a desktop database, MS Access is rather limited. Enterprise databases like SQL Server offer much greater capabilities for tasks like this, such as using CTE's.

However, just to offer another perspective, you could also use an auxiliary table of times, instead. (That was a common approach in SQL Server, prior to the advent of CTE's). You could easily populate a table with fifteen minute increments, between 00:00 to 23:45, using Mark's loop as a basis. Then simply JOIN to that table on the scheduled start and end times. (Access requires the extra parenthesis and derived table).

SELECT ti.IntervalTime
        , s.StartTime
        , s.EndTime
        , s.AppointmentName
FROM  TimeInterval ti LEFT JOIN 
        (
            SELECT AppointmentName, StartTime, EndTime
            FROM   ScheduleTable 
            WHERE  ScheduleDate = <cfqueryparam value="#someDate#" cfsqltype="cf_sql_timestamp">    
        ) s
        ON ( 
             ti.IntervalTime >= s.StartTime AND 
             ti.IntervalTime <= s.EndTime 
        )
WHERE ti.IntervalTime >= <cfqueryparam value="#fromTime#" cfsqltype="cf_sql_timestamp">
AND   ti.IntervalTime <= <cfqueryparam value="#toTime#" cfsqltype="cf_sql_timestamp">
ORDER BY ti.IntervalTime

The result will contain all of the intervals and appointments in one query, no need for looping. You can then output the results however you need. 

IntervalTime | StartTime | EndTime  | Appointment Name
06:00:00     |           |          | 
06:15:00     |           |          | 
06:30:00     |           |          | 
06:45:00     | 06:45:00  | 08:00:00 | Edge 
07:00:00     | 06:45:00  | 08:00:00 | Edge 
07:15:00     | 06:45:00  | 08:00:00 | Edge 
07:30:00     | 06:45:00  | 08:00:00 | Edge 
....

Upvotes: 3

Mark A Kruger
Mark A Kruger

Reputation: 7193

You can loop over time increments directly using the CFLOOP tag. For example this code outputs each 15 minutes. You could combine a loop like this with a check (Q of a Q maybe) to extract availability.

<cfloop index="tm" from="8:00 AM" to="5:00 PM" step="#createTimespan(0,0,15,0)#">    
    <cfoutput> <li>#TimeFormat( tm, "h:mm TT" )#</li></cfoutput>
</cfloop>

Just keep in mind this is an ok solution for short iterations. If doing long iterations I would set that "step" attribute to a variable rather than calling a function directly. If really long I would probably choose something else. But for a couple days worth of increments it will work perfectly fine I think. See this post on Interesting loop for date and time. Good luck!

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

I usually put a garbage value before starting a loop.

<cfset CompareValue = "value that will never occur in real life">
<cfloop>
<cfif FieldToCheck is not CompareValue>
<cfset CompareValue = FieldToCheck>
more code
<cfelse>
appropriate code, maybe nothing
</cfif>
</cfloop>

Upvotes: 1

Related Questions