SQL Server Performance

World week

Discussion in 'Getting Started' started by shan1430, Apr 13, 2008.

  1. shan1430 New Member

    Hi, I have a table ABC. It has 2 columns, A and B. At column A i have 3 rows, Week,Goal,Used. Let say for column B at row Week is WW15 now, after 7 days i want the row to be updated to WW16. How do i do that? Advice please.
  2. martins New Member

    How about scheduling a job to update the column? You have to be more clear as to what you are struggling with...is it with scheduling something to update the column or with the actual update of the column?
  3. shan1430 New Member

    i can schedule the update but i just dont know how to make the WW15 to WW16 and so on each week. I think i must use the variable i but i dont know how to do that. Please help. thanks..
  4. martins New Member

    Well, if the format will stay as you have mentioned above...why not do something like:
    select left(<column>,2) + convert(varchar,(convert(int,right(<column>,2)) + 1))
    from <table>
  5. shan1430 New Member

    hi, i think your answer is almost close. Im receiving result as ww1 no matter how many times i execute. It should keep increasing right? How to fix this?thanks
  6. martins New Member

    Please post a sample of the data and the code that you used...
  7. shan1430 New Member

    Hi I used the following and it works thanks..
    UPDATE
    ABCSET
    B = substring(B, 1, 2) + cast(cast(substring(B, 3, 10) as int) + 1 as varchar(8))WHERE
    A = 'Week'
  8. shan1430 New Member

    Hi, can anyone suggest me how to make the incresing order to be reset after WW52 and start again from WW01?
  9. martins New Member

    You can use a CASE statement. Read up on the syntax in Books Online.
  10. ranjitjain New Member

    Hi,
    If your weeknumber is based on week of the year then consider this
    select 'WW'+convert(varchar(10),datepart(wk,getdate()))
    UPDATE ABC SET B='WW'+convert(varchar(10),datepart(wk,getdate())) WHERE A='week'
    else as suggested use CASE statement where check when the column value is 52 then reset it 0 else as is and then add 1.
  11. shan1430 New Member

    Hi, I need one week before the current week. How to get that? Example, this is week17 (WW17) so i need to have WW16. Please advice. Thanks

Share This Page