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

• NineIron

  SSChampion

  Points: 12536

  More actions

  September 15, 2020 at 4:53 pm

  #3788602

  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: 246960

  More actions

  September 15, 2020 at 5:24 pm

  #3788618

  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 4 years, 2 months ago by  Phil Parkin.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  September 17, 2020 at 3:25 pm

  #3789187

  Did my response satisfy your requirement?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin