SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query help !!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ktkn
Starting Member

13 Posts

Posted - 11/29/2012 :  13:02:09  Show Profile  Reply with Quote
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

5155 Posts

Posted - 11/29/2012 :  13:13:51  Show Profile  Reply with Quote
-- 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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/29/2012 :  13:51:21  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/29/2012 :  15:41:50  Show Profile  Reply with Quote
Where did 6 Order go in last row? Your question is not clear.
Go to Top of Page

ktkn
Starting Member

13 Posts

Posted - 11/29/2012 :  16:29:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/29/2012 :  19:45:13  Show Profile  Reply with Quote
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 - 11/30/2012 :  09:03:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/30/2012 :  09:16:04  Show Profile  Reply with Quote
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 - 11/30/2012 :  10:46:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/30/2012 :  11:05:34  Show Profile  Reply with Quote
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 - 11/30/2012 :  11:29:55  Show Profile  Reply with Quote
unfortunately nope
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/30/2012 :  11:37:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/30/2012 :  12:09:04  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.61 seconds. Powered By: Snitz Forums 2000