So I have a database that stores when someone leaves and when they return to.
dayleft
dayreturned

I need to do a query and also an cfif to see if a date selected is between these dates.
test - selected date

the
Here is my long if

<cfif (datecompare(vactrue.dayreturned, test, "yyyy") eq 0 AND datecompare(vactrue.dayreturned, test, "m") eq 0 AND datecompare(vactrue.dayreturned, test, "d") eq 0) 
OR (datecompare(vactrue.dayreturned, test, "yyyy") eq -1)      
OR (datecompare(vactrue.dayreturned, test, "yyyy") eq 0 AND datecompare(vactrue.dayreturned, test, "m") eq 0 AND datecompare(vactrue.dayreturned, test, "d") eq -1)
OR (datecompare(vactrue.dayreturned, test, "yyyy") eq 0 AND datecompare(vactrue.dayreturned, test, "m") eq -1 AND datecompare(vactrue.dayreturned, test, "d") eq -1)
OR (datecompare(vactrue.dayreturned, test, "yyyy") eq 0 AND datecompare(vactrue.dayreturned, test, "m") eq -1 AND datecompare(vactrue.dayreturned, test, "d") eq 0)
OR (datecompare(vactrue.dayreturned, test, "yyyy") eq 0 AND datecompare(vactrue.dayreturned, test, "m") eq -1 AND datecompare(vactrue.dayreturned, test, "d") eq 1)> 

<cfif (datecompare(vactrue.dayleft, test, "yyyy") eq 0 AND datecompare(vactrue.dayleft, test, "m") eq 0 AND datecompare(vactrue.dayleft, test, "d") eq 0) 
OR (datecompare(vactrue.dayleft, test, "yyyy") eq 1)      
OR (datecompare(vactrue.dayleft, test, "yyyy") eq 0 AND datecompare(vactrue.dayleft, test, "m") eq 0 AND datecompare(vactrue.dayleft, test, "d") eq 1)
OR (datecompare(vactrue.dayleft, test, "yyyy") eq 0 AND datecompare(vactrue.dayleft, test, "m") eq 1 AND datecompare(vactrue.dayleft, test, "d") eq 1)
OR (datecompare(vactrue.dayleft, test, "yyyy") eq 0 AND datecompare(vactrue.dayleft, test, "m") eq 1 AND datecompare(vactrue.dayleft, test, "d") eq 0)
OR (datecompare(vactrue.dayleft, test, "yyyy") eq 0 AND datecompare(vactrue.dayleft, test, "m") eq 1 AND datecompare(vactrue.dayleft, test, "d") eq -1)>

Here is my query

