super complex queri

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

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

  • 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

  • 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

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

  • What I gave you will show what you wanted

     

    tal


    Kindest Regards,

    Tal Mcmahon

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply