Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2011
    Posts
    10

    Question Unanswered: Is this possible? Two questions by a newbie.

    Hello, everyone!

    I have got two questions.

    The first question:

    Is it possible in SQL Server 2005 to have it automatically update a value from a previous row into a next row in a same column? I basically want to increment a value from a very first row per 5% with each new row inserted so that I don’t have to do it manually with every new row. Here is an example:

    1. 100 (automatic 5% increment command will send its result automatically to the next row when it is created by a user)
    2. 105
    3. 110,2
    4. 115,7
    5. 121,4

    and so on...

    If there is a syntax for it, I would love to know it.

    The second question:

    Is it possible to have a column with the datetime data type showing only Month-Year format of a date? If yes, how?

    Thank you kindly for all your answers.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    1) Yes, that is possible. I would go for an insert trigger. But it won't be easy to program it correct in case of multiple inserts. There may be better solutions than a trigger.

    2) Yes. Dates are stored in a standard way in SQL Server. In the presentation layer (report) you can apply any format you like, or with this script you can retrieve a string in MM/YYYY format based on a DATETIME column:
    Code:
    SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]
    Check this for more date and time format examples.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2011
    Posts
    10
    So you say there is no other solution than the trigger for the first question?

    Could you please give some list of commands I should study to solve it? Some clue?

    Thank you!

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    So you say there is no other solution than the trigger for the first question?
    I don't know how records are INSERTed into the table. When it is done by a stored procedure, you only have to alter that SP. When it is done by INSERT statements, a trigger is the only solution I can think of, unless you can alter the INSERT statement.

    Is it a working system or are you still designing/developing it? When you're still developing it, use a stored procedure to handle the INSERTs.

    You need a way to tell what record is "the last" one (the one with the highest ID, highest Create_DateTime, ...). Get the column of interest, multiply it with 1.05 and store that value in the new record.

    How will you handle DELETE's?
    - When the first record is deleted, should all values in the records still present, be adjusted by 5% (lowered)?
    - When the last record is deleted, suppose it held a 105, the "last" record will now hold 100. What should be the value of that column for a new insert: 105 or 110.2?

    Try to find out the rules behind that column, perhaps it is possible to calculate the value at runtime.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Nov 2011
    Posts
    10
    Thanks for the tips, but this is going to be a nightmare for me.

    Let's say I have chosen a column named "ID" (filled with the IDENTITY command to automatically count each new row created) as the one which will tell the program what is a previous and a next row. But after I specify this into the code, how can I tell the program that it now have to choose a value from a different column but in the same row to be incremented by those 5%?

    I mean, the column_1 will say the program a number of a row but the value to be incremented is not located in the column_1 but in the column_6.

    //

    Regarding the DELETEs I am aiming for the second scenario you have written. A new insert should have the 105 value again.

    Quote Originally Posted by Wim View Post
    When the last record is deleted, suppose it held a 105, the "last" record will now hold 100. What should be the value of that column for a new insert: 105 or 110.2?
    Thank you very much for your support.

  6. #6
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Quote Originally Posted by Bouben View Post

    Let's say I have chosen a column named "ID" (filled with the IDENTITY command to automatically count each new row created) as the one which will tell the program what is a previous and a next row. But after I specify this into the code, how can I tell the program that it now have to choose a value from a different column but in the same row to be incremented by those 5%?

    I mean, the column_1 will say the program a number of a row but the value to be incremented is not located in the column_1 but in the column_6.

    If your insert is done through a stored procedure it is easy.

    assume column_1 is ur auto generated number which indicates the latest row and column_6 is the one that is used to increment by 5%, the sp logic will be as follows

    --retrieve the last row id
    Set @lastrowid = (Select Top 1 column_1 from table order by column_1 desc)

    --retrieve the column6 val for the prev row
    Set @lastrow_column6val = (Select column_6 from table where column_1 = @lastrowid )

    -- increment by 5%
    Set @newval = @lastrow_column6val * 1.05

    --insert next row
    insert into table (....) Values (... @newval )
    ..
    Cheers....

    baburajv

  7. #7
    Join Date
    Nov 2011
    Posts
    10
    Quote Originally Posted by baburajv View Post
    --insert next row
    insert into table (....) Values (... @newval )
    ..
    Thank you, but I don't understand this part of the code. Could you please write this part completely without blank spaces? I am a newbie a need to orientate.

    Thanks again!

  8. #8
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    assuming that column_1 (auto generated number/ Identity value) and column_2 are the only columns in the table, the insert statement will become

    insert into table( column_6) values (@newval )


    if you have more columns in the table, specify the column names (except column_1 which is autogenerated) before "values" keyword and their respective values in the last ()
    Cheers....

    baburajv

  9. #9
    Join Date
    Nov 2011
    Posts
    10
    You are the man! It works! Thank you so much! First step of my task is done. I will write soon because there will definitely be more problems.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •