Finding same job done within 6 months on a unit

[AMBranch]

I am trying to do a report that shows the same job done on a unit within 6 months of the previous job. Here's my SQL:

SELECT b.Unit, b.Job, b.WO, b.[Unit-Job], b.[Unit-Job-WO], b.RepairDate, b!RepairDate-[Repairs]![RepairDate] AS [Delta in Days], [Repairs].WO AS [Previous WO], [Repairs].RepairDate AS [Previous WO Repair Date]
FROM [Repairs] AS b INNER JOIN [Repairs] ON b.[Unit-Job] = [Repairs].[Unit-Job]
WHERE (((b.RepairDate)>[Repairs].[RepairDate]) AND ((![RepairDate]-[Repairs]![RepairDate])<=180))
ORDER BY b.[Unit-Job-WO];

Its giving me the Delta in Days of all the previous jobs not just the most recent job...so if there was four jobs with repair dates of 2/1/06(WO #1), 5/1/06(WO #2) and 7/1/06 (WO #3) and 10/1/06 (WO #4)...for WO #2 the report returns a Delta of 90 days, for WO #3 it returns a Delta of 150 and 60 days (only want 60days) and for WO #4 it returns a Delta of 150 and 90 days (only want 90days..so the report is returning more information than I need.

Any help you can give me would be greatly appreciated.

 

[rac2]

Thats pretty tricky.

Lets make a query that yields one work order per Unit-Job.
This will be the latest work order for that job on the unit and the date of that work order.

SELECT Unit, Job,
       MAX(WO) AS "LatestWO",
       MAX(RepairDate) AS "LatestRepairDate"
FROM Repairs
GROUP BY Unit, Job

I assume that there will be one work order for a Unit-Job on a RepairDate. If so, then the highest numbered work order, MAX(WO) will be the one with the latest repair date.


That query can be JOINed with the Repairs table to find the previous work order.

SELECT a.Unit, a.Job, a.LatestWO, a.LatestRepairDate
       b.WO AS "PreviousWO",
       b.RepairDate AS "PreviousRepairDate"

FROM (
      SELECT Unit, Job,
      MAX(WO) AS "LatestWO",
      MAX(RepairDate) AS "LatestRepairDate"
      FROM Repairs
      GROUP BY Unit, Job
     ) a
JOIN Repairs b ON b.Unit = a.Unit
              AND b.Job = a.Job

WHERE b.RepairDate = (
          SELECT MAX(RepairDate)
          FROM Repairs
          WHERE Unit = a.Unit
            AND Job = a.Job
            AND RepairDate < a.RepairDate
       )

The JOIN yields a row for every work order for a Unit-Job. But the WHERE clause limits the result to the previous one.
The previous one has the maximum repair date which is less than the latest repair date.

At least that is my theory. Let me know.

 

[AMBranch]

Thanks so much for your quick response,
I got parameter boxes for a.RepairDate, a.LatestWO, a.LatestRepairDate so I clicked OK for each without putting values in them and then it just gave me an hour glass for a long period of time...I finally had to abort the run.

I was wondering about the "Delta in days" field which fields and from what tables do that subtraction expression need to include and where would it go in the sql or was this sql suppose to be added to my original sql....should the "PreviousWO" and "PreviousRepairDate" fields be the same as the "LatestWO" and the "LatestRepairDate" fields....any additional assistance you can give me will be greatly appreciated.

 

[PHV]

And what about this ?
SELECT b.Unit, b.Job, b.WO, b.[Unit-Job], b.[Unit-Job-WO], b.RepairDate
, b.RepairDate-a.RepairDate AS [Delta in Days], a.WO AS [Previous WO]
, a.RepairDate AS [Previous WO Repair Date]
FROM Repairs AS b INNER JOIN Repairs AS a ON b.[Unit-Job] = a.[Unit-Job]
WHERE (b.RepairDate-a.RepairDate)<=180
AND a.RepairDate=(SELECT Max(RepairDate) FROM Repairs
WHERE [Unit-Job]=b.[Unit-Job] AND RepairDate<b.RepairDate)
ORDER BY b.[Unit-Job-WO];

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rac2]

oops.

The parameter boxes mean something is wrong. PHV suggests using brackets instead of quotes for the aliases. That might work.

There is an error in my query in the last line. See below in red.
Use the DateDiff() function to calculate the number of days between dates. See below in green.
Create and save a query for the latest work order fields; name it LastRepairs. Then join it instead of using a subquery. See below in blue.

SELECT a.Unit, a.Job, a.LatestWO, a.LatestRepairDate
       b.WO AS "PreviousWO",
       b.RepairDate AS "PreviousRepairDate",
       [COLOR=green]DateDiff("d",b.RepairDate,a.LatestRepairDate) AS "Delta"[/color]


FROM [COLOR=blue]LastRepairs[/color] a
JOIN Repairs b ON b.Unit = a.Unit
              AND b.Job = a.Job

WHERE b.RepairDate = (
          SELECT MAX(RepairDate)
          FROM Repairs
          WHERE Unit = a.Unit
            AND Job = a.Job
            AND RepairDate < [COLOR=red]a.LatestRepairDate[/color]
       )

Create LastRepairs with this query.

SELECT Unit, Job,
       MAX(WO) AS LatestWO,
       MAX(RepairDate) AS LatestRepairDate
FROM Repairs
GROUP BY Unit, Job

PHV query looks promising too. But use the DateDiff() function.

 

[AMBranch]

Thanks for both of your help.

The first approach gave me an hour glass for a long period of time so I had to abort the run.

The second approach yielded too few lines.

I will continue to play with it.

Thanks again for your help.