How to change week8, week31, week32, etc to week1, week2, week3, etc.

  • NineIron

    SSChampion

    Points: 12525

    I'm trying to use row_number to "reassign" the week number of my data. So, the lowest week number should be 1, then 2 and so on.

     

    create table #T
    (
    ServiceDate datetime,
    Department varchar(50),
    WeekOfYear int
    )

    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-14 00:00:00.000','VASCULAR SURGERY',33)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-11 00:00:00.000','CARDIOLOGY',33)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-05 00:00:00.000','CARDIOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-11 00:00:00.000','CARDIOLOGY',33)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-02-20 00:00:00.000','CARDIOLOGY',8)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-02-20 00:00:00.000','CARDIOLOGY',8)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-02-20 00:00:00.000','CARDIOLOGY',8)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-03-23 00:00:00.000','VASCULAR SURGERY',13)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-02-20 00:00:00.000','CARDIOLOGY',8)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-02-20 00:00:00.000','CARDIOLOGY',8)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-10 00:00:00.000','NULL',33)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-10 00:00:00.000','CARDIOLOGY',33)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-01 00:00:00.000','RADIOLOGY',31)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-01 00:00:00.000','PEDIATRICS',31)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-01 00:00:00.000','EYE SERVICES',31)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-02 00:00:00.000','RADIOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','DERMATOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','PEDIATRICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','BARIATRICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','OBGYN',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ORTHOPEDICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','THORACIC SURGERY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','DERMATOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ABI OBAT CLINIC',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ORTHOPEDICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','OBGYN',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','PEDI C & Y',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ORTHOPEDICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ONCOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ENDOCRINOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','CARDIOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','DERMATOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ENDOCRINOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ORTHOPEDICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','OPD',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','OPD',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','BARIATRICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','OBGYN',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','EYE SERVICES',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ENDOCRINOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','DERMATOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','PLASTIC SURGERY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','VASCULAR SURGERY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ONCOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ORTHOPEDICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ORTHOPEDICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','OBGYN',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','RHEUMATOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','CARDIOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','CARDIOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','PEDIATRICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','PODIATRY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','PEDIATRICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','RADIOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','PEDIATRICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','OBGYN',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','PAIN MANAGEMENT',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','PEDI C & Y',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','EYE SERVICES',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','GENERAL SURGERY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ONCOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','OBGYN',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ENDOCRINOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','PODIATRY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','EYE SERVICES',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','ORTHOPEDICS',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','EYE SERVICES',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','PULMONARY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','INTERNAL MED',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','OBGYN',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','THORACIC SURGERY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','DERMATOLOGY',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
    insert into #T(ServiceDate, Department, WeekOfYear) values('2020-08-03 00:00:00.000','FAMILY PRACTICE',32)
  • Phil Parkin

    SSC Guru

    Points: 244739

    Try this:

    DROP TABLE IF EXISTS #T;

    CREATE TABLE #T
    (
    ServiceDate DATE
    ,Department VARCHAR(50)
    ,WeekOfYear INT
    );

    INSERT #T
    (
    ServiceDate
    ,Department
    ,WeekOfYear
    )
    VALUES
    ('20200814', 'VASCULAR SURGERY', 33)
    ,('20200811', 'CARDIOLOGY', 33)
    ,('20200805', 'CARDIOLOGY', 32)
    ,('20200811', 'CARDIOLOGY', 33)
    ,('20200220', 'CARDIOLOGY', 8)
    ,('20200220', 'CARDIOLOGY', 8)
    ,('20200220', 'CARDIOLOGY', 8)
    ,('20200323', 'VASCULAR SURGERY', 13)
    ,('20200220', 'CARDIOLOGY', 8)
    ,('20200220', 'CARDIOLOGY', 8)
    ,('20200810', 'NULL', 33)
    ,('20200810', 'CARDIOLOGY', 33)
    ,('20200801', 'RADIOLOGY', 31)
    ,('20200801', 'PEDIATRICS', 31)
    ,('20200801', 'EYE SERVICES', 31)
    ,('20200802', 'RADIOLOGY', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'DERMATOLOGY', 32)
    ,('20200803', 'PEDIATRICS', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'BARIATRICS', 32)
    ,('20200803', 'OBGYN', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'ORTHOPEDICS', 32)
    ,('20200803', 'THORACIC SURGERY', 32)
    ,('20200803', 'DERMATOLOGY', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'ABI OBAT CLINIC', 32)
    ,('20200803', 'ORTHOPEDICS', 32)
    ,('20200803', 'OBGYN', 32)
    ,('20200803', 'PEDI C & Y', 32)
    ,('20200803', 'ORTHOPEDICS', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'ONCOLOGY', 32)
    ,('20200803', 'ENDOCRINOLOGY', 32)
    ,('20200803', 'CARDIOLOGY', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'DERMATOLOGY', 32)
    ,('20200803', 'ENDOCRINOLOGY', 32)
    ,('20200803', 'ORTHOPEDICS', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'OPD', 32)
    ,('20200803', 'OPD', 32)
    ,('20200803', 'BARIATRICS', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'OBGYN', 32)
    ,('20200803', 'EYE SERVICES', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'ENDOCRINOLOGY', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'DERMATOLOGY', 32)
    ,('20200803', 'PLASTIC SURGERY', 32)
    ,('20200803', 'VASCULAR SURGERY', 32)
    ,('20200803', 'ONCOLOGY', 32)
    ,('20200803', 'ORTHOPEDICS', 32)
    ,('20200803', 'ORTHOPEDICS', 32)
    ,('20200803', 'OBGYN', 32)
    ,('20200803', 'RHEUMATOLOGY', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'CARDIOLOGY', 32)
    ,('20200803', 'CARDIOLOGY', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'PEDIATRICS', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'PODIATRY', 32)
    ,('20200803', 'PEDIATRICS', 32)
    ,('20200803', 'RADIOLOGY', 32)
    ,('20200803', 'PEDIATRICS', 32)
    ,('20200803', 'OBGYN', 32)
    ,('20200803', 'PAIN MANAGEMENT', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'PEDI C & Y', 32)
    ,('20200803', 'EYE SERVICES', 32)
    ,('20200803', 'GENERAL SURGERY', 32)
    ,('20200803', 'ONCOLOGY', 32)
    ,('20200803', 'OBGYN', 32)
    ,('20200803', 'ENDOCRINOLOGY', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'PODIATRY', 32)
    ,('20200803', 'EYE SERVICES', 32)
    ,('20200803', 'ORTHOPEDICS', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'EYE SERVICES', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'PULMONARY', 32)
    ,('20200803', 'INTERNAL MED', 32)
    ,('20200803', 'OBGYN', 32)
    ,('20200803', 'THORACIC SURGERY', 32)
    ,('20200803', 'DERMATOLOGY', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'FAMILY PRACTICE', 32)
    ,('20200803', 'FAMILY PRACTICE', 32);


    SELECT t.ServiceDate
    ,t.Department
    ,t.WeekOfYear
    ,AdjWeek = DENSE_RANK() OVER (ORDER BY t.WeekOfYear)
    FROM #T t
    ORDER BY t.ServiceDate;

    • This reply was modified 1 week, 2 days ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil Parkin

    SSC Guru

    Points: 244739

    Did my response satisfy your requirement?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 3 posts - 1 through 3 (of 3 total)

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