Date Range with Null Records

I have this query that I am using to bring back a range of data: SELECT ENROLLMENTS, FOLLOWUPS, OUTCOMES, REPORTDATE FROM dailyActivity where reportDate BETWEEN '2010-10-09' AND '2010-10-12' AND traxUserID = XXXXXX My dilemma is that I want to bring back a record for the date that does not have any records. How can I do that? TIA

> I want to bring back a record for the date that does not have any records. If you re-read that, it sounds like you want to magically invent data out of thin air? :P Do you mean that, in addition to currently returned data, you also want to include records that have a null/unknown date value? (So they're not in the specified range, but they're not outside either.) If so, you just need to do an OR inside parentheses: SELECT <columns> FROM dailyActivity WHERE traxUserID = XXXXXX AND (       reportDate BETWEEN '2010-10-09' AND '2010-10-12'     OR       reportDate IS NULL     )

----- Excess quoted text cut - see Original Post for more ----- Well there are no records for 10/10/2010 but I need to include that date in the results with no records. Because it is not returned by the query, it is not displayed in the output. Since it is within the range, can I somehow include it in the query results?

The question you have to ask yourself is "what is a record?" Right now, the query is basing a record on the fact that the WHERE condition is true which would be there is a reportdate between date1 and date2 for traxuserid=xxxxx If you are not pulling date 10/10/2010, then chances are the traxuserid does not match xxxxx exactly OR the data that is in reportdate is not a datetype data, so the date comparison isn't working unless you convert the data to date format. George ----- Excess quoted text cut - see Original Post for more -----                 

SELECT ENROLLMENTS, FOLLOWUPS, OUTCOMES, REPORTDATE FROM dailyActivity where (                      reportDate BETWEEN '2010-10-09' AND '2010-10-12'                or                      reportDate is null              ) AND traxUserID = XXXXXX Ray Thompson Tau Beta Pi Headquarters 865-546-4578 www.tbp.org I have this query that I am using to bring back a range of data: SELECT ENROLLMENTS, FOLLOWUPS, OUTCOMES, REPORTDATE FROM dailyActivity where reportDate BETWEEN '2010-10-09' AND '2010-10-12' AND traxUserID = XXXXXX My dilemma is that I want to bring back a record for the date that does not have any records. How can I do that? TIA

----- Excess quoted text cut - see Original Post for more ----- >TIA

I really just need to access all of the dates in the submitted date range. Is there a way to do this?

On 3/18/2011 6:09 AM, Torrent Girl wrote: > I really just need to access all of the dates in the submitted date range. Is there a way to do this? Are there records in your database for all dates?  Or do you want your SQL to make up phantom records in the result set for dates that have no data?

----- Excess quoted text cut - see Original Post for more ----- data? I want SQL to make up phantom records in the result set for dates that have no data? I think I amy have a soluttion tho. <cfloop from="#form.startDate#" to="#form.endDate#" index="i"> <cfoutput>#dateformat(i, "yyyy-mm-dd")#<br /></cfoutput> </cfloop> I really just needed to access the date in the date range to be used in subsequent queries if the date wasn't included in the first query.

If I understand this correctly, I think you can get what you want by doing a UNION on all the days within your date range minus the days returned in your original query. Since it seems like you are using Coldfusion, it's easier to accomplish than writing it straight SQL. SELECT DATE, etc FROM TABLE       UNION       (CFLOOP to generate All dates within your date range)       SELECT DATE from SQL where date between your date range             and DATE NOT IN             repeat your main query here to remove duplicate dates (SELECT DATE, etc FROM TABLE ) Hope it helps or opens up an idea! ----- Excess quoted text cut - see Original Post for more ----- data? I want SQL to make up phantom records in the result set for dates that have no data? I think I amy have a soluttion tho. <cfloop from="#form.startDate#" to="#form.endDate#" index="i"> <cfoutput>#dateformat(i, "yyyy-mm-dd")#<br /></cfoutput> </cfloop> I really just needed to access the date in the date range to be used in subsequent queries if the date wasn't included in the first query.

I really just need to access all of the dates in the submitted date range. Is there a way to do this?

----- Excess quoted text cut - see Original Post for more ----- Thanks guys, Adding the is null line still doesn't bring back 10/10/2010 as a empty row.

This got me what I needed: <cfoutput> <cfloop from="#form.startDate#" to="#form.endDate#" index="i">     <cfinvoke component="cfc.alere" method="getProductivity" returnvariable="productivity">         <cfinvokeargument name="traxUserID" value="#session.user.TRAXUSERID#">             <cfinvokeargument name="reportDate" value="#dateformat(i, "yyyy-mm-dd")#">     </cfinvoke> </cfloop> </cfoutput> Now, all dates within the range are being returned whether there is data or not *BIG SMILE* Thanks for everyone's help.