Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query help !!

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 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

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 --
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-29 : 13:51:21
You mean this :

[code]select *
from mytbl
where Isnull([Order Qty],0) > Isnull(Delivery,0)
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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 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
Go to Top of Page

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 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

Go to Top of Page

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?
Go to Top of Page

ktkn
Starting Member

13 Posts

Posted - 2012-11-30 : 11:29:55
unfortunately nope
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -