To keep it simple I have a table that has two columns: PatientID, VisitYear.
PatientID is the patient identity and VisitYear is the year of their visit to a clinic. It not a physical year, but the number of years since the patients first visit.
A patient can make a number of visits per year. So the table could look like:
Note that patient C has not made any visits during VisitYear 2. This is important as you will see later.
I need some TSQL that can produce me a cross table that shows VisitYear against the number of patients that have made 0, 1, 2 etc visits.
So for the above data i would expect
VisitYear 0 visits 1 visit 2 visits 3 visits
0 0 1 1 1 -- A made 3 visits, B made 1 and C made 2.
1 0 1 2 0 -- A and B made 2 visits, C made 1 visit.
2 1 1 1 0 -- C made 0 visits, A made 1 visit, B made 2 visits
I think most of this is quite easy... what i can not do it return the fact that patient C did not make any visits in the second treatment year. This is because there is no row in the table for the visits the patients have not made!
The query should be able to support any number of VisitYears. The example uses years 0 to 2, but in the future there can be more years of treatment.