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

Question

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

SSChampion

Points: 12537

September 15, 2020 at 4:53 pm

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)

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Did my response satisfy your requirement?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel