3 Replies - 692 Views - Last Post: 23 January 2009 - 02:06 PM Rate Topic: -----

#1 staticz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 14-January 09

Help with SQL query

Posted 23 January 2009 - 10:54 AM

I am having some trouble trying to figure out a way to actually create this query. The data is stored in Access 2007 and maybe there is a better way of doing it, rather than a query, anyways.

Basically the table has every purchase order placed in the last year along with the part number, description, accountnum, orderdate, and recieve date.

Now, We are trying to figure out a way to calculate our average lead time on these orders for one year, 6months, and 3months. The lead time would be our orderdate - recievedate. My problem is calculating the average lead times.

Here is what I have so far.
SELECT partNum, description, accountNo, DateDiff("d",poDate,dateRecvd) AS leadTime
FROM Leadtime;



Anyone have any ideas how to calculate the average leadTime and only show one record of each part?

Is This A Good Question/Topic? 0
  • +

Replies To: Help with SQL query

#2 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Help with SQL query

Posted 23 January 2009 - 01:36 PM

Im not sure how you would get all 3,6,12 month results in one table other than union-ing separate tables but here is an example of how to get the average over the past 180 days(6 months)

select sum(datediff(d,poDate,dateRecvd))/count(*) from Leadtime where dateRecvd>getdate()-180

Was This Post Helpful? 0
  • +
  • -

#3 staticz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 14-January 09

Re: Help with SQL query

Posted 23 January 2009 - 01:58 PM

Thanks for your help. I am still running into problems though, my biggest issue is figuring out a way to calculate the average for each part.

example:
We ordered #005 five times last year, with lead times of 5,2,8,10,and 24.
And we ordered #110 three times last year with lead times of 8,15, and 30.

Is there anyway I can write the query to only calculate the average for every part separately when the information for all the parts is stored in the same table?

This post has been edited by staticz: 23 January 2009 - 02:00 PM

Was This Post Helpful? 0
  • +
  • -

#4 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Help with SQL query

Posted 23 January 2009 - 02:06 PM

Just do some grouping on the query I posted

select partNum, sum(datediff(d,poDate,dateRecvd))/count(*) from Leadtime where dateRecvd>getdate()-180 
group by partNum

This post has been edited by xerxes333: 23 January 2009 - 02:07 PM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1