date query - got me stumped

[psdiva]

some background: i have 56 rooms that have one of 60 different status' at any time. the status for each location is updated when necessary.
what i NEED is the ability to query from one date TO another date. ok, i can do that.
what i can't do is grab any of the status time posted to a location if the first date is BEFORE the #from# date.

i pass the #from# and #to# dates from a form to an action page.
my query so far:
<cfquery datasource=&quot;mymess&quot; name=&quot;locationA&quot;>
select updt, uptm, nextdt, nexttm
from cs_dyna
where updt BETWEEN #from# and #to#
AND cell = 'A-7'
AND status = 'Active'
</cfquery>

for my output i use the DateDiff to determine the shift time between dates.
is there any way i can output this total for the whole date range selected?

example: from = 6/17/2001
to = 6/24/2001

my record has a date range of 6/15/2001 to 6/18/2001 but the way the query is set now, this record is skipped and my time is not counted.
what i would like to do is change the first date to #from# (6/17/2001) and then figure the elapsed time.

any ideas? i've tried so many things my eyes are crossed. any help will be greatly appreciated! thanx!
psdiva

 

[khazmir]

If I understand you correctly, it sounds like you're only interested in the &quot;to&quot; date not the &quot;from&quot; date. You don't really want the status for a start date through an end date, but the the status up to the end date. If that's true, try this:

where updt <= #to#

Hope this helps

 

[psdiva]

khazmir,
thanks for the reply. i really need the time between the 'from' and 'to' dates.
i use <cfoutput> to figure the DateDiff, add elapsed minutes for all records, then convert that total into a &quot;shift&quot; number. the only thing the user sees is the status total.

this report is used for usage and efficiency graphs. the time spans can change depending on what TPTB want to see.

 

[tlhawkins]

I'll take a stab at it. It sound like what you need is either #UPDT# or #NEXTDT# to be between #From# and #TO#. If I guessed correctly this is how:

<cfquery datasource=&quot;mymess&quot; name=&quot;locationA&quot;>
select updt, uptm, nextdt, nexttm
from cs_dyna
where updt BETWEEN #from# and #to#
OR nextdt BETWEEN #from# and #to#
AND cell = 'A-7'
AND status = 'Active'
</cfquery>

If I guessed wrong also maybe we can get a clarification on the problem.