Author |
Topic |
ktkn
Starting Member
13 Posts |
Posted - 2012-11-29 : 13:02:09
|
I have query to find out how many records have not been delivery but select *from mytblwhere NotYetDelivery = 0and NotYetDelivery <OrderQtyBasically all i need just find out how may records have not been delivered. order #1 :- not deliveryorder #3 - only delivered in half (5)order #4 , delivery four and decided not to purchase any more then it show 0 from NotYetDeliver.With condition above i got some extra for record 4. How can i not inlcude # 4 in since it shows 0 on NotYetDelivery ?Thanks=============Order # OrderQty Delivery NotYetDelivery 1 10 0 10 2 10 10 0 3 10 5 5 4 10 4 0 |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-29 : 13:13:51
|
-- deleted reply because after op modified the original post my reply looks silly and irrelevant -- |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-29 : 13:51:21
|
You mean this :[code]select *from mytblwhere Isnull([Order Qty],0) > Isnull(Delivery,0) |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-29 : 15:41:50
|
Where did 6 Order go in last row? Your question is not clear. |
|
|
ktkn
Starting Member
13 Posts |
Posted - 2012-11-29 : 16:29:19
|
i only have 4 orders not 6. Can you pleae take alook now to see if it makes sense? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-29 : 19:45:13
|
I mean you have 10 Order and 4 of them are delivered so what happen to 6 ? Can you layout with example and Expected output you are looking for |
|
|
ktkn
Starting Member
13 Posts |
Posted - 2012-11-30 : 09:03:51
|
they cancel other 6 order but unfortunately we don’t have flag to set records to cancel. I want my query does not included this record. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-30 : 09:16:04
|
quote: Originally posted by ktkn I have query to find out how many records have not been delivery but select *from mytblwhere NotYetDelivery = 0and NotYetDelivery <OrderQtyBasically all i need just find out how may records have not been delivered. order #1 :- not deliveryorder #3 - only delivered in half (5)order #4 , delivery four and decided not to purchase any more then it show 0 from NotYetDeliver.With condition above i got some extra for record 4. How can i not inlcude # 4 in since it shows 0 on NotYetDelivery ?Thanks=============Order # OrderQty Delivery NotYetDelivery 1 10 0 10 2 10 10 0 3 10 5 5 4 10 4 0
SELECT * FROM tblWHERE OrderQty > Delivery AND NotYetDelivery > 0 |
|
|
ktkn
Starting Member
13 Posts |
Posted - 2012-11-30 : 10:46:57
|
i alreay have that but still not pulled record for 5 if customer just placing order (i didn't not inlude because so many there)Order# OrderQty Delivery NotYetDelivery1 10 0 102 10 10 03 10 5 5 4 10 4 0(customer only purchased 4 and cancel 6)5 10 0 0 (just placing the order)want results: #1,3,5thanks |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-30 : 11:05:34
|
Is there anything in your table or data that will enable you to distinguish between a customer who is placing an order (10,0,0) and another customer who placed an order for 10 and cancelled the order before any was delivered? |
|
|
ktkn
Starting Member
13 Posts |
Posted - 2012-11-30 : 11:29:55
|
unfortunately nope |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-30 : 11:37:58
|
If you have the ability to add a status column to your table and populate that you will be able to definitively distinguish. But if you cannot change the database schema and change the scripts/code that feed it, I can't see any way to accomplish what you are trying to do |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-30 : 12:09:04
|
As Sunitaback said, we need to get broad picture of your table. We are not mind reader and also you need to have some information to determine which is cancelled or just placed order |
|
|
|