Querying 1 Day Old Data ( Date )

[ilikebirds]

My table looks like the following

[PartNumber], [FromStep],[ToStep],[MovedDateValue]
Ex.
[Fish], [Step1], [Step2],[1/2/2007]
[Fish[, [Step2], [Step3],[1/3/2007]

It contains all the [FromStep] and [ToStep] that [PartNumber] goes through in the past 3 months.

How would I go about querying for all [PartNumber]'s that took more than 1 day to move from Step 1 to Step 2?... so on and so forth.

I tried the DateDiff Function and the numbers did not work.

[FishVal]

Hi, there.

You may try to join the table with itself on records where [PartNumber] is the same and [ToStep] in one table alias is the same as [FromStep] in another. Thus you will obtain dataset with records containing dates of sequential steps, then you may use DateDiff() function. Or you may include day difference in JOIN criteria.

Something like this.

SELECT t1.[PartNumber], t1.[MovedDateValue], t2.[MovedDateValue] FROM [YourTableName] AS t1 INNER JOIN [YourTableName] AS t2 ON t1.[PartNumber]=t2.[PartNumber] AND t1.[ToStep]=t2.[FromStep] AND DateDiff("d", t1.[MovedDateValue], t2.[MovedDateValue]) > 1;
 

Regards,
Fish

[ilikebirds]

If I only had half the smarts you did!
That seem to do the trick. Thanks.

Now if I add:

WHERE t1.[to_order_step_num]="Step1";

To the end, I will be able to search specific steps that are =>1, correct?

[FishVal]

If I only had half the smarts you did!
That seem to do the trick. Thanks.

Now if I add:

WHERE t1.[to_order_step_num]="Step1";

To the end, I will be able to search specific steps that are =>1, correct?

If you ever have records where [ToStep]="Step1", then you will get records for Step1->Step2 movements.