Using datediff in a non-normalized database

[galaxy]

Surely someone has done this, but I am having a lot of trouble figuring out the correct keywords to search for.

I have a SharePoint list that tracks the date/time of each stop of an order from its origin to its destination. The number of stops varies depending on the type of order (you can use train as a metaphor if that helps). The goal is to calculate the average time between each stop (we're trying to figure out where orders typically get held up).

Let's say there are stops A, B, C, D, and E. Every order starts at A and ends at E. If they all made every stop, I could easily get the average of datediff("d", B, A) etc.

However, since they make different numbers of stops, what I need is the difference between the stop and the last stop they actually made. I need a crosstab query (or report) that lists the OrderType as the row, the StopName as the column, and the average(datediff) as the value.

In a perfect world, this would be normalized and I'd have separate tables for the stops and the order types, but trying to do that through SharePoint is Not Fun and is going to confuse the end users.

 

[plog]

Can you provide sample data from your table? Include table name, field names and also include what you want the final output to be based on the sample data you provide.

 

[galaxy]

plog

I have a table with the fields OrderNumber (primary key), DateSubmitted, DatePOCreated, DateApprovedPurchasing, DateApprovedFinance, DateApprovedExec, DateSent, DateDelivered

Every record has an OrderNumber, DateSubmitted, and DateDelivered. But not all orders have to be approved by Purchasing, Finance, and Exec so those fields may be blank.

An example record:
OrderNumber XYZ123
DateSubmitted 2/3/2013 8:00 AM
DatePOCreated 2/4/2013 9:00 AM
DateApprovedPurchasing 2/5/2013 8:00 AM
DateApprovedFinance (blank)
DateApprovedExec (blank)
DateSent 2/7/2013 8:00 AM
DateDelivered 2/10/2013 11:00 AM


What I want to end up with is:

Average time between DateSubmitted to POCreated - 3 days
Average time between DatePOCreated to DateApprovedPurchasing - 2 days
Average time between DateApprovedPurchasing and DateApprovedFinance - 4 days
etc

What I am ending up with (using the example record above) is things like

Time between DateApprovedExec and DateSent is 0 (because DateApprovedExec is blank - but I don't want those records factored into the average)
I'm starting to confuse myself, but hopefully you can make some sense of it.

 

[plog]

Time between DateApprovedExec and DateSent is 0 (because DateApprovedExec is blank - but I don't want those records factored into the average)

If you use the Avg() function in an aggregate query it will exclude blank records. This SQL should do it:

SELECT Avg(DateDiff("d",[DateSubmitted],[DatePOCreated])) AS Submitted_PO, Avg(DateDiff("d",[DatePOCreated],[DateApprovedPurchasing])) AS PO_Purchasing, Avg(DateDiff("d",[DateApprovedPurchasing],[DateApprovedFinance])) AS Purchasing_Finance
FROM YourTableNameHere;

You didn't include your table name, so replace every instance of 'YourTableNameHere' with your table's name.

If it doesn't work, please post some sample records that cause it to fail.

 

[galaxy]

Yeah, that's the same SQL I had before. The problem is that if no PO is created, then the next step (PO_Purchasing) is 0. So it could take three weeks from DateSubmitted to DateApprovedPurchasing and we would have no idea from the report that there was a delay.

I attached a sample database to show you what I mean. There are two orders (foo and bar) that took more than a month to get from submitted to purchasing, but you'd never know it from the query because it is only averaging the orders that actually made the steps.

 

[plog]

The problem is that if no PO is created, then the next step (PO_Purchasing) is 0

No, its actually Null, there's a difference. It seems your moving the target. You wanted average time between events and to exclude those records without data. My query gave that. Now you are talking about looking at specific records and not averages.

If you want time between submitted and purchasing use this:

Submitted_Purchasing: Avg(DateDiff("d",[DateSubmitted],[DateApprovedPurchasing]))

 

[galaxy]

Okay, thanks, you did do precisely what I asked for.

For future searchers, this is how I would have structured the data in a perfect world, alas, I'm hamstrung by needing to use SharePoint.

Tbl_orderinfo
OrderID
UserID
RegionID
CountryID

Tbl_users
UserID
UserName

Tbl_regions
RegionID
RegionName

Tbl_countries
CountryID
RegionID
CountryName

Tbl_steps
StepID
StepDescrip

Tbl_ordersteps
OrderID
StepID
StepDate