Need a date sorting expression...

[jesahs]

I just received an assignment at work that I don't think I can figure out on my own. As an info sys intern with a large company (I'm still an undergrad), this is my first "programming" assignment. I've spent 5 hours trying to figure it out while at work today and while I've come up with a general blueprint of what needs to be done, I feel like going around in circles trying to figure out the proper SQL syntax.

If this is an easy problem, my only excuse is that I've only taken one course that had any programming in it; and it wasn't even that much. So, please excuse my ignorance as I try to explain this.

What I need to do, is create a query that brings up all service requests that have been in their current step in the service request life cycle for more than 30 days.

Each service request has an identifying # assigned to it.
Each service request also has multipule "Date_of_Completions" which correspond to the date of its approval before proceeding onto the next step of the service request cycle.

For example:

Service_Request_ID ------- Date_of_Completion -- Step

123 ----------------------- 1/1/05 ------------------ 1
123 ----------------------- 3/3/05 ------------------ 2
123 ----------------------- 11/19/05 ---------------- 3
1234 ---------------------- 11/2/05 ----------------- 1
1234 ---------------------- 12/4/05 ----------------- 2

What I've figured out so far, seems to be this:

1) Since each individual ID has multipule records, filtering out all but the most recent Date_of_Completion should leave me with the necessary date I need to determine if it has been in that step for more than 30 days. After filtering, this should narrow it down to just one record for each Service_Request_ID and its corresponding Date_of_Completion.

2) Next, the remaining Dates need to be run through a date function. This function would subtract the Date_of_Completion from the current date. If the answer is positive, then the service request has been in it's current step for less than. If the answer is negative, then the date has been been in its step for more than 30 days.

That's my plan so far. It seems feasible to me, I'm just not sure how I would work-out the syntax with the expression builder.

Suggestions, hints, or comments are welcome. Many thanks in advance.

 

[r937]

select Service_Request_ID
     , Date_of_Completion
     , Step
     , DateDiff("d",Date_of_Completion,Date())
           AS days_diff
  from yourtable as X
 where Date_of_Completion
     = ( select max(Date_of_Completion)
           from yourtable
          where Service_Request_ID
              = X.Service_Request_ID )
   and DateDiff("d",Date_of_Completion,Date())
    >= 30

r937.com | rudy.ca