<cfquery datasource="residents5" name="hosptrue">
          SELECT *
          FROM hospital
          WHERE ID = 193 AND dayreturned IS NOT NULL 
          AND (DATEDIFF(dayleft, #test#) >= 0 AND DATEDIFF(#test#, dayreturned) <= 0)
          </cfquery>

Neither of these seemed to work and was hoping that someone could enlighten me to my errors. Thanks!!!!!

It's not going to be nearly that complicated.

What's your query used for in plain english? ie Is it a reservation system and you're trying to find out what's booked?

Neither of these seemed to work

Can you give an an example of all 3 dates and what you expect to happen?

I work for an assisted living facility company. We have have a report that shows each day when and who gave their medication. If they arent given their meds we need to indicate why. that being whether a nurse forgot or they were in the hospital or on vacation. When a resident goes on vacation/hospital it is entered into a DB when they left and when they returned. I need to pull that information from that database and check if whatever date we are looking at is between those dates.

I hope that makes sense.

I think so. Can you also post a few examples? Those are better than a 1000 words of description. Something like this


theSelectedDate => 08/30/2011

Sample Data
recordID, dayLeft, dayreturned,
#1, 08/01/2011, NULL
#2, 08/10/2011, 08/16/2011
#3, 08/28/2011, 09/05/2011
....

Which records should the query return and why?

Table = hosptial
Feilds:
ID, residentID, dayleft, dayreturned
#1, 193, 08/01/2011, NULL
#2, 16, 08/10/2011, 08/16/2011
#3, 47, 08/28/2011, 09/05/2011
#4, 193, 07/10/2011, 07/13/2011

selected date = 07/11/2011
residentID = 193
I need find all the records for resident 193 in hospital database and see if my selected date of 07/11/2011 is in between any date ranges in the database. So for this it is in between ID #4.

If it is in between any of the date ranges I need it to output that the resident was in the hostpital.

Sorry about not putting this sooner. I missed it when you asked he first time

Then all you'd need is this query.

ie
<cfset selectedDate = "07/11/2011">

<cfquery name="checkResident" ...>
SELECT *
FROM   hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE  residentID = 193
AND    dayleft <= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
AND    dayreturned >= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
</cfquery>

<!--- if a matching record was found .. --->
<cfif checkResident.recordCount gt 0>
  <cfoutput>resident was in the hospital on #selectedDate#</cfoutput>
</cfif>

Or you could generate a list of all residents in the hospital on date X by eliminating the residentID = 193 clause. Then output the query results.

<cfoutput query="checkResident">
    ... show names ...
</cfoutput>

I ran this one and nothing worked(no output on dates I know they were in the hospital).

ie
<cfset selectedDate = "07/11/2011">

<cfquery name="checkResident" ...>
SELECT *
FROM hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE residentID = 193
AND dayleft <= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
AND dayreturned >= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_timestamp">
</cfquery>

<!--- if a matching record was found .. --->
<cfif checkResident.recordCount gt 0>
<cfoutput>resident was in the hospital on #selectedDate#</cfoutput>
</cfif>

I changed cfsqltype="cf_sql_timestamp" to cfsqltype="cf_sql_date". Doing this now it pertially works. It will not output if the selectedDate = dayleft. Not sure exactly why this is happening.

Any Ideas?

The report this is for single residents so doing the output query you mentioned wont work in this scenario.

I changed cfsqltype="cf_sql_timestamp" to cfsqltype="cf_sql_date"

Shouldn't make a difference. Not unless you're storing times too, not just dates. Are you? Also, what db type?

It will not output if the selectedDate = dayleft.

Let's do some debugging. Grab all records for resident #193. Can you dump the dates only. Then post the results. So we can see why they're not matching?

<cfquery name="checkResident" ...>
SELECT dayleft , dayreturned 
FROM hospital
WHERE residentID = 193
</cfquery>

Debugging stuff <br>
selectedDate = <cfdump var="#selectedDate#">
<cfdump var="#checkResident#">

ok I think that I got it, but it feels like cheating. I moved the where clause you gave me into a cfif statement

<cfif #dateAdd("d", -1, checkResident.dayleft)# LTE #createODBCDate(selectedDate)# AND #createODBCDate(checkResident.dayreturned)# GTE #createODBCDate(selectedDate)#>

You can see that I did a dateAdd to minus one day from the dateleft to fix the problem that it wouldn't recognize when the dayleft and selectedDate were the same.

I am gonna leave this unsolved for about 30 minutes just in case you have another idea besides that. After 30 minutes and if I don't see anything from you arrgh, I will mark it a solved.

you have helped me out greatly. Thank you!!!!!!!!:)

We are storing date/times but not using the times for this report. This is a MYSQL DB

RESULTSET	
query
 	DAYreturned	DAYleft
1	2011-01-18 11:07:26.0	2010-12-10 12:01:13.0
2	2011-08-12 15:30:18.0	2011-08-10 15:17:47.0
3	[empty string]	2011-08-19 16:23:34.0
CACHED	false
EXECUTIONTIME	0
SQL	SELECT dayleft , dayreturned FROM hospital WHERE ID = 193

Since it's not trivial stuff, I'd rather recommend the right code. But without the debugging output, I don't know if what you posted is right or not ;-) If you can post it, I can tell you what went wrong and how to fix it. Correctly.

Oops... our responses keep overlapping. lol

We are storing date/times but not using the times for this report. This is a MYSQL DB

Yep. That explains it :) What #selectedDate# did you actually use in your test? Still "07/11/2011"?

