March 3, 2005 at 9:33 pm
iam working on IPO, its about share allotement,
in this every applicant can make 3 bids
and shares are alloted according to the fair judegements . by using reports
1 is price demand analysis
suppose an applicant makes 3 bids
no.of shares price
100 500
400 700
300 650
now in this i need a query
which will have one column showing all the prices
like it will start from 500 and ends at 800
now it will look like this
price no. of shares
500 1800 (it will 500+700+650)
501..
670. 1350 (600+750)
the no. of shares which prices, which are higher should also get accumulate in the lowest prices
like in case of 500.
it will be (500+700+650) becoz they are willing to pay higher prices. and will have no problem in paying the less price
so they should be included in the lower price tag.
and there should also be an auto increment column.
which will increment from 500 till 800
if anybody wants to ask questions pls feel free to ask, any way this thing can be done via query.
i know it can be done by looping or cursors, but there are more than 700,000 records. looping will be extremly slow.
any better idea??
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
March 4, 2005 at 2:51 am
if u dont understand my question pls ask, but this is the query iam trying to write and needs help
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
March 4, 2005 at 12:28 pm
So, If I understand you are looking for a way to find out how many would sell at or below the price rang from 500-800$ so for instance:
Price number to buy
500 100
501 10
502 20
you want a table like this
500 100
501 110
502 130
I think thats what you mean, my only question next is are the offers seperated by applicant or are they all offers for 500$
tal
March 4, 2005 at 1:01 pm
hi again,
after I played with it some I am making the assumption that it is not applicant based. Here is a solution.
First Whenever I am making a table that iterates through some numbers to keep it set based i build a table with a set of numbers that i query against. in this case a table of integers from 500 to 800 here is that table
CREATE TABLE [number] (
[number] [int] IDENTITY (500, 1) NOT NULL
) ON [PRIMARY]
GO
Declare @loop int
set @loop=1
While @Loop < 301
Begin
insert into numbers
default values
set @loop = @loop +1
end
Now for the query i built a table to test against called 'test'
it follows
CREATE TABLE [test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[ApplicantID] [int] NULL ,
[OfferID] [int] NULL ,
[price] [int] NULL ,
[buyNumber] [int] NULL
) ON [PRIMARY]
GO
now the query,
there is a subquery that gets the sum of buynumber for each price from 500-800
then I cross the subquery with itself and limit the results to only results less than the current price in the first table
then I sum the values
Select t1num,
sum(t2buy)
from (
Select number as t1num ,
sum(buynumber) as t1buy
from Number
left outer join test on number=price
Group BY Number
) as t1,
(
Select number as t2num,
sum(buynumber) as t2buy
from Number
left outer join test on number=price
Group BY Number
) as t2
Where t2.t2num <= t1.t1num
Group by t1num
Order by t1num
[/code]
this was a neat little query
hth
tal mcmahon
March 5, 2005 at 1:06 am
i will try whatever u said below, but
500 100
501 10
502 20
in the case u mentioned it should reflet
500 130(100+10+20)
501 30(10+20)
502 20(only 20)
this is what should appear
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
March 5, 2005 at 5:13 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply