Select row by ID to nearest date, future (or past if no future dat

[Trexner]

Hello all,

I have a table in a database named "tm_tasks" that contains records of tasks
to be carried out on a case. The end user enters the tasks from a form which
includes a future date by which the task must be completed. The date is
stored in the column "CASE_DUE_DATE". The structure of the table is:

TASK_ID (autonumber field)
CLIENT_ID (link back to another table holding the client specific data)
CASE_ID (link back to another table holding the case specific data)
CASE_DATE_ENTERED (the date the task is created - defaults to "Date() )
CASE_DAYS_OUT (the number of days before task must be completed)
CASE_DUE_DATE (calculated as a DateAdd of the two above)
CASE_TASK (memo field of what the end user must to do complete the task)
CASE_TASK_COMPLETED (yes/no field to be ticked when task is completed)

The tm_tasks table will have multiple rows for each case as the end user
will put in several tasks with future dates at one time. Additionally, as
the tasks are completed and the user ticks the completed task box, the old
tasks will remain until the case is completely closed and the rows are moved
to an archive at a later time.

I've had no problem building a task report showing all tasks, sorted by the
task closest to today's date from which the end user can prioritize their
day/week/month schedule.

What I'm trying to do is build a query that will show ONLY one task per open
case (CASE_ID). I need it to check for future tasks, and if there are future
tasks, select the row that has the task that is closest to today (aka...the
next task that needs to be completed for that case). AND, if there are no
future tasks, it needs to select the row of the most recent past task. The
result will be a status report that shows the most current task on the case
(and only one task), the next future if there is one, or the most recent past
if no future tasks exist.

Once I get the query to select only one row for each case by the nearest
date, future or past, I will then add in a fair number of joins to include
additional details from other tables But first I have to get it to select
the correct row, and only one row, for each CASE_ID based on the nearest
date, future if available, past if not.

Thanks in advance!

 

[John W. Vinson]

Hello all,

I have a table in a database named "tm_tasks" that contains records of tasks
to be carried out on a case. The end user enters the tasks from a form which
includes a future date by which the task must be completed. The date is
stored in the column "CASE_DUE_DATE". The structure of the table is:

TASK_ID (autonumber field)
CLIENT_ID (link back to another table holding the client specific data)
CASE_ID (link back to another table holding the case specific data)
CASE_DATE_ENTERED (the date the task is created - defaults to "Date() )
CASE_DAYS_OUT (the number of days before task must be completed)
CASE_DUE_DATE (calculated as a DateAdd of the two above)
CASE_TASK (memo field of what the end user must to do complete the task)
CASE_TASK_COMPLETED (yes/no field to be ticked when task is completed)

You're storing data redundantly and inappropriately storing calculated fields.

I would suggest removing Case_Days_Out from this table altogether - if it is
entered today, *ITS VALUE WILL BE WRONG* tomorrow. Instead, just store
CASE_DUE_DATE - you can use an unbound textbox for CASE_DAYS_OUT to
dynamically calculate it - but you should certainly not store both fields.

The tm_tasks table will have multiple rows for each case as the end user
will put in several tasks with future dates at one time. Additionally, as
the tasks are completed and the user ticks the completed task box, the old
tasks will remain until the case is completely closed and the rows are moved
to an archive at a later time.

I've had no problem building a task report showing all tasks, sorted by the
task closest to today's date from which the end user can prioritize their
day/week/month schedule.

What I'm trying to do is build a query that will show ONLY one task per open
case (CASE_ID). I need it to check for future tasks, and if there are future
tasks, select the row that has the task that is closest to today (aka...the
next task that needs to be completed for that case). AND, if there are no
future tasks, it needs to select the row of the most recent past task. The
result will be a status report that shows the most current task on the case
(and only one task), the next future if there is one, or the most recent past
if no future tasks exist.

Once I get the query to select only one row for each case by the nearest
date, future or past, I will then add in a fair number of joins to include
additional details from other tables But first I have to get it to select
the correct row, and only one row, for each CASE_ID based on the nearest
date, future if available, past if not.

What if there's a tie - three tasks all due on the same day? Pick an arbitrary
row?

The "if then" logic (next future if there is one OR most recent if there's
not) make the query more complicated, but a TOP VALUES query should work, if
you sort both by the date and the future/past:

SELECT TOP 1 CLIENT_ID, CASE_ID, CASE_DATE_ENTERED, CASE_DUE_DATE, CASE_TASK,
CASE_TASK_COMPLETED
FROM TM_TASKS
ORDER BY IIF([Case_Due_Date] > Date(), 1, -1), DateDiff("d", Date(),
[Case_Due_Date]) * IIF([Case_Due_Date] > Date(), 1, -1);

Untested and a bit tricky but should be a start...!

 

[Jason Hoag]

Hi and thanks for the reply.

I tried the query that you recommended and it didn't work. The result was a single row selected to the exclusion of all others. I need a single row selected per CASE_ID.

I see what you mean about storing the CASE_DAYS_OUT. Once that data is captured as input from the user and used to calculate the future due date, it can be dropped. I'll rework the table to eliminate that column, though I think I'll have to change the way I do the calculation in the form.

So, why would it select one row only rather than selecting a row for every case ID?

The one row it selected was the oldest entry in the table, which as it turns out, is past due. No future dates were selected for the same CASE_ID even though there are future dates. And, also, no row selections were made for the other cases.

This has had me stumped for a while now.

I appreciate the help, and again, thanks in advance for any additional help.

Trexner



John W. Vinson wrote:

wrote:You're storing data redundantly and inappropriately storing calculated
04-Nov-09

wrote

You're storing data redundantly and inappropriately storing calculated fields

I would suggest removing Case_Days_Out from this table altogether - if it i
entered today, *ITS VALUE WILL BE WRONG* tomorrow. Instead, just stor
CASE_DUE_DATE - you can use an unbound textbox for CASE_DAYS_OUT t
dynamically calculate it - but you should certainly not store both fields


What if there is a tie - three tasks all due on the same day? Pick an arbitrar
row

The "if then" logic (next future if there is one OR most recent if there i
not) make the query more complicated, but a TOP VALUES query should work, i
you sort both by the date and the future/past

SELECT TOP 1 CLIENT_ID, CASE_ID, CASE_DATE_ENTERED, CASE_DUE_DATE, CASE_TASK
CASE_TASK_COMPLETE
FROM TM_TASK
ORDER BY IIF([Case_Due_Date] > Date(), 1, -1), DateDiff("d", Date()
[Case_Due_Date]) * IIF([Case_Due_Date] > Date(), 1, -1)

Untested and a bit tricky but should be a start...
-

John W. Vinson [MVP]

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET: Using SQLite with Enterprise Library 3.1
http://www.eggheadcafe.com/tutorial...41c-f4dadf9b7127/aspnet-using-sqlite-wit.aspx

 

[John W. Vinson]

Hi and thanks for the reply.

I tried the query that you recommended and it didn't work. The result was a single row selected to the exclusion of all others. I need a single row selected per CASE_ID.

I see what you mean about storing the CASE_DAYS_OUT. Once that data is captured as input from the user and used to calculate the future due date, it can be dropped. I'll rework the table to eliminate that column, though I think I'll have to change the way I do the calculation in the form.

So, why would it select one row only rather than selecting a row for every case ID?

Because I misinterpreted your question... sorry about that!!!

The one row it selected was the oldest entry in the table, which as it turns out, is past due. No future dates were selected for the same CASE_ID even though there are future dates. And, also, no row selections were made for the other cases.

This has had me stumped for a while now.

I appreciate the help, and again, thanks in advance for any additional help.

You'll need a fairly hairy correlated Subquery I fear. I'm too sleepy tonight
to work it out but I'll mark this thread and try to come back to it tomorrow.
Basically you'll need to use the query I suggested (or one like it) as a
Subquery returning a calculated field. The resulting query will certainly not
be updateable, if that matters to you.

 

[Jason Hoag]

You'll need a fairly hairy correlated Subquery I fear. I'm too sleepy tonight
to work it out but I'll mark this thread and try to come back to it tomorrow.
Basically you'll need to use the query I suggested (or one like it) as a
Subquery returning a calculated field. The resulting query will certainlynot
be updateable, if that matters to you.

I appreciate anything you can do.

At your suggestion, I did remove the column tm_tasks.CASE_DAYS_OUT and
am just calculating it via an unbound text box.

So, for what it's worth, here is the SQL that I have that will
actually gather all of the needed data from all of the related tables
(just to show the various joins).

SELECT tm_tasks.CASE_DUE_DATE, tm_clients.CL_FULL_NAME,
tm_cases.CASE_TYPE, tm_cases.CASE_OPEN_DATE, tm_counties.COUNTY_NAME,
tm_tasks.CASE_TASK
FROM (tm_tasks LEFT JOIN (tm_cases LEFT JOIN tm_clients ON
tm_cases.CLIENT_ID = tm_clients.CLIENT_ID) ON tm_tasks.CASE_ID =
tm_cases.CASE_ID) LEFT JOIN tm_counties ON tm_cases.COUNTY_ID =
tm_counties.COUNTY_ID
WHERE (((tm_cases.CASE_CLOSED)=0))
ORDER BY tm_tasks.CASE_ID;

This, of course, simply lists all of the cases with all of their
tasks. As a reminder, what I'm needing is to see one task for each
case...either the case task with the nearest future date, or if no
future dates exist, the case task of the nearest past date.

As before, I really appreciate the assistance with this query that
has, thus far, stumped me for well over a week now.

Cheers,

Trexner