Help with a queries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi group,

I need a help to build a few queries.
I have a table with the following columns:

orderID as int ; ID of a market order
price as money,' obviously the price of a traded item
volRemaining as int ' amount of the item availabe on stock, or in case
of buy order, amount of item the a custormer is looking to buy
typeID as int ' ID of the item, for lookup in the Items table
bid as bit ' 0 means Sell order (i.e. a vendor is selling items), 1
means that it is a customer order form a customer, looking to buy a
certain amount (volRemaining) of items
cityID as int ' ID of the city, where the order is put
countryID as int 'ID of the county where the order is up.
vendorID as int 'ID of teh customer or the vendor

For each traded item (itemID) there are multiple sell and buy orders
(bid= 0 or 1).

What I need are a few queries that will retrun the following
information:
Q1: Calculate the expectable profit from the lowest sell order(bid=0)
and higest buy(bid=1) order, showing the city and country for those
two orders for each Item available in the orders table. The results
can be from the same City or Country. Some items may have only buy or
only sell orderds, or no orders at all

something like:
item,sellCountry,sellCity,buyCountry,buyCity,profit
Rice, Italy, Rome, Paris, France, 7%

Q2: Show the lowest sell prices for a given item in all countries,
where the Country
like as follows
item,lowestSellPriceCountry1,lowestSellPriceCountry2,lowestSellPriceCounty3
and so on
Rice, 25.3, 22.4, 22.8

Q3: Same as Q2, but for the highest buy price

Q4: Similar as Q1, to calculate the expectable profit from the
available orders, for each of the items, but taking into account the
amount available for Sell/Buy (volRemaining column)
for example lets say we have the following orders.
Order 1:
orderID, price, volRemaining, typeID, bid, cityID, countryID, vendorID
1, 20, 10,10(Rice) ,0, 5, 2, 10 - a sell order for 10 tons of Rice at
$20, city, county and vendor are irrelevent
2, 21, 50,10(Rice) ,0 ,4, 3, 17 - a sell order for 50 tons of Rice at
$21
3, 24, 2, 10(Rice), 1, 5, 7 ,12 - a buy order for 1 ton Rice at $24
4, 22, 30, 10(Rice), 1, 7, 4, 18 - a buy order for 30 tons of Rice at
$22

What the query should calculate which is the biggest profit, taking
into acount the price, the amount available for sale and the amount a
customer is willing to buy.

Please help me with this problem, as I am simple sysadmin, not a
developer. Our dev went missing, and I have to do this kind of things
lately.

Thank you in advance.
Any help will be greatly appreciated.

Regards,
John





.

Relevant Pages

  • Re: looping
    ... If he does answer it will be some off topic rant aimed at distracting us away from the facts in the case in point also - it won't come anywhere near addressing the point I'm making that for running totals cursors rock and set based utterly sucks. ... (select sum(amount) ... from Trans t2 ... salesperson_id int not null, ...
    (microsoft.public.sqlserver.programming)
  • Re: looping
    ... id), id, salesperson_id, tran_date, clear_date, amount, ... from Trans t2 ... salesperson_id int not null, ... I've not done cursors in MS ...
    (microsoft.public.sqlserver.programming)
  • Re: looping
    ... id), id, salesperson_id, tran_date, clear_date, amount, ... from Trans t2 ... salesperson_id int not null, ... I've not done cursors in MS ...
    (microsoft.public.sqlserver.programming)
  • Re: looping
    ... I will focus specifically on this comparison cursor v set for rolling totals. ... amount, transaction_types_id, account_id) ... from Trans t2 ... salesperson_id int not null, ...
    (microsoft.public.sqlserver.programming)
  • Re: looping
    ... SQLBits, the recording and presentation is here: But, I will focus specifically on this comparison cursor v set for rolling totals. ... amount, transaction_types_id, account_id) ... from Trans t2 ... salesperson_id int not null, ...
    (microsoft.public.sqlserver.programming)