By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,775 Members | 2,658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,775 IT Pros & Developers. It's quick & easy.

Querying 1 Day Old Data ( Date )

P: 36
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.
Jan 10 '08 #1
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. 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;
  2.  
Regards,
Fish
Jan 10 '08 #2

P: 36
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?
Jan 10 '08 #3

FishVal
Expert 2.5K+
P: 2,653
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.
Jan 10 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.