Need SQL Query help with avg

[athanasiusrc]

Alright SQL fans, I need to create a query for MSSQL that does the following:

Finds the average difference between the date an order entered the shipping
line and either the first partial ship date or total ship date for orders
between specific dates.

The tables are an order table and an order location table that uses the order
primary key. The order location table will have multiple entries per order as
an order goes from received to shipped.

The end result should be a single decimal value.

[philh]

OK. Before I essay a response, I need two things:

1) Your first best try at the query.
2) A schema for the tables, pointing out the key date fields for use in the query.

tks,

[athanasiusrc]

This is a similar query I wrote but since there are multiple possible values
for the first date, I'm not sure how to formulate that.

SET
@OpenOrderAge =
(SELECT
avg(datediff(day, getDate(), orderDate))
FROM
Orders
JOIN
OrderStatuses
ON
OrderStatus_Pk = OrderStatus_Fk
WHERE
Processing = 1)

Here is a schema for the tables with the necessary fields

Orders
Order_Pk (primary key)

OrderLocations
PartialShipment (int),
DefaultShipped (int)
OrderLocation_Pk (primary key)

OrderLocationHistory
Order_Fk (foreign key from Orders)
OrderLocation_Fk (foreign key from OrderLocations)
OrderLocationHistoryTS (datetime field stamped with date order changed
locations)

The OrderLocationHistoryTS for when partiallyshipped = 1 or defaultshipped = 1
needs to be between two dates. The difference will need to be found between the
ship date and the first partially shipped date or the defaultshipped date -
which ever is older.

[philh]

'what's the schema for OrderStatuses? By "partiallyshipped" are you referring to the PartialShipment field in OrderLocations? What table and field holds the ship date?

[athanasiusrc]

Order Statuses contains a text description of the status (Received, In Process,
Shipped First Class, etc.) and a primary key (orderstatus_pk)

The date of shipment is contained in the orderlocationhistory table. One of
the possible options in this table is partially shipped.

I could add a "first shipment" timestamp to the orders table if that would
make the query work better.

[philh]

In your original query, where is the field "OrderDate" from? I thought we
were looking for an average of the difference in final ship date vs earliest
default or partial ship date.

I'm still having trouble understanding how you differentiate "ship date" from
"partial ship date" or "default ship date." Please explain further.

[athanasiusrc]

The original query was a sample that did something similar to what I am doing
now. The orderdate is the date the order was placed but is irrelevant to this
question.

The Orderlocation field has columns for "default shipped" and "partial" that
take 1 or 0 as values. This allows the system to properly choose which location
to use when an order is shipped. If the entire order is shipped, it uses the
"default shipped" row. If the order is only partially shipped, it chooses the
partial row.

I need to know the average time orders in a date range take to go from the
Order Location row "shipping line" to either "partial" or "default shipped".

[philh]

Well.

I had asked for your first best attempt at a query that returns exactly the
results you're seeking, not an unrelated query.

Help me to understand your shipment status and location scheme. Your
OrderLocations table holds two states of shipments (partial and default), but
in your discussion you mention three states (partial, default, and shipped).
How is the actual ship date flagged? Do multiple partials constitute a
complete shipment? Is a "defaultshipped" a single shipment? Can you have both
partial and defaults for the same order?

[athanasiusrc]

The OrderLocationHistory table keeps a record of all the places an order has
been. Other locations can include "pending payment", "partial shelf", etc. An
order can have several partial ship statuses that eventually end in the default
shipped status when the whole order goes out. An order can also skip the
partial ship status and go straight to default shipped if there is only one
shipment.

It is possible that an order could have the following history:
Printer
In process
Shipping line
Partial shipment
Shipping line
default shipped

Would it be easier to put a date stamp in the order itself when an order is
shipped for the first time and then subtract the initial shipping line status
from the order location table?

[philh]

OK, got it.
It may be easier to mark the order, but that wouldn't satisfy your business
rules.

I take it a shipping line entry always precedes a shipment.

Show me the Order Statuses schema and how it relates to the Locations table,
and we can get started on a query.

[athanasiusrc]

OrderStatuses is a more detailed explanation of what was done with an order and
includes things like partially shipped, shipped first class, shipped airmail,
pending payment, cancelled, etc. Apart from different statuses triggering the
orderlocationhistory additions, there isn't a correlation.

For example, all statuses in the orderstatuses table that involve shipped
products have a field "shipped" marked 1. When one of these is chosen, it
triggers the addition of the shipped orderlocation history addition in the
orderlocationhistory table. If a partial status is chosen it triggers the
partially shipped status in the orderlocationhistory.

When I say trigger, I mean that the status is added to orderlocationhistory
through sql logic, not using a trigger statement in the database.

I guess another possible option would be to only use the orderstatuses table
and create a location history from that instead of from a separate table.

[philh]

Originally posted by: athanasiusrc


Finds the average difference between the date an order entered the shipping
line and either the first partial ship date or total ship date for orders
between specific dates.

q]

In what table/field, or combinations thereof, would I determine the state
"shipping line"?

[athanasiusrc]

The OrderLocations table has a column that can either be 1 or 0 for Shipping Line.

[philh]

Originally posted by: athanasiusrc


It is possible that an order could have the following history:
Printer
In process
Shipping line
Partial shipment
Shipping line
default shipped

____________
The OrderLocations table has a column that can either be 1 or 0 for Shipping
Line.


Which column? I don't see a column in your schema that relates to this
information.

I assume from your history example that each line I quoted above is a distinct
entry. How do I tell the difference between "Shipping line" and "partial
shipment"?

[athanasiusrc]

Sorry, here is the whole schema for the orderlocations table

orderlocation_pk int
orderlocation varchar
defaultpending int
defaultshipped int
defaultprinter int
shippingline int
partialshipment int

The various rows are marked either 1 or 0 in these columns to determine what
kind of location they are.

[philh]

OK. I'll pick at this over the weekend.

[athanasiusrc]

I understand that. If the query is too convoluted I can change the way we keep track of shipping dates to make this work better.

Thanks for your help.

[philh]

As long as you can live with a stored procedure, this should be doable. I'll try again today.

[athanasiusrc]

A stored procedure is actually preferable since that is what we use for almost all database access on the site.