This is what I am working with right now.
selectedDate = 2011-08-10
It matches up with DayLeft but will show that the resident is Not in the hospital.
That is why I did the date add to minus a day and checked it against other residents and it works. I dont think this is the correct way to do it but it is working.

<cfset Adate = createODBCDate(NOW())>
<cfset selectedDate = dateAdd("d", -20, Adate)> 
<cfset selectedDate = #createODBCDate(selectedDate)#>

<cfquery name="checkResident" datasource="residents5">
SELECT *
FROM   hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE  ID = 193
<!---AND    dayout =< <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_date">
AND    dayin >= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_date">
--->
</cfquery>

<cfif checkResident.recordCount gt 0>

<cfif #dateAdd("d", -1, checkResident.dayleft)# LTE #createODBCDate(selectedDate)# AND #createODBCDate(checkResident.dayreturned)# GTE #createODBCDate(selectedDate)#>
  <cfoutput>resident was in the hospital on #selectedDate#</cfoutput>
  <cfelse>
  <cfoutput>resident was NOT in the hospital on #selectedDate#</cfoutput>
</cfif>       
</cfif>

Oh, no. Don't use that code. If it works, it's pure luck ;-)

What's this "dayout" and "dayin" value? Are you using those columns or just DAYreturned and DAYleft?

It is just dayleft and day returned. They used to be dayout and dayin but it was rather confusing, I didnt set up the database originally. I had to change the around so people understood what was in them.

I am confuse by what you mean with

Oh, no. Don't use that code. If it works, it's pure luck ;-)

<cfif #dateAdd("d", -1, checkResident.dayleft)

That only checks whatever dates happen to be in the 1st record of the query. Remember you removed the WHERE clause. So there might be more than 1 record returned. For accurate results you'd have to cfloop through the query to check all of them.

The original sql didn't work because I didn't know the data contained times too. So it was the wrong the comparison. Just adjust it to account for the time.

Example: if today is 8/30/2011, #selectedDate# will be 8/10/2011 and #dayAfter# will be 8/11/2011.

<cfset todaysDate = createODBCDate(NOW())>
<cfset selectedDate = dateAdd("d", -20, todaysDate)> 
<cfset dayAfter     = dateAdd("d", 1, selectedDate)>

<cfquery name="checkResident" datasource="residents5">
SELECT *
FROM hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE ID = 193
AND   dayLeft    < <cfqueryparam value="#dayAfter#" cfsqltype="cf_sql_date">
AND   dayReturn > <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_date">
</cfquery>
commented: Extremely Smart!! +1

You my friend are a genius!!!

I have one more question. What should I do if the dayreturned is NULL. Like from the cfdump we did that the resident is currently in the hospital. It is not showing that the resident is in the hospital.

Ok so I did this It seems to be working

<cfquery name="checkResident2" datasource="residents5">
SELECT *
FROM hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE ID = 193
and dayreturned IS NULL
AND   dayleft  < <cfqueryparam value="#dayAfter#" cfsqltype="cf_sql_date">
</cfquery>

I just ran a second query looking for dayreturned IS NULL and kept the dayleft where statement. It looks right. Again arrgh I cant thank you enough!!!!!

I just ran a second query looking for dayreturned IS NULL and kept the dayleft where statement

Yep, that'll do it! Good job.

If you want everything in 1 query. Just do somethin like this.

WHERE ID = 193 
AND dayLeft < <cfqueryparam value="#dayAfter#" cfsqltype="cf_sql_date">
AND ( <!--- using greater than or equals to also include selectedDate at midnight exactly --->
    dayReturn >= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_date"> 
OR  dayReturn IS NULL 
    )