Output Correct Information Within Dates Selected

[mijorog]

In my database I have a datediscontinued. When someone runs a a report they select a start date (FORM.StartDate) and an end date (FORM.EndDate). I need to find the records where date discontinued is between the start and end date. If the start date is NEQ to the end date I need to find if there is another entry with a discontinued date and if not then output the entries that have a NULL endate.

I have tried a bunch of different ways. But I can't seem to wrap my head around the right logic/ and syntax to follow. what I a trying now os

<cfset startdate = createDate(2012, 02, 01)>
        <cfset enddate = createDate(2012, 02, 03)>
        <cfset stop = false>
        <cfset go = false>
        <cfoutput>#dateFormat(startdate, "MMMM DD, YYYY")# - #dateFormat(enddate, "MMMM DD, YYYY")#<br /></cfoutput>
        <cfquery datasource="residents#Session.facility#" name="slide">
            SELECT *
            FROM slidingscale
            Where who = 20 and discontinued = 1
            ORDER BY datediscontinued, beginning asc
        </cfquery> 
		<cfif slide.RecordCount NEQ 0>
        <cfquery datasource="residents#Session.facility#" name="slide">
            SELECT *
            FROM slidingscale
            Where who = 20 and datediscontinued >= #startdate#
            ORDER BY datediscontinued, beginning asc
        </cfquery>
				<cfloop query="slide">
        			<cfif slide.datediscontinued eq enddate>
        		 		<cfoutput>
                   		 #slide.beginning# - #slide.ending# <cfif slide.datediscontinued NEQ "">#dateformat(slide.datediscontinued, "mmmm dd, yyyy")#</cfif><br>
                   		</cfoutput>
        			<cfelseif slide.datediscontinued gte startdate>
        		 		<cfoutput>
                   		 #slide.beginning# - #slide.ending# <cfif slide.datediscontinued NEQ "">#dateformat(slide.datediscontinued, "mmmm dd, yyyy")#</cfif><br>
                   		</cfoutput>
              		 		</cfif>
                    </cfloop>
     
			</cfif>
February 01, 2012 - February 03, 2012
query
RESULTSET	
query
 	BEGINNING	DATEDISCONTINUED	DISCONTINUED	DOSE	ENDING	ID	MEDNAME	WHO
1	0	[empty string]	0	11	100	46	Insulin	20
2	101	[empty string]	0	22	150	47	Insulin	20
3	151	[empty string]	0	33	200	48	Insulin	20
4	201	[empty string]	0	44	250	49	Insulin	20
5	0	{ts '2012-02-03 00:00:00'}	1	11	100	39	Insulin	20
6	101	{ts '2012-02-03 00:00:00'}	1	22	150	40	Insulin	20
7	151	{ts '2012-02-03 00:00:00'}	1	33	200	41	Insulin	20
8	0	{ts '2012-02-06 00:00:00'}	1	11	100	42	Insulin	20
9	101	{ts '2012-02-06 00:00:00'}	1	22	150	43	Insulin	20
10	151	{ts '2012-02-06 00:00:00'}	1	33	200	44	Insulin	20
11	201	{ts '2012-02-06 00:00:00'}	1	44	250	45	Insulin	20
CACHED	false
EXECUTIONTIME	0
SQL	SELECT * FROM slidingscale Where who = 20 ORDER BY datediscontinued, beginning asc

I include the dump of what is in my database.
Thanks guys

[arrgh]

1) where date discontinued is between the start and end date
2) If the start date is NEQ to the end date I need to find if there is another entry with a discontinued date
3) if not then output the entries that have a NULL endate.

The 1st one's clear ... but I don't follow the rest of it. Why display records that don't match the filtered dates?

Can you give an example of all 3 cases using your sample data?

[mijorog]

Form.StartDate = 1 Feb 2012
FORM.EndDate = 5 Feb 2012

If these dates are selected I should only output
5 0 {ts '2012-02-03 00:00:00'} 1 11 100 39 Insulin 20
6 101 {ts '2012-02-03 00:00:00'} 1 22 150 40 Insulin 20
7 151 {ts '2012-02-03 00:00:00'} 1 33 200 41 Insulin 20
8 0 {ts '2012-02-06 00:00:00'} 1 11 100 42 Insulin 20
9 101 {ts '2012-02-06 00:00:00'} 1 22 150 43 Insulin 20
10 151 {ts '2012-02-06 00:00:00'} 1 33 200 44 Insulin 20
11 201 {ts '2012-02-06 00:00:00'} 1 44 250 45 Insulin 20

The reason for this is that the first datediscontinued is between start and end dates. Which means I need to find if there is more records with a greater datediscontinued because that means that those were the record used for the remainder of the timeframe.

if Form.StartDate = 4 Feb 2012 And Form.EndDate = 7 Feb 2012
I would need to output
1 0 [empty string] 0 11 100 46 Insulin 20
2 101 [empty string] 0 22 150 47 Insulin 20
3 151 [empty string] 0 33 200 48 Insulin 20
4 201 [empty string] 0 44 250 49 Insulin 20
8 0 {ts '2012-02-06 00:00:00'} 1 11 100 42 Insulin 20
9 101 {ts '2012-02-06 00:00:00'} 1 22 150 43 Insulin 20
10 151 {ts '2012-02-06 00:00:00'} 1 33 200 44 Insulin 20
11 201 {ts '2012-02-06 00:00:00'} 1 44 250 45 Insulin 20
If there is no DB entry with an datediscontinued i can out put all of them

How the person who set this database intended it is that. When someone enters in a record into the DB it is the ones being used. If they change the records those one are discontinued and new entries are add to the DB and the new entries are the active ones.

I can add a datestarted to the DB if it will help to clear things up and make things easier to find.

[arrgh]

Yeah I think it'd be easier if the table had a date range (effective start to end). I have a meeting now so I'll have to check back later.

[mijorog]

I had the datestart added into the DB. He is a dump of what is in the DB now. I am going to try a couple of things but will continually check back to see if you have any good ideas and to also post what I have tried and what works and so for.

Thanks arrgh!

query
RESULTSET
query
BEGINNING DATEDISCONTINUED DATESTART DISCONTINUED DOSE ENDING ID MEDNAME WHO
1 0 {ts '2012-02-03 00:00:00'} {ts '2012-01-01 00:00:00'} 1 11 100 39 Insulin 20
2 101 {ts '2012-02-03 00:00:00'} {ts '2012-01-01 00:00:00'} 1 22 150 40 Insulin 20
3 151 {ts '2012-02-03 00:00:00'} {ts '2012-01-01 00:00:00'} 1 33 200 41 Insulin 20
4 0 {ts '2012-02-06 00:00:00'} {ts '2012-02-03 00:00:00'} 1 11 100 42 Insulin 20
5 101 {ts '2012-02-06 00:00:00'} {ts '2012-02-03 00:00:00'} 1 22 150 43 Insulin 20
6 151 {ts '2012-02-06 00:00:00'} {ts '2012-02-03 00:00:00'} 1 33 200 44 Insulin 20
7 201 {ts '2012-02-06 00:00:00'} {ts '2012-02-03 00:00:00'} 1 44 250 45 Insulin 20
8 0 [empty string] {ts '2012-02-06 00:00:00'} 0 11 100 46 Insulin 20
9 101 [empty string] {ts '2012-02-06 00:00:00'} 0 22 150 47 Insulin 20
10 151 [empty string] {ts '2012-02-06 00:00:00'} 0 33 200 48 Insulin 20
11 201 [empty string] {ts '2012-02-06 00:00:00'} 0 44 250 49 Insulin 20
CACHED false
EXECUTIONTIME 0
SQL SELECT * FROM slidingscale Where who = 20 ORDER BY datestart, beginning asc

[mijorog]

So This seems to be working. I tried every scenario and got the right outputs.

<cfquery datasource="residents#Session.facility#" name="slide">
            SELECT *
            FROM slidingscale
            Where who = 20 
            AND ((datestart <= <cfqueryparam cfsqltype="cf_sql_date" value="#FORM.StartDate#"> AND (datediscontinued <= <cfqueryparam cfsqltype="cf_sql_date" value="#FORM.EndDate#"> AND datediscontinued >= <cfqueryparam cfsqltype="cf_sql_date" value="#FORM.StartDate#">)) OR (datestart >= <cfqueryparam cfsqltype="cf_sql_date" value="#FORM.StartDate#"> AND datestart <= <cfqueryparam cfsqltype="cf_sql_date" value="#FORM.EndDate#">))
            ORDER BY datestart, beginning asc
        </cfquery>

[arrgh]

If I'm reading it correctly, you could simplify it using BETWEEN. I left out the cfqueryparam's for brevity.

WHERE who = 20
AND (
(  datestart <= #FORM.StartDate# AND 
   datediscontinued BETWEEN #FORM.StartDate# AND #FORM.EndDate#
) 
OR 
    datestart BETWEEN #FORM.StartDate# AND #FORM.EndDate# 
) )

.. and I'm not so sure you even need the datestart <= #FORM.StartDate# check. There should probably be a constraint preventing the discontinue date from being earlier than the start date.