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

• NineIron

  SSChampion

  Points: 12529

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

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

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Phil Parkin

  SSC Guru

  Points: 246695

  More actions

  September 17, 2020 at 3:25 pm

  #3789187

  Did my response satisfy your requirement?

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.