| Author |
 Topic  |
|
|
ktkn
Starting Member
13 Posts |
 Posted - 11/29/2012 : 13:02:09
|
I have query to find out how many records have not been delivery but
select *
from mytbl
where NotYetDelivery = 0
and NotYetDelivery <OrderQty
Basically all i need just find out how may records have not been delivered.
order #1 :- not delivery
order #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
|
Edited by - ktkn on 11/29/2012 16:28:42
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/29/2012 : 13:13:51
|
| -- deleted reply because after op modified the original post my reply looks silly and irrelevant -- |
Edited by - sunitabeck on 11/29/2012 15:37:02 |
 |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/29/2012 : 13:51:21
|
You mean this :
[code]select *
from mytbl
where Isnull([Order Qty],0) > Isnull(Delivery,0) |
Edited by - sodeep on 11/29/2012 13:55:13 |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/29/2012 : 15:41:50
|
| Where did 6 Order go in last row? Your question is not clear. |
|
|
|
ktkn
Starting Member
13 Posts |
Posted - 11/29/2012 : 16:29:19
|
| i only have 4 orders not 6. Can you pleae take alook now to see if it makes sense? |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/29/2012 : 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 - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/30/2012 : 09:16:04
|
quote:
Originally posted by ktkn
I have query to find out how many records have not been delivery but
select *
from mytbl
where NotYetDelivery = 0
and NotYetDelivery <OrderQty
Basically all i need just find out how may records have not been delivered.
order #1 :- not delivery
order #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 tbl
WHERE
OrderQty > Delivery
AND NotYetDelivery > 0 |
|
|
|
ktkn
Starting Member
13 Posts |
Posted - 11/30/2012 : 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 NotYetDelivery
1 10 0 10
2 10 10 0
3 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,5
thanks
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/30/2012 : 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 - 11/30/2012 : 11:29:55
|
| unfortunately nope |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/30/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/30/2012 : 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 |
|
|
| |
Topic |
|
query help !!
