hi i need cursor program

  • the main task is.

    scenario:

    assume if a production company take material for a weekly basis..

    this week they have a stock 800 ton rawmaterial.

    for daily basis they going to use only 100 ton for production,

    when stock get inside the inventory is automatically want to be alloted for the daily basis.

    eg :mon =100 ton,tue=100ton,wed=100ton,thur=100ton,fri=100 ton,sat=100 ton,sunday=company off,monday =100ton ,tue=100ton,wed=no stock in inventory..

    output must be like this..can any one do this in sql by using cursor..

    pls help me..

  • Why do you want to use a cursor?

    Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • yes I agree with Mr.Gail.. You may not need cursor for this..

    As per my understand.. simple triggers will work for you

    🙂

  • Ram:) (7/2/2010)


    yes I agree with Mr.Gail.. You may not need cursor for this..

    As per my understand.. simple triggers will work for you

    Whoa! Hold the phone! Triggers? For this? You'll have to show me that one, Ram.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • rajendra prasad-377679 (7/2/2010)


    the main task is.

    scenario:

    assume if a production company take material for a weekly basis..

    this week they have a stock 800 ton rawmaterial.

    for daily basis they going to use only 100 ton for production,

    when stock get inside the inventory is automatically want to be alloted for the daily basis.

    eg :mon =100 ton,tue=100ton,wed=100ton,thur=100ton,fri=100 ton,sat=100 ton,sunday=company off,monday =100ton ,tue=100ton,wed=no stock in inventory..

    output must be like this..can any one do this in sql by using cursor..

    pls help me..

    Take a look at the following code. If you don't know what a Tally table is or how it works to replace cursors and loops, then see the following article.

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    --===== Declare a couple of obviously name variables

    DECLARE @StartDate DATETIME,

    @Quantity INT,

    @DailyAmount INT

    --===== Present the variables to the known values

    SELECT @StartDate = '20100705',

    @Quantity = 800,

    @DailyAmount = 100

    --===== Use a Tally table to produce the data in a set-based

    -- fashion without a CURSOR.

    SELECT @StartDate + (t.N-1) AS Date,

    @DailyAmount AS Quantity

    FROM dbo.Tally t

    WHERE t.N BETWEEN 1 AND @Quantity/@DailyAmount

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ram:) (7/2/2010)


    yes I agree with Mr.Gail.. You may not need cursor for this..

    As per my understand.. simple triggers will work for you

    Jeff already responded to the trigger comment, so I'll leave that alone.

    However, Gail is one of the brightest women you will find on SSC or anywhere else, especially when it comes to things about SQL Server.

  • Please post table definitions, sample data and desired output

    Dear ,

    THANKS FOR UR SUPPORT.

    i want to do that by using cursor program not in trigger..

    U CAN CREATE OWN TABLE AS U LIKE

    table definitions.

    for days USE datefunctions or varchar

    tons must be get from the user

    eg:if 800 tons means

    The output must be like this

    sun mon tue wed thur fri sat-------attributes

    100100100100100100 ---values that passed automatically

    OFF100100100 ----inventory values that passed automatically

    if they try to retrieve information for thursday

    /*select inventory values from tablename

    where date='thur'

    */

    this will raise error like this.

    FOR THURSDAY = NO STOCK IN INVENTORY or stock does not exist./...

    Kindly help me as soon as possible..

    Thank u..

  • hi dude

    hey can u send me the sample table with queries by using trigger..

    Regards

    prasad

  • rajendra prasad-377679 (7/2/2010)


    Please post table definitions, sample data and desired output

    Dear ,

    THANKS FOR UR SUPPORT.

    i want to do that by using cursor program not in trigger..

    U CAN CREATE OWN TABLE AS U LIKE

    table definitions.

    for days USE datefunctions or varchar

    tons must be get from the user

    eg:if 800 tons means

    The output must be like this

    sun mon tue wed thur fri sat-------attributes

    100100100100100100 ---values that passed automatically

    OFF100100100 ----inventory values that passed automatically

    if they try to retrieve information for thursday

    /*select inventory values from tablename

    where date='thur'

    */

    this will raise error like this.

    FOR THURSDAY = NO STOCK IN INVENTORY or stock does not exist./...

    Kindly help me as soon as possible..

    Thank u..

    I'm sorry, but did you bother to read the article that Gail linked to in her port (it is the same article I reference below in my signature block regarding asking for help)? Without that information there really isn't much we can do to help you.

    Also, please explain why it MUST be a cursor.

  • rajendra prasad-377679 (7/2/2010)


    THANKS FOR UR SUPPORT.

    i want to do that by using cursor program not in trigger..

    It's highly unlikely this needs a cursor and I don't see what a trigger has to do with anything. Triggers fire when data changes, you haven't mentioned anything about that

    U CAN CREATE OWN TABLE AS U LIKE

    Why should I spend time creating tables to solve your problem? Post the table definitions, some sample data and what the query should output and it will make it a lot easier for the volunteers here to produce a tested and working solution

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • hey linn,

    i didnt know that much of using cursor,

    frankly through niit only i learn sql.

    there didnt teach me how to use cursor and everything..

    now i am working in one organisation they ask me to write the program for that scenario using cursor..

    that s y i ask help to the experts..like u and gail etc.

  • hey gail,

    i didnt have any sample data and everything its just scenario which my TL gave to me.

    i dont know how to use that..

    he ask me to create own table as u like..

    thats y i mentioned it to u..if any thing wrong means sorry for that..

    i know only simple queries whatever in niit material..

    that y i ask help through the experts..

    can u teach me or give some material to become experts in sql server''

    Regards

    Rajendra prasad

  • You need to provide us with the table definition(s), sample data, expected results, and what codeyou have written so far to solve your problem.

    You are going to find many volunteers here that are going to take the time to design our own tables, sample data, etc to help you solve a problem you can't even properly post yourself. We are volunteers, not paid employees. We help people wherewe can on our own time. If your lead told you to make up your own scenerio, then make it up and show us what you are trying to do.

    Most likely, what ever your lead is asking you to accomplish with a cursor can be done using set based code instead.

  • rajendra prasad-377679 (7/3/2010)


    hey gail,

    i didnt have any sample data and everything its just scenario which my TL gave to me.

    Then I think you need to go back to your TL (Technical Lead?) and ask him for more information. Frankly, from the little you gave, I have no idea where to even start.

    I can't believe that there's no existing table, unless this is a completely new system that you're supposed to be writing, in which case there should be a lot more information available on what is required.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • hey gail,

    thanks a lot..

    regards

    Rajendra prasad

Viewing 15 posts - 1 through 15 (of 17 total)

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