Query Problem

4 Sep 2006 2:50 PM

trullock

Hi,

I have an orders table and an order_items table. Simply, they look like
this:

Orders:
ID | Status
-------
0 | New
1 | InProgress
2 | InProgress

Order_Items:
ID | Ord_ID | Supplier | Status
-------------
0 |   0    |   Fred   | New
1 |   1    |   Fred   | New
2 |   1    |   Fred   | Complete
3 |   2    |   Fred   | New
4 |   2    |   Joe    | Complete

When Joe wants to view his 'Complete' Orders, he should see order 2,
because all his items for order 2 are complete (even though its
orderstatus is inprogress)

When Fred wants to view his new orders, he should see order 0 and 2
(because all his items for 2 are new), and order 1 should be seen as
inprogress.

How can i write a query which given a supplier and status (either new,
inprogress or complete) will return all the relevant orders?

Thanks

Andrew

4 Sep 2006 3:42 PM

Sha Anand

This should work for you...
SELECT
     h.id,h.Status
FROM
     sOrders h
INNER JOIN sOrder_items d ON
      h.Id = d.Ord_Id
WHERE
     d.Supplier = <Name>
GROUP BY
    h.Id,h.Status
HAVING
    SUM(CASE WHEN d.Status = <Status> THEN 0 ELSE 1 END) = 0
ORDER BY
    h.Id

- Sha Anand

Show quote

"trull***@hotmail.com" wrote:

> Hi,
>
> I have an orders table and an order_items table. Simply, they look like
> this:
>
> Orders:
> ID | Status
> -------
> 0 | New
> 1 | InProgress
> 2 | InProgress
>
> Order_Items:
> ID | Ord_ID | Supplier | Status
> -------------
> 0 |   0    |   Fred   | New
> 1 |   1    |   Fred   | New
> 2 |   1    |   Fred   | Complete
> 3 |   2    |   Fred   | New
> 4 |   2    |   Joe    | Complete
>
> When Joe wants to view his 'Complete' Orders, he should see order 2,
> because all his items for order 2 are complete (even though its
> orderstatus is inprogress)
>
> When Fred wants to view his new orders, he should see order 0 and 2
> (because all his items for 2 are new), and order 1 should be seen as
> inprogress.
>
>
> How can i write a query which given a supplier and status (either new,
> inprogress or complete) will return all the relevant orders?
>
> Thanks
>
> Andrew
>
>

4 Sep 2006 4:11 PM

trullock

Sha Anand wrote:
Show quote

> "trull***@hotmail.com" wrote:
>
> > Hi,
> >
> > I have an orders table and an order_items table. Simply, they look like
> > this:
> >
> > Orders:
> > ID | Status
> > -------
> > 0 | New
> > 1 | InProgress
> > 2 | InProgress
> >
> > Order_Items:
> > ID | Ord_ID | Supplier | Status
> > -------------
> > 0 |   0    |   Fred   | New
> > 1 |   1    |   Fred   | New
> > 2 |   1    |   Fred   | Complete
> > 3 |   2    |   Fred   | New
> > 4 |   2    |   Joe    | Complete
> >
> > When Joe wants to view his 'Complete' Orders, he should see order 2,
> > because all his items for order 2 are complete (even though its
> > orderstatus is inprogress)
> >
> > When Fred wants to view his new orders, he should see order 0 and 2
> > (because all his items for 2 are new), and order 1 should be seen as
> > inprogress.
> >
> >
> > How can i write a query which given a supplier and status (either new,
> > inprogress or complete) will return all the relevant orders?
> >
> > Thanks
> >
> > Andrew
> >
> >
> This should work for you...
> SELECT
>      h.id,h.Status
> FROM
>      sOrders h
> INNER JOIN sOrder_items d ON
>       h.Id = d.Ord_Id
> WHERE
>      d.Supplier = <Name>
> GROUP BY
>     h.Id,h.Status
> HAVING
>     SUM(CASE WHEN d.Status = <Status> THEN 0 ELSE 1 END) = 0
> ORDER BY
>     h.Id
>
> - Sha Anand
>

Hi,

Thanks yeah that almost works for me :D

It doesnt work under one condition however :(

Orders are "in progress" if they have a mix of "New" and "completed"
orders (regardless of supplier)

An orderitem is never "in progress"

Let me better explain the scenario:

if the orderstatus is NEW, all orderitems' itemstatus' are NEW
if the orderstatus is COMPLETE, all orderitems' itemstatus' are
COMPLETE
if the orderstatus is INPROG, the orderitems are a mix of NEW and
COMPLETE.

However, when only considering a specific supplier, if all the items in
an INPROGRESS order are NEW or COMPLETE, then the order should be
considered NEW or COMPLETE.

when i do:

SUM(CASE WHEN d.Status = 'INPROGRESS' THEN 0 ELSE 1 END) = 0

It obviously doesnt work :(

Do you know how to make this work?

Thanks a lot :)

Andrew

4 Sep 2006 4:46 PM

Tom Cooper

So, as I understand it, you need to look at Orders Status to get the status
of the order if it is New or Complete, but if it is InProgress and you want
the Status for a particular supplier, you must check the Order_Items Status
for that Order for that Supplier and there are only two possible status
values or Order_Items Status - New or Complete. Then the following should
wrok (just add a Where clause if you want to restrict the result to a
particular Order ID and/or a particular Supplier.

Select o.ID, i.Supplier,
Case When o.Status = 'New' Then 'New'
When o.Status = 'Complete' Then 'Complete'
When Min(i.Status) <> Max(i.Status) Then 'InProgress'
Else Min(i.Status)
End As Status
From Order_Items i
Inner Join Orders o On i.Ord_ID = o.ID
Group By o.ID, i.Supplier, o.Status
Order By o.ID, i.Supplier;

Tom

<trull***@hotmail.com> wrote in message
Show quote

news:1157386282.888448.86750@b28g2000cwb.googlegroups.com...
> Sha Anand wrote:
>> "trull***@hotmail.com" wrote:
>>
>> > Hi,
>> >
>> > I have an orders table and an order_items table. Simply, they look like
>> > this:
>> >
>> > Orders:
>> > ID | Status
>> > -------
>> > 0 | New
>> > 1 | InProgress
>> > 2 | InProgress
>> >
>> > Order_Items:
>> > ID | Ord_ID | Supplier | Status
>> > -------------
>> > 0 |   0    |   Fred   | New
>> > 1 |   1    |   Fred   | New
>> > 2 |   1    |   Fred   | Complete
>> > 3 |   2    |   Fred   | New
>> > 4 |   2    |   Joe    | Complete
>> >
>> > When Joe wants to view his 'Complete' Orders, he should see order 2,
>> > because all his items for order 2 are complete (even though its
>> > orderstatus is inprogress)
>> >
>> > When Fred wants to view his new orders, he should see order 0 and 2
>> > (because all his items for 2 are new), and order 1 should be seen as
>> > inprogress.
>> >
>> >
>> > How can i write a query which given a supplier and status (either new,
>> > inprogress or complete) will return all the relevant orders?
>> >
>> > Thanks
>> >
>> > Andrew
>> >
>> >
>> This should work for you...
>> SELECT
>>      h.id,h.Status
>> FROM
>>      sOrders h
>> INNER JOIN sOrder_items d ON
>>       h.Id = d.Ord_Id
>> WHERE
>>      d.Supplier = <Name>
>> GROUP BY
>> h.Id,h.Status
>> HAVING
>> SUM(CASE WHEN d.Status = <Status> THEN 0 ELSE 1 END) = 0
>> ORDER BY
>> h.Id
>>
>> - Sha Anand
>>
>
>
> Hi,
>
> Thanks yeah that almost works for me :D
>
> It doesnt work under one condition however :(
>
> Orders are "in progress" if they have a mix of "New" and "completed"
> orders (regardless of supplier)
>
> An orderitem is never "in progress"
>
>
> Let me better explain the scenario:
>
> if the orderstatus is NEW, all orderitems' itemstatus' are NEW
> if the orderstatus is COMPLETE, all orderitems' itemstatus' are
> COMPLETE
> if the orderstatus is INPROG, the orderitems are a mix of NEW and
> COMPLETE.
>
> However, when only considering a specific supplier, if all the items in
> an INPROGRESS order are NEW or COMPLETE, then the order should be
> considered NEW or COMPLETE.
>
>
> when i do:
>
> SUM(CASE WHEN d.Status = 'INPROGRESS' THEN 0 ELSE 1 END) = 0
>
> It obviously doesnt work :(
>
> Do you know how to make this work?
>
> Thanks a lot :)
>
> Andrew
>

5 Sep 2006 10:40 PM

Hugo Kornelis

On 4 Sep 2006 09:11:22 -0700, trull***@hotmail.com wrote:

(snip)

>Thanks yeah that almost works for me :D
>
>It doesnt work under one condition however :(
>
>Orders are "in progress" if they have a mix of "New" and "completed"
>orders (regardless of supplier)
>
>An orderitem is never "in progress"

Hi Andrew,

A small modification to Sha Anand's suggestion should take care of that:

SELECT     h.Id,
           CASE WHEN MIN(h.Status) = MAX(h.Status)
                THEN MIN(h.Status)
                ELSE 'In Progress'
           END AS Status
FROM       sOrders      AS h
INNER JOIN sOrder_items AS d
      ON   h.Id = d.Ord_Id
WHERE      d.Supplier = <Name>
GROUP BY   h.Id
ORDER BY   h.Id;

(Untested - see www.aspfaq.com/5006 if yoou prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

5 Sep 2006 11:15 PM

--CELKO--

Please post DDL, so that people do not have to guess what the keys,

constraints, Declarative Referential Integrity, data types, etc. in

your schema are.

Look at that Orders table -- vague "id" that might or might not be a
key (you are not using IDENTITY for order numbers, are you!!??); vague
"status" attribute (what kind of status??). Cann't this be done in a
view off of the OrderItems table instead of mimicing a file? Look up
how to name a data element.

Here is my guess and corrections to your "pseudo-code" non-table

CREATE TABLE Order_Items
(order_nbr INTEGER NOT NULL
   CHECK (<< validation rule here>>),
item_nbr INTEGER NOT NULL
   CHECK (item_nbr > 0),
supplier_name CHAR(10) NOT NULL
   REFERENCES Suppliers (supplier_name)
   ON UPDATE CASCADE,
item_status CHAR(1) DEFAULT 'N' NOT NULL
   CHECK (item_status IN ('N', 'C'), -- new, completed
PRIMARY KEY (order_nbr, item_nbr));

Notice the use of a relational key, instead of mimicing a tape file
record number? The use of IDENTITY for items in a bill of materials or
order problem screw up things. Use an item number within the order
number.

INSERT INTO Order_Items VALUES (0, 1, 'Fred', 'N');
INSERT INTO Order_Items VALUES (1, 1, 'Fred', 'N');
INSERT INTO Order_Items VALUES (1, 2, 'Fred', 'C');
INSERT INTO Order_Items VALUES (2, 1, 'Fred', 'N');
INSERT INTO Order_Items VALUES (2, 2, 'Joe', 'C');

That is, in Order #2, Fred supplied item #1 and Joe supplied item #2.
Lot easier to track things with a proper design. Now throw out your
redundant table:

CREATE VIEW OrderStatus (order_nbr, supplier, order_status)
AS
SELECT order_nbr, supplier,
       CASE WHEN MIN(item_status) = 'New'
            THEN 'New'
            WHEN MAX(item_status) = 'Complete'
            THEN 'Complete'
            ELSE 'In Progress' END;
FROM Order_Items
GROUP BY order_nbr, supplier;

The VIEW is always current and you do not have to keep writing to disk
to mimic a physical file.

6 Sep 2006 12:07 PM

Hilarion

There's a minor mistake in the view definition:

> CREATE VIEW OrderStatus (order_nbr, supplier, order_status)
> AS
> SELECT order_nbr, supplier,
> CASE WHEN MIN(item_status) = 'New'

The line above should be:

CASE WHEN MIN(item_status) = 'N'

('N' instead of 'New' - Order_Items.item_status
is defined to use 'N' and 'C'.)

> THEN 'New'
> WHEN MAX(item_status) = 'Complete'

As above:

WHEN MAX(item_status) = 'C'

> THEN 'Complete'
> ELSE 'In Progress' END;
> FROM Order_Items
> GROUP BY order_nbr, supplier;

Kamil 'Hilarion' Nowicki