Select Query Help Needed

6 Sep 2006 7:50 PM

webdevjohn

I have a table that tracks the status of an order as it is being
processed. As the order moves through the system, each station that
handles the order updates it's location.

My table layout is as follows:

Table Name: status
statusId (int - identity)
orderId (int)
stationDate (datetime)
station (char 20)

An order may go through the following stations:

Order #12345   9/1/06 2:45 PM   Sales In
Order #12345   9/1/06 2:52 PM   Sales Out
Order #12345   9/1/06 3:14 PM   Accounting In
Order #12345   9/1/06 4:45 PM   Accounting Out
Order #12345   9/2/06 3:33 AM   Warehouse In
Order #12345   9/2/06 5:12 AM   Warehouse Out
Order #12345   9/2/06 7:22 AM   Accounting In
Order #12345   9/2/06 7:46 PM   Accounting Out
Order #12345   9/2/06 9:44 AM   Shipping In
Order #12345   9/2/06 3:43 PM   Shipping Out

I want to be able to query all orders that are in a specific station
and that have not moved on to another station. For example, select all
orders where station = 'Accounting In' and do not have any entries
after the order went into Accounting In. The purpose of this is to
give each person a "to do" list of orders that are currently at their
station.

This seems like such a simple thing but I just can't figure it out. Any
help would greatly be appreciated.

6 Sep 2006 8:11 PM

Roy Harvey

Lets start my making a view that only shows the "current" status:

CREATE VIEW status_CurrentV
AS
SELECT A.*
FROM status as A
WHERE stationDate =
       (select MAX(stationDate)
          from status as B
         where A.orderId = B.orderId)
GO

Now we can simply query that view:

SELECT *
FROM status_CurrentV
WHERE station = 'Accounting In'

Of course we did not need a view, but it makes the query simple and
you only have to write it once, while it can be used in many different
queries.

Roy Harvey
Beacon Falls, CT

Show quote

On 6 Sep 2006 12:50:14 -0700, "webdevjohn" <sd4Web***@gmail.com>
wrote:

>I have a table that tracks the status of an order as it is being
>processed. As the order moves through the system, each station that
>handles the order updates it's location.
>
>My table layout is as follows:
>
>Table Name: status
>statusId (int - identity)
>orderId (int)
>stationDate (datetime)
>station (char 20)
>
>An order may go through the following stations:
>
>Order #12345   9/1/06 2:45 PM   Sales In
>Order #12345   9/1/06 2:52 PM   Sales Out
>Order #12345   9/1/06 3:14 PM   Accounting In
>Order #12345   9/1/06 4:45 PM   Accounting Out
>Order #12345   9/2/06 3:33 AM   Warehouse In
>Order #12345   9/2/06 5:12 AM   Warehouse Out
>Order #12345   9/2/06 7:22 AM   Accounting In
>Order #12345   9/2/06 7:46 PM   Accounting Out
>Order #12345   9/2/06 9:44 AM   Shipping In
>Order #12345   9/2/06 3:43 PM   Shipping Out
>
>I want to be able to query all orders that are in a specific station
>and that have not moved on to another station. For example, select all
>orders where station = 'Accounting In' and do not have any entries
>after the order went into Accounting In. The purpose of this is to
>give each person a "to do" list of orders that are currently at their
>station.
>
>This seems like such a simple thing but I just can't figure it out. Any
>help would greatly be appreciated.

7 Sep 2006 1:01 AM

webdevjohn

Thank you both for your ideas!

Roy - I tested your code and it works and fits perfectly into my
situation. I'm going to drop it into a stored procedure and pass in
the station to get the result set. I'm still fairly new with SQL.
I've been struggling with this for over a month - never thought about
doing a subquery on the same table!

Roy Harvey wrote:
Show quote

> Lets start my making a view that only shows the "current" status:
>
> CREATE VIEW status_CurrentV
> AS
> SELECT A.*
>   FROM status as A
> WHERE stationDate =
>        (select MAX(stationDate)
>           from status as B
>          where A.orderId = B.orderId)
> GO
>
> Now we can simply query that view:
>
> SELECT *
>   FROM status_CurrentV
> WHERE station = 'Accounting In'
>
> Of course we did not need a view, but it makes the query simple and
> you only have to write it once, while it can be used in many different
> queries.
>
> Roy Harvey
> Beacon Falls, CT
>
>
> On 6 Sep 2006 12:50:14 -0700, "webdevjohn" <sd4Web***@gmail.com>
> wrote:
>
> >I have a table that tracks the status of an order as it is being
> >processed. As the order moves through the system, each station that
> >handles the order updates it's location.
> >
> >My table layout is as follows:
> >
> >Table Name: status
> >statusId (int - identity)
> >orderId (int)
> >stationDate (datetime)
> >station (char 20)
> >
> >An order may go through the following stations:
> >
> >Order #12345   9/1/06 2:45 PM   Sales In
> >Order #12345   9/1/06 2:52 PM   Sales Out
> >Order #12345   9/1/06 3:14 PM   Accounting In
> >Order #12345   9/1/06 4:45 PM   Accounting Out
> >Order #12345   9/2/06 3:33 AM   Warehouse In
> >Order #12345   9/2/06 5:12 AM   Warehouse Out
> >Order #12345   9/2/06 7:22 AM   Accounting In
> >Order #12345   9/2/06 7:46 PM   Accounting Out
> >Order #12345   9/2/06 9:44 AM   Shipping In
> >Order #12345   9/2/06 3:43 PM   Shipping Out
> >
> >I want to be able to query all orders that are in a specific station
> >and that have not moved on to another station. For example, select all
> >orders where station = 'Accounting In' and do not have any entries
> >after the order went into Accounting In. The purpose of this is to
> >give each person a "to do" list of orders that are currently at their
> >station.
> >
> >This seems like such a simple thing but I just can't figure it out. Any
> >help would greatly be appreciated.

7 Sep 2006 1:47 AM

--CELKO--

>> I'm still fairly new with SQL. I've been struggling with this for over a month - never thought about doing a subquery on the same table! <<

You might want to get a few of my books to help with the paradigm shift
-- hell, I need the money!

After teaching SQL for a few decades, the biggest things are:

1) Learning that files are not tables, columns are not field and rows
are not records. Big jump in abstraction here! Separate the physical
and the logical; use only the logical model.

2) How to think in sets and not sequences. This is almost Zen and I
have some examples in SQL PROGRAMMING STYLE that shows the principles.
Remember learning recursion and the zatori it took to be able to use
it?

7 Sep 2006 2:07 PM

Hilarion

Roy Harvey write:

> Lets start my making a view that only shows the "current" status:
>
> CREATE VIEW status_CurrentV
> AS
> SELECT A.*
> FROM status as A
> WHERE stationDate =
>       (select MAX(stationDate)
>          from status as B
>         where A.orderId = B.orderId)
> GO

This may cause some problems if any order changed it's status
twice in the exact same moment (I mean exact same value in
"stationDate" field). It'll probably not happen unless
the "stationDate" values are filled manually (not by for
example "GetDate()" function). If it causes problems and
the status changes are entered chronologicaly, then one
may try to use "statusId" instead of "stationDate" (the
problem will appear if "stationId" gets recycled).

Kamil 'Hilarion' Nowicki

6 Sep 2006 9:04 PM

Andrew Petrochuk [MSFT]

something like that might work too:

select * from dbo.status s1
where RIGHT(RTRIM(station), 3) = N' In'
AND NOT EXISTS(select s2.statusId from dbo.status s2 where
RTRIM(s2.station) = LEFT(RTRIM(s1.station), LEN(RTRIM(s1.station)) - 3) + '
Out')

--

This posting is provided "AS IS" with no warranties, and confers no rights.

Show quote

"webdevjohn" <sd4Web***@gmail.com> wrote in message
news:1157572214.374831.73040@i3g2000cwc.googlegroups.com...
>I have a table that tracks the status of an order as it is being
> processed. As the order moves through the system, each station that
> handles the order updates it's location.
>
> My table layout is as follows:
>
> Table Name: status
> statusId (int - identity)
> orderId (int)
> stationDate (datetime)
> station (char 20)
>
> An order may go through the following stations:
>
> Order #12345   9/1/06 2:45 PM   Sales In
> Order #12345   9/1/06 2:52 PM   Sales Out
> Order #12345   9/1/06 3:14 PM   Accounting In
> Order #12345   9/1/06 4:45 PM   Accounting Out
> Order #12345   9/2/06 3:33 AM   Warehouse In
> Order #12345   9/2/06 5:12 AM   Warehouse Out
> Order #12345   9/2/06 7:22 AM   Accounting In
> Order #12345   9/2/06 7:46 PM   Accounting Out
> Order #12345   9/2/06 9:44 AM   Shipping In
> Order #12345   9/2/06 3:43 PM   Shipping Out
>
> I want to be able to query all orders that are in a specific station
> and that have not moved on to another station. For example, select all
> orders where station = 'Accounting In' and do not have any entries
> after the order went into Accounting In. The purpose of this is to
> give each person a "to do" list of orders that are currently at their
> station.
>
> This seems like such a simple thing but I just can't figure it out. Any
> help would greatly be appreciated.
>

7 Sep 2006 1:41 AM

--CELKO--

>> I want to be able to query all orders that are in a specific station and that have not moved on to another station. <<

History tables are usually of the form:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date),
PRIMARY KEY (upc CHAR(13) NOT NULL,
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You then use a BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;

You might also want to look at my article on Transition constraints at
www.dbazine.com for this problem.