I am looking for a few pointers rather than a complete solution.
I have a table (well indexed) that contains 100000+ rows that lists PatientId, TreatmentDate, and a number of columns that list the patients treatment.
CREATE TABLE [dbo].[PatientTreatments](
[id] [int] IDENTITY(1,1) NOT NULL,
[PatientId] [nvarchar](12) NOT NULL,
[TreatmentDate] [date] NULL,
[Treatment_None] [int] NULL,
[Treatment_PDT] [int] NULL,
[Treatment_Laser] [int] NULL,
[Treatment_Lucentis] [int] NULL,
[Treatment_Macugen] [int] NULL,
[Treatment_Avastin] [int] NULL,
[Treatment_Triamcinolon] [int] NULL,
[Treatment_AnecortAveac] [int] NULL,
[Treatment_Annan] [int] NULL,
[TreatmentType] [int] NULL
) ON [PRIMARY]
I need to count the number of treatments that patients have had over a certain period.
I guess that it is a simple group by TreatmentType however there is one other piece of logic that needs to be addressed.
There are 8 different treatment types each with its own column in the database. I am only interested in patients that have had the same treatment type in all thier visits
So if a patient has changed treatment type his/her treatments should be excluded from the totals.
How would the experts recommend doing this?
Maybe there is a simple statement but i was wondering if i should create a temporary table with a "total value" for treament. For example the combination of the treatment types could be seen as a binary value. If i create a table with that value then it would be easy to compare records against each other.
Then maybe a not exists in sub query could look for patients who have changed treatment.