Cycle time report

sbolton1855

I am trying to produce a cycle time report. I believe our jobs table is custom. When part is built (only interested in a few fields in the jobs table), in the Job's table jobs are defined as

[Job table]

Job Type 2= 1 is a New Build
Job Type 2= 2 is a Repair

[Creation Date]
[Completion Date]

---

Sales Invoice Line table
[Asset Serial No_]

Sales Invoice Header table
[Service Date]


I am able to see when a part was used by the [Service Date] in the Sales invoice header table.. So the sequence of events would be triggered off the date differences

New Build part -- look in the jobs table and find where a serial no Job Type 2= 1 -- get [Completion Date] to Look in Sales invoice header and get the first service date that is >= above completion date to look in the jobs table for a repair - Job Type 2= 2 and get the creation date of the first repair that is >= service date above

---

I do not care how long it takes to repair the part

---

grab the above repair's completion date
to
Next service date reported
to
next repairs creation date
to
above repairs completion date
to
next service date

Can anyone help me with the logic to complete this type of report... I started by writing SQL to get the results... I have Jet reports but i am unsure the easiest way to complete the programming to get the results desired...

http://social.msdn.microsoft.com/Forums ... f59f27e2d9

sbolton1855

Bump--- [-o<