TO FIND TOTAL ORDERS SHIPPED AND NOT SHIPPED AND TOTAL OF AMOUNT WHICH WERE SHIPPED AND WHICH WERE N

[Ashley]

THIS IS THE SAMPLE DATE
I want to see supplierID, sum of amount shipped and sum of amount not
shipped, and group them by suppier, total no of orders accpted by
supplier,
total no of orders declined

ORDERS_SHIPPED

OrderID int, [Shipped Date] datetime

1 '1/2/05'
2 '2/3/05'
6 5/6/06'

Orders
supplierID int, OrderID int, status char,
amount int
111 1 'accepted'
1000
111 2 'accepted'
2500
111 8 'declined'
200
222 3 'accepted'
2000
222 4 'accepted'
3000
333 5 'declined'
2000
333 6 'accepted'
500


SUPPLIER


supplierid supplier
111 ABC
222 ROCKWAY
333 CASTLE


THIS IS THE EXPECTED RESULT



supplier OrdersShipped OrdersPending OrdersAccepted
OrdersDeclined AmtShipped
ABC 2 1 2
1 3500
ROCKWAY 0 2 1
0 0
CASTLE 1 0 1
1 500


NEED TO ADD ANOTHER COLUMNs WITH

AMTDECLINED AMTPENDINGSHIPPING
200 0
00 5000
2000 0

PENDING IS WHICH IS ACCEPTED BUT NOT SHIPPED

I am trying something like this. I AM NOT AN ACCESS PROGRAMMER AND
WOULD
APPRECIATE HELP WITH THIS. I AM DOING SOMETHING WRONG HERE.



SELECT s.supplier,


sum (IIf((o.orderstatusStatus ="ACCEPTED")
AND ( EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS where
o.orderid = os.orderid group by os.orderid)
) ,1,0)) AS [ORDERSSHIPPED]

sum (IIf((o.Status ="ACCEPTED")
AND ( NOT EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS
where o.orderid = os.orderid group by os.orderid)
) ,1,0)) AS [ORDERSPENDING],

sum (IIf((o.Status ="ACCEPTED") ) ,1,0)) AS [ORDERSACCEPTED],

sum (IIf((o.Status ="DECLINED") ) ,1,0)) AS [ORDERSDECLINED],

sum (IIf((o.orderstatusStatus ="ACCEPTED")
AND ( EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS where
o.orderid = os.orderid group by os.orderid)
) ,o.amount ,0)) AS [AMOUNTSHIPPED]


sum (IIf((o.Status ="DECLINED") ) ,o.orderamount,0)) AS [AMTDECLINED],

sum (IIf((o.Status ="ACCEPTED")
AND ( NOT EXISTS (select os.ordersshipped from ORDERS_SHIPPED OS
where o.orderid = os.orderid group by os.orderid)
) ,o.amount,0)) AS [AMTPENDINGSHIPPING],



FROM

supplier as s,
orders o,
ORDERS_SHIPPED os



WHERE

o.orderid = os.orderid
and o.supplierid = s.supplierid

GROUP BY

o.supplierid

 

[Ashley]

ORDERS_SHIPPED


OrderID int, [Shipped Date] datetime


1 '1/2/05'
2 '2/3/05'
6 5/6/06'


ORDERS (1st 2 columns)

supplierID int OrderID int
111 1
111 2
111 8
222 3
222 4
333 5
333 6


ORDERS (3rd and 4th cloumn)

status char, amount int
'accepted' 1000
'accepted' 2500
'declined' 200
'accepted' 2000
'accepted' 3000
'declined' 2000
'accepted' 500



SUPPLIER


supplierid supplier
111 ABC
222 ROCKWAY
333 CASTLE


THIS IS THE EXPECTED RESULT


supplier OrdersShipped OrdersPending OrdersAccepted
ABC 2 1 2
ROCKWAY 0 2 1
CASTLE 1 0 1


NEED TO ADD ANOTHER COLUMNs WITH

OrdersDeclined AmtShipped
1 3500
0 0
1 500


AND

AMTDECLINED AMTPENDINGSHIPPING
200 0
00 5000
2000